cmj

ExecuteSQL Based On Found Set

Discussion created by cmj on Aug 21, 2018
Latest reply on Aug 21, 2018 by beverly

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.

Outcomes