AnsweredAssumed Answered

Perform Script on Server -> Going nuts

Question asked by fredrick on Nov 24, 2017
Latest reply on Nov 25, 2017 by fredrick

Hi,

 

I have been trying to get a script to run on the server to make a forecasting tool more efficient for those who are using our filemaker solution at home or off-site. We use a forecasting tool that loads data from a sales data file and it creates a separate table using an SQL search (the table it takes this from is not visible for other users and can therefore not be ' open', to avoid slowing down the SQL query).

 

The purpose of the script is to create a table loaded with data from a sales data file (which displays in a portal on the layout the users see), when a user selects a customer they would like to enter the forecast for.

 

I have been using this script normally (without the perform script on server), with success, but if people are working from home the network traffic makes the execution slow.

 

Part of my script is compatible and part is not compatible with PSOS, so I have tried to be smart and cut the script in half, supplying data to the server script via a dump table, so that the server script would have the necessary data.

 

My Local part is as follows:

  • Set Variable [ $Customerselector; Value:"'" & Layout table Forecast::customer selector & "'" ]

  • Set Variable [ $CustomerID; Value:Layout table Forecast::customer selector ]

  • Set Variable [ $user; Value:Get ( AccountName ) ]

  • Go to Layout [ “Populate Forecast table DUMP” (Populate Forecast table DUMP) ]

  • Show All Records

  • New Record/Request

  • Set Field [ Populate Forecast table DUMP::Accountname; Get ( AccountName ) ]

  • Set Field [ Populate Forecast table DUMP::Customer Selector; $Customerselector ]

  • Set Field [ Populate Forecast table DUMP::Customer ID; $CustomerID ]

  • Perform Script on Server [ “Populate rpt_forecast trial test for perform on Server SERVER PART”; Parameter: Get ( AccountName ) ] [ Wait for completion ]

  • If [ Get ( ScriptResult ) = "Occupied" ]

  • Show Custom Dialog [ Title: "Customer occupied"; Message: "Dear " & Get ( AccountName ) & ",¶ ¶ This customer is currently being edited by another user."; Default Button: “OK”, Commit: “Yes” ]

  • Go to Layout [ original layout ]

  • Set Field [ Layout table Forecast::customer selector; "" ]

  • Else If [ Get ( ScriptResult ) = "OK" ]

  • Go to Layout [ original layout ]

  • Show Custom Dialog [ Title: "Script executed"; Message: "The script executed on the server and the result was: " & Get ( ScriptResult ) & "."; Default Button: “OK”, Commit: “Yes” ]

  • End If

  • Go to Layout [ “Populate Forecast table DUMP” (Populate Forecast table DUMP) ]

  • Enter Find Mode [  ]

  • Set Field [ Populate Forecast table DUMP::Accountname; Get ( AccountName ) ]

  • Perform Find [  ]

  • Enter Browse Mode

  • Delete All Records [ No dialog ]

  • Go to Layout [ original layout ]

 

The dump file record is being created, this I have been able to check and follow.

 

My server part is as follows:

  • Set Variable [ $User; Value:Get ( ScriptParameter ) ]
  • Go to Layout [ “Populate Forecast table DUMP” (Populate Forecast table DUMP) ]
  • Enter Find Mode [  ]
  • Set Field [ Populate Forecast table DUMP::Accountname; $User ]
  • Perform Find [  ]
  • Enter Browse Mode
  • Set Variable [ $Customerselector; Value:Populate Forecast table DUMP::Customer Selector ]
  • Set Variable [ $CustomerProducts; Value:ExecuteSQL ( " SELECT DISTINCT Product FROM rpt_forecast WHERE Customer_ID = "  & $Customerselector ; ¶ ; ¶  ) ]
  • Set Variable [ $CustomerID; Value:Populate Forecast table DUMP::Customer ID ]
  • Set Variable [ $i; Value:0 ]
  • Set Variable [ $itemcount; Value:ValueCount ( $CustomerProducts ) ]
  • Go to Layout [ “rpt_forecast summary” (rpt_forecast summary) ]
  • Show All Records
  • Enter Find Mode [  ]
  • Set Field [ rpt_forecast summary::User ID; $user ]
  • Set Error Capture [ On ]
  • Perform Find [  ]
  • Set Error Capture [ Off ]
  • Delete All Records [ No dialog ]
  • Enter Browse Mode
  • Enter Find Mode [  ]
  • Set Field [ rpt_forecast summary::Customer SAP ID; $CustomerID ]
  • Set Error Capture [ On ]
  • Perform Find [  ]
  • Set Variable [ $Foundcount_Customer; Value:Get ( FoundCount ) ]
  • Go to Record/Request/Page [ First ]
  • Set Variable [ $Occupied_User; Value:rpt_forecast summary::User ID ]
  • Set Error Capture [ Off ]
  • If [ $Foundcount_Customer > 0 ]
  • Exit Script [ Result: "Occupied" ]
  • End If
  • Enter Browse Mode
  • Loop
  • Exit Loop If [ Let ( $i = $i + 1 ; $i > $itemcount ) ]
  • Set Variable [ $product; Value:GetValue ( $CustomerProducts ; $i ) ]
  • New Record/Request
  • Set Field [ rpt_forecast summary::Customer SAP ID; $CustomerID ]
  • Set Field [ rpt_forecast summary::Product name; $product ]
  • Set Field [ rpt_forecast summary::User ID; $user ]
  • Set Field [ rpt_forecast summary::Existing record; "Yes" ]
  • End Loop
  • Exit Script [ Result: "OK" ]

 

If I check the Table ' rtp_forecast summary' One record has been created, but I cannot get the script as a whol to run from the server. If I run it from the local system it works like a charm, but not from the server, even though I have checked the compatibility of each step.... There are no scripts that run when I open the file in which the script is active, so that should not be the problem either.

 

The result given by the script is the "ok" script result, but the table is not being created.

 

Does anyone have an Idea what I have been doing wrong???

I must have be staring at this script for days now......

 

Kind regards,

 

Fredrick

Outcomes