Loop through all records and act on specific value in field

[Dextar]

Hi all,

I need to implement a server side script that runs every minute and loops through all records of a table (in this case "Invoices") and if it encounters a certain value in a certain field runs various commands followed by a shell script.

I have tried all sorts of combinations using Loop but I am new to FM scripting and I can't make it work, I am sure for you it's an easy task but how do I go about it so that it starts from the first record and then loops to the last?

Thanks!

[bcooney]

Rather than looping thru all records, couldn't the first step be to perform a Find for the records that have "the certain value?" Then loop thru that found set.

The basic loop structure is:

Go to Rec First

Loop

do something

Go to Rec Next (Exit after Last)

End Loop

So, what does your script look like? You can print it to pdf and upload it here.

[Wim Decorte]

I need to implement a server side script that runs every minute and loops through all records of a table (in this case "Invoices") and if it encounters a certain value in a certain field runs various commands followed by a shell script.

I have tried all sorts of combinations using Loop but I am new to FM scripting and I can't make it work

What part doesn't work? The looping or the execution of the shell script?

Am I understanding correctly that you have "script sequence" schedule? A schedule that first runs a FileMaker script and when that is done, a shell script?

[Dextar]

Hi Guys, thanks for your replies and sorry for the delay, but as so often another project from a "much more important client"^tm got in the way...

Anyway, I exported the script and attached it to this post.

When the script runs I receive script error 101 in the Go to Record step (doesn't say which one of the two). It appears that this can happen on FM Server 10 if you are using "Exit after last" in the Go to Record step?

Also, I get scripting error 800 in the Export Records step (unable to create file). The export folder is located in <Path to FileMaker>/Data/Databases/output writeable by fmsadmin. I have no problem changing this to the temporary directory though since the file will be deleted after successful FTP upload, would that be /tmp/?

I think bcooneys suggestion using a Find Record to speed up the process seems promising, maybe you can give me a nudge on how to implement it. :-)

Thanks again!

Facturas.pdf

[bcooney]

Your Export path does not look valid and that is why you're getting Error 800. Use Get (DocumentPath) or Get (TemporaryPath) & $filename. Define the $filename just like you are already. You do not need a $$var, since this all happens in one script.

Your field names suggest a poor structure. When is see Catidad1, Cantidad2, etc. I worry. You have several fields named in this way.

Error 101 does indeed occur on the Go To Record Exit after Last step.

I usually put all my logic in a flag calc field. Then the Server script would start off with a Find for that flag=1. That should minimize the logic within the script.

[Rick Whitelaw]

Hi Guys, thanks for your replies and sorry for the delay, but as so often another project from a "much more important client"^tm got in the way...

Anyway, I exported the script and attached it to this post.

When the script runs I receive script error 101 in the Go to Record step (doesn't say which one of the two). It appears that this can happen on FM Server 10 if you are using "Exit after last" in the Go to Record step?

Also, I get scripting error 800 in the Export Records step (unable to create file). The export folder is located in <Path to FileMaker>/Data/Databases/output writeable by fmsadmin. I have no problem changing this to the temporary directory though since the file will be deleted after successful FTP upload, would that be /tmp/?

I think bcooneys suggestion using a Find Record to speed up the process seems promising, maybe you can give me a nudge on how to implement it. :-)

Thanks again!

Error 101 should be expected in a looping script when the script runs out of records. It can be useful to trap error 101 too. Error capturing should certainly be set to "On" whether or not you decide to specifically deal with 101, meaning in some cases it can be ignored.

RW

[Wim Decorte]

Also, I get scripting error 800 in the Export Records step (unable to create file). The export folder is located in <Path to FileMaker>/Data/Databases/output writeable by fmsadmin. I have no problem changing this to the temporary directory though since the file will be deleted after successful FTP upload, would that be /tmp/?

FMS server-side scripts can only export to to the 'Documents' folder in the FMS folder structure. And to a temp folder it creates for its own purpose but that is deleted at the end of the script.

So set the export path to the FMS documents folder and you'll be fine.

If you need the file in another folder, use a script sequence schedule that runs an OS script after the FM script to move the file to its final destination. Keep in mind that fmserver/fmsadmin will need to have write privileges to that final folder.

[Dextar]

Right, thanks for all the input!

I now edited my script to use correct file path and using Get (DocumentsPath) it now at least exports, however:

It exports all records in one single file, so it seems it does loop through all records but then concatenates the result instead of exporting each Record with the flag value into an own file.

What do i need to accomplish that?

Crear_CFD.pdf

[Wim Decorte]

You're obviously in a found set of records otherwise you wouldn't be using the loop-go to next record.

The export command you have will export all records in the current found set. If you want to export just one record, you'll need to make sure you have only record in the found set:

while you're on the record:

show all records

omit record

show omitted

But of course this will break the found set for your loop so you should probably spawn a new window for isolating the record and exporting.

[bcooney]

My question is...what is your goal? What are you going to do with a collection of cvs files in the Documents folder?

Your script shows many examples of poor data structure, as I've previously commented. Perhaps you should step back from this technique and discuss what it is you're trying to achieve.

[Dextar]

OK, just to explain what I try to do:

- The Mexican government (hence the spanish) has mandated all invoicing to be electronic.

- The customer is using FileMaker for accounting, data structure is given @bcooney and he does not use list items for the positions, he uses 5 sets of fields for each record, hence the "strange" data structure. True, I could make a loop for that counting from 1 to 5 but that's about all I can do here and it should not affect the result

- Each invoice is to be represented and exported by a single file with a given data structure: UTF-8, first field is index field, all fields separated by a pipe "|", it is a multi line text file

- The customer insists on server side implementation so I can't implement this as a client side script, since for the second stage, the FTP upload of these files I need to use a system script (bash) which I would have to roll out to all clients. We also discussed using a plugin e.g. FTPeek but the customer does not want that.

- So, the customer now has a button on his invoices which says "electronic invoice", this sets a flag field to "1" (default "0")

Enters the script:

- Runs every minute or so, we have to test

- Checks for value "1" in flag field

- If flag is "1" exports the invoice record into a file, filename consists of internal and external invoice numbers + .txt

- If export successful sets flag to "2"

- loops through all records, or better perhaps found sets, I have to check the implementation of that

That's about it, hope that explains what I am trying to do here :-)

[Wim Decorte]

Fair enough... so you need one exported invoice file for each record. Your approach is pretty good but as bcooney says you do need to look at your data/table structure a bit.

Also looking at the script: you're doing replaces that affect the whole found set (every record), is that really what you intended?

As to the server side script: I would advise to set the interval at every 5 minutes with a force abort after 4. This will prevent run-away activity on the server.

You also will need to beef up your script with extensive error trapping and handling to make sure you handle locked records (records that are in active use by one of the users).

[Dextar]

Fair enough... so you need one exported invoice file for each record. Your approach is pretty good but as bcooney says you do need to look at your data/table structure a bit.

Also looking at the script: you're doing replaces that affect the whole found set (every record), is that really what you intended?

As to the server side script: I would advise to set the interval at every 5 minutes with a force abort after 4. This will prevent run-away activity on the server.

You also will need to beef up your script with extensive error trapping and handling to make sure you handle locked records (records that are in active use by one of the users).

Thanks, I was thinking to implement error trapping, etc. as soon as I have the basic functionality going, which is to successfully export all records with the flag value "1" into respective files, that's why it's still not there... Also, I am working with a backup of the DB so currently I am not too worried about records being locked.

I replaced the "Replace field contents" with "Set Field", let's see how that works, I was not aware that replaces changes the contents of all records in the set.

So, what I first really need to solve is the problem of pinning down each record with flag "1" and export it into a single file.. How can I isolate each record matching the search pattern and then have the script only export this data into a file and not all records at once?

Edit: As for the data structure, is there anything I can provide to you that would help you understand better? As said, the implementation the client has consists of the table Facturas which has 5 sets of fields for products, so there is no link to another table for list items, should be fairly easy...

Thanks again!

PS: Sorry, I am from the implementor side having been working almost solely with MySQL/PHP based systems and FileMaker appears to have a different approach so I am probably thinking in the wrong patterns here... :-/

[Wim Decorte]

There a couple of different ways. Here's one:

- do the find for all the records flagged as 1

- build a list of their IDs in a variable

- loop through the list in that variable, each loop iteration will do a find for an ID in that list, each find will produce a one-record found set that you can export and update the flag in the record

[bcooney]

Here's another.

Create a self-join relationship (keyID=keyID) and use GTRR in a new window in your loop. This'll isolate the current record. Set fields, export etc., and then close window. Go to Next.

or

[Dextar]

There a couple of different ways. Here's one:

- do the find for all the records flagged as 1

- build a list of their IDs in a variable

- loop through the list in that variable, each loop iteration will do a find for an ID in that list, each find will produce a one-record found set that you can export and update the flag in the record

Allright, almost there, now it only exports the records with the flag, but I still don't know how to isolate each record. What happens currently is that let's say 4 invoices are flagged, so first it exports 4 records into one file, then 3, then 2 and then 1.

What I am not clear about is how to build the variable list, creating a variable does not give me the option to choose the type, so what do I have to put in the calculate field?

Thanks!