I'm looking to run a script on FMS13 from FM14 clients which should, when working, speed up the user experience compared to running the same on the client side.
The problem I have is that it doesn't pull through the data from the record I want it to, despite the call being made with ExecuteSQL. I'm pretty sure there's something I don't understand about PSOS which means that no data is being returned from the records I want.
I have two tables in question - Customer and Data. The idea is that the user will click a button on the Customer layout, passing a Global Variable of the customer ID to the PSOS script which then uses ExecuteSQL to find the relevant data on the Data table with the Global Customer ID as an argument in the SQL statement:
SELECT FilterRefresh, SUM(FilterRefreshVal), ''||Year
WHERE CustomerID_FK = ? and
PeriodNumber = 1
GROUP BY FilterRefresh, Year
"|" ; "|" ;
I understand that PSOS has no context of table or layout when it's run, but in this instance I would have expected that the code above be enough to tell it where to find the data it needs (Data_Home) and for which record ($$CustomerID), but this isn't working.
I sense there's something fundamental I'm doing incorrect here and any help is appreciated, as always!