Jump to content

Exporting Single Record from found set. (Server Side Script)


Tyra
 Share

This topic is 2875 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I am in the process of converting scripts from a robot client to run as server side scripts and this is a common one that I have. This works, but exits with a script error of (101). I know about the big in server side scripts that produces this bug with "Go Record Next (Exit after last). Which happens in other files, but this one actualy shows up in the Schedule Log as an error. Also was womdering if any one had any ideas on simplifying this script without invoking a new window. I know of the omit method, but I can't get that to work with a found set..

 

Go to Layout [“Client Export” (Company)]

Set Variable [$CTime; Value:Get ( CurrentTimeStamp )]

#Export last hour

Set Variable [$sdate; Value:Get ( CurrentTimeStamp ) - 3600]

Set Variable [$date; Value:" ≥ "&$sdate]

Show All Records

Set Error Capture [On]

Enter Find Mode []

#Find by Date (Normal)

Set Field [Company::ts_Mod; $date]

Perform Find []

If [Get ( FoundCount ) = 0]

Exit Script []

End If

##Export Records##

Go to Record/Request/Page [First]

Loop

New Window [Name: "Export Client"; Style: Document]

Set Variable [$rec; Value:Company::n_SNComp]

Set Variable [$name; Value:Company::n_SNComp&".client.xml"]

Enter Find Mode []

Set Field [Company::n_SNComp; $rec]

Perform Find []

#Remote Export

Export Records [No dialog; “$name”; "FMPDSORESULT"; Unicode (UTF-8)]

Close Window [Name: "Export Client"; Current file]

Go to Record/Request/Page [Next; Exit after last]

End Loop

 

 
Link to comment
Share on other sites

A quick observation, this:

Enter Find Mode []
Set Field [Company::n_SNComp; $rec]
Perform Find []

is dangerous code, you may get more records than you bargain for.  Use:

Enter Find Mode []
Set Field [Company::n_SNComp; "==" & $rec]
Perform Find []

In general though, I would use an ExecuteSQL() call to find all the record ids that match your search request, store them in a variable and loop through those.  That will eliminate the need to spawn a new window to preserve your original found set.

Link to comment
Share on other sites

Thanks for the quick tip Wim. I have not used the ExecuteSQL() function much but in playing with, I am getting a wierd result.. I run:

 

Let ([

$query =
“SELECT ts_Mod
FROM COMPANY
WHERE ts_Mod < '2014-01-01 00:00:00'”;
$result = ExecuteSQL( $query ; “” ; “”)
];
$result)
 
I get all records. both before 2014 and after... (the "ts_Mod" field is a modification timestamp) 
 
If I change it to "WHERE ts_Mod > '2014-01-01 00:00:00'”;
 
I get none..
Link to comment
Share on other sites

This query works for me:

 

Let (
[
_query =
"SELECT ts_Mod
FROM COMPANY
WHERE ts_Mod < ?";
_result = ExecuteSQL( _query ; "" ; "" ; Timestamp ( Date ( 1 ; 1 ; 2014 ) ; Time ( 0 ; 0 ; 0 ) ))
];
_result
)
 
Note that I let FM format the timestamp and pass it as a parameter to teh query, so that I don't have to remember to format it correctly and quote it properly
Link to comment
Share on other sites

Ok, I have been able to get the following to work:

 

Let (

[tt = Get ( CurrentTimeStamp )-7200;

_query =
"SELECT ts_Mod
FROM COMPANY
WHERE ts_Mod > ?";
_result = ExecuteSQL( _query ; "" ; "" ; tt)
];
_result
)
 
but would like to use something like:
 
Let (
[
_query =
"SELECT ts_Mod
FROM COMPANY
WHERE ts_Mod > DATE_SUB(NOW(), INTERVAL 2 HOUR)";
_result = ExecuteSQL( _query ; "" ; "" )
];
_result
)
 
but of course there is the time format difference..
Link to comment
Share on other sites

This is the script I ended up with. The list removal part seems a bit clunky, but it works. (I'm using "" instead of isempty for no psrticular reason).

 

Go to Layout [“Client Export” (Company)]

#Export last two days

//  Set Variable [$rec; Value:Let ( [tt = Get ( CurrentTimeStamp )-172800;  _query = "SELECT n_SNComp FROM COMPANY WHERE ts_Mod > ?"; _result = ExecuteSQL( _query ; "" ; "" ; tt) ]; _result )]

#Export last 2 hours

Set Variable [$rec; Value:Let ( [tt = Get ( CurrentTimeStamp )-7200;  _query = "SELECT n_SNComp FROM COMPANY WHERE ts_Mod > ?"; _result = ExecuteSQL( _query ; "" ; "" ; tt) ]; _result )]

Show All Records

If [$rec= ""]

Exit Script []

End If

##Export Records##

Loop

Set Variable [$name; Value:Left ($rec; Position ( $rec ; "¶" ; 1 ; 1 )-1)]

If [$name=""]

Set Variable [$name; Value:$rec]

End If

Enter Find Mode []

Set Field [Company::n_SNComp; $name]

Set Error Capture [On]

Perform Find []

Set Variable [$filename; Value:Company::n_SNComp&".client.xml"]

Export Records [No dialog; “$filename”; "FMPDSORESULT"; Unicode (UTF-8)]

If [WordCount ( $rec ) > 1]

Set Variable [$rec; Value:Substitute ( $rec; $name &"¶"; "" )]

Else

Set Variable [$rec; Value:Substitute ( $rec; $name; "" )]

End If

Exit Loop If [$rec=""]

End Loop

Link to comment
Share on other sites

This topic is 2875 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.