I started a new discussion on this as it had morphed into this on a previous discussion that was loosely related and getting a bit messy.

I have a calculation on a field called HistoryData_Results_Year.

The calculation looks to the records in the HistoryData table and orders them ascending.

1) This works fine and provides the correct answer.

ExecuteSQL ( "SELECT HistoryData_Results_Year

FROM HistoryData

ORDER BY HistoryData_Results_Income_YearAmtIndexed_Total_ByYear FETCH FIRST ROW ONLY";

"" ;"")

I wish to enhance the calculation to look at a found set of records with the same foreign key.

The key is set globally when clicking on the record to be viewed (which also launches the layout where the found set of records and calculation are contained).

2) I tried this and the calculation results in a blank.

ExecuteSQL ( "SELECT HistoryData_Results_Year

FROM HistoryData

WHERE HDfkHistoryID =?

ORDER BY HistoryData_Results_Income_YearAmtIndexed_Total_ByYear FETCH FIRST ROW ONLY";

"" ;GetAsNumber($$HistID))

Field Types:

HistoryData_Results_Year **is text**

HistoryData_Results_Income_YearAmtIndexed_Total_ByYear **is a number**.

$$HistID **should be a number since it is set from a number field.**

** **

Any thoughts on why I can't get results from the found set would be appreciated.

FileMakers SQL capabilities are always unrelated to foundset (and by the way also relationship graph).

You have to collect all IDs of foundset into a $$_var and use SQL IN-Operator.

(Hope that FM ExecuteSQL can deal with a variable number of arguments to ... WHERE id IN (???))