AnsweredAssumed Answered

Exporting Records Based on Dates

Question asked by AlastairMcInnes on Jul 21, 2012
Latest reply on Jul 23, 2012 by philmodjunk


Exporting Records Based on Dates


I have a layout based on two linked tables - one holding book Title information and the other holding information specific to a particular edition of the book (hardback, paperback, kindle, etc.) The two tables are linked on the relationships graph by Titles::TitleID = Editions::TitleID.

I need to be able to export records which have changed since the last time the export was run in order to update an external database.

I have created a "last exported" global field in a table called (imaginatively enough) Globals.

The export needs to be run in two parts as I have to export Retail Books and Trade Books separately. There are fields on the layout detailing which are which - they're text fields displayed as radio button yes/no options.

So what I'm trying to do is find the records where either the Titles or Editions record was modifed since the last export and where the Retail flag is set to Yes.

I have created a form based on my Globals table which has buttons on it to run the export routine - this form is opened from the main form by the user pressing a button. I didn't want to clutter up the main form with multiple export buttons. 

My script is as follows:

#Run when the user presses the Retail Export button.
Freeze Window
Go to Layout [ “Browse Titles” (Titles) ]
Enter Find Mode [ ]
Set Field [ Editions::Retail; "Yes" ]
Set Field [ Editions::ModifiedDate; " > " & Globals::LastRetailExport ]
Duplicate Record/Request
Set Field [ Titles::ModifiedDate; " > " & Globals::LastRetailExport ]
Set Error Capture [ On ]
Perform Find/Replace [ ]
[ No dialog ]
Set Variable [ $ErrCode; Value:Get ( LastError ) ]
If [ $ErrCode ≠ 0 ]
Go to Layout [ original layout ]
Show Custom Dialog [ Title: "Retail Export"; Message: "An error has occurred during the export." & ¶ & ¶ & "Error Code = " &
$ErrCode; Buttons: “OK” ]
Halt Script
End If
Export Records [ FileName: ... ]
[ No dialog ]
Insert Current Time [ Globals::LastRetailExport ]
[ Select ]
Go to Layout [ original layout ]
I have removed all the filespec stuff from the Export Records step for brevity.
The script is failing at the error check - reporting an error of 5. When I try to manually follow the script through, it does the find without any problems.
Can anyone see where I've gone wrong or suggest a better way of doing this?
Many thanks,