2 Replies Latest reply on Mar 21, 2016 4:59 AM by Stu412

    PSOS problem with global variables

    Stu412

      Hi there

       

      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:

       

      ExecuteSQL (

      "

      SELECT FilterRefresh, SUM(FilterRefreshVal), ''||Year

      FROM Data_Home

      WHERE  CustomerID_FK = ? and

      PeriodNumber = 1

      GROUP BY FilterRefresh, Year

      ";

      "|" ; "|" ;

      $$CUSTOMERID

      )

       

      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!

       

      Thanks