ndveitch

Coping data between tables using PSoS

Discussion created by ndveitch on Sep 28, 2016
Latest reply on Sep 28, 2016 by monkeybreadsoftware

Hi there,

 

I need to find out what would be the best way to copy data between 2 tables using the PSoS. I have a lines table that I want to copy the last 12 months wroth of data to a temp table for reporting. The reason for this is because I have a ton of calculation fields to get the values for the individual months, such as the date headings,  amount values per month and so on.

 

I originally had it that the script would go to the lines table, find the last 12 months worth of records for whatever date the user requested, and then go to the temp table, remove any records that the user might have previously imported, and then import a fresh copy to the temp table. This works, but to import 1000 records takes anywhere from 25-45min. As you can imagine this is not really ideal. So I decided to try use the PSoS only to find out later that you cant import from FM to FM when doing it on the server. So I tried CSV, but had some trouble with the import as it wasn't importing to the correct fields. Then I tried using variable lists of the key fields, going to the temp table and creating new records using the lists and having the database update required fields via relationships but that also took some time. Now I am using export records to xlsx, then import records using the same path. It seems to be working but is still slow.

 

This is the script step that calls the PSoS. The zg_Date is the global date that the user is asked to enter the end date they require.

 

Perform Script on Server [ Wait for completion ; “Catches by month - Server” ; Parameter: Get ( AccountName )  & ¶ &  Catches::zg_Date ]

 

This is my PSoS script that is called when the report is run.

 

Set Variable [ $Name ; Value: GetValue ( Get ( ScriptParameter ) ; 1 ) ]

Set Variable [ $Date ; Value: GetValue ( Get ( ScriptParameter ) ; 2 ) ]

 

# Using the $Date variable, go to the Lines table and search for the required date range for the last 12 months up to the global date

 

# Create variables to be used for the date search in the Lines table

Set Variable [ $Var1 ; Value: Date ( Month($Date) ; Day(1) ; Year($Date) - 1 ) ]

 

Enter Find Mode []

Go to Layout [ “All fields Received Lines” (Received_Received Lines) ]

Set Field [ Received::Date ; $Var1 & "..." & $Date ]

Set Field [ Received::Stock ; "==Equipment" ]

Set Field [ Received::zc_sort ; "==Boxed" ]

Perform Find []

 

# Should there be no records, exit the script and pass the result so the calling script knows there was an error

If [ Get ( FoundCount ) = 0 ]

Exit Script [ Result: "No Records" ]

End If

 

# Setup Path for Export File

Set Variable [ $Path ; Value: Case (   Get ( SystemPlatform ) = -2 ; "filewin:" ;  ""  )  &  Get ( DocumentsPath ) & "List.xlsx" ]

If [ Get ( LastError ) <> 0 ]

Exit Script [ Result: "Var" ]

End If

 

 

# Export to excel

Export Records [ No dialog ; “$Path” ; Unicode (UTF-16) ]

If [ Get ( LastError ) <> 0 ]

Exit Script [ Result: "Export" ]

End If

 

Refresh Window []

 

# Import from excel export

 

Enter Find Mode []

Go to Layout [ “All Fields Catches” (Catches) ]

Set Field [ Catches::UserName ; $Name ]

Perform Find []

If [ Get ( FoundCount ) > 0 ]

Delete All Records [ No dialog ]

End If

Import Records [ No dialog ; Source: "$Path"; Worksheet: "" ; Add; Unicode (big endian) ]

Replace Field Contents [ No dialog ; Catches::UserName ; $Name ]

 

If [ Get ( FoundCount ) = 0 ]

Exit Script [ Result: "No Records" ]

End If

 

 

Exit Script []

 

From here is goes back to the calling script. This is the first time I am playing around with PSoS, so I am not sure if i have done it right, but it still takes a long time. Any ideas on what I should look for to see why it is taking so long to import?

Outcomes