ExecuteSQL in field to limit Value List via Relationship

[MSPJ]

Hi - I've been trying a number of approaches to get a calculated value list using ExecuteSQL code. I initially tried putting the SQL code directly into the Value List definition but that didn't work.  Right now, I've done this:

I created a UnusedClaims field in a Visit table, whose value is calculated by this:

ExecuteSQL("     
  Select \"_k1_xSAClaim_ID\"
  FROM \"xSA Claim Status:xSAClaim\"
  WHERE \"_k1_xSAClaim_ID\" NOT IN
   (
    Select \"_k2_ClaimID\"
    FROM \"xSA Claim Status:VisitClaims\"
    WHERE \"_k2_VisitID\" = ?
          
    )
" 
;""; "" ; Visits::${Visit ID}

)

 

I then put a Claim TO in and related its primariy ID variable to the UnusedClaims field in the visit table.  Then I created a ValueList, using the _k1_xsaClaim_ID value from that related Claim TO, and in the value list definition clicked Use only related values from the Visits table.

Sometimes this works - but other times it includes values that are in the list that should be NOT IN.

The ExecuteSQL code works perfectly in the data viewer, but the Unused Claims field, which has the exact same code in it, includes values it should not include.

What am I missing?

 

 

 

[Wim Decorte]

A couple of points:

 

1) you really should avoid hard-coding field and table names in your SQL queries.  There are a lot of options out there that allow you to use a real field reference and that will make your queries safe for any future TO or field name changes

2) you may not want to put ExecuteSQL() calls in field definitions.  Especially unstored ones.  I've shown the potential penalty for that two devcons ago and it can be quite severe.

3)  This bit is really weird: "Visits::${Visit ID}" - did you type this yourself or did FM convert to this?

 

[MSPJ]

FM did it initially - it seems to be doing that for names with spaces - I assumed it was the equivalent of enclosing between \" and \"

So what strategy would you suggest for accomplishing the same thing in a value list?

[dwdata]

Seems like this is something that can be triggered via script upon layout/record load. Maybe?