Maybe this is not news to everyone here, or maybe my setup is very sub-optimal, or maybe I am missing something significant but...
I was very excited to implement ExecuteSQL Function in some of my solutions. My thought was that I could quickly grab data from a table with SQL instead of switching to a Filemaker Layout, scripting a search, and returning the value I was interseted in.
In practice, however, a simple call like:
$dispenseID = $someValue; // passed to the step from earlier
$sql = "SELECT ITEM.NDC_CODE FROM ITEM, DISPENSE
WHERE DISPENSE.DISPENSED_ITEM_ID = ITEM.ITEM_ID
AND DISPENSE.DISPENSE_ID = " & $someValue
$result = ExecuteSQL ($sql ; "" ; "" )
is much, much slower than switching to the Item layout and searching for the dispense ID, returning to the original layout and returning the needed value.
Is the problem related to the fact that the data being accessed by the ExecuteSQL is actually an ESS datasource instead of a native FM table?