I want to create a global list of unique years to facilitate searching by year using a checkbox.
I have an ExecuteSQL statement that works in the Data Viewer but doesn't work when entered as an auto-enter calculation for a field.
This is the statement that works in the DataViewer with results that are almost instant
ExecuteSQL ("SELECT DISTINCT c_Year FROM Transactions";
c_Year is a text field with a text result.
g_Year is a global text field with the ExecuteSQL statement above as the AutoEnter with calculation.
If I configure the field as a calculation field with this SQL statement, it works for takes forever to complete as I have almost 10,000 transactions. The calculation does not work if the field is Text with this SQL as the AutoEnter with calculation.
What could be going on here?