Setting a field value from an external data source with a script

[strngr12]

Hello Everyone,

 

I need to run a script that executes an Applescript that, in turn, runs a Terminal command that isolates a value and then sets an FMP field accordingly.  Up front, here are the limitations:

 

1. The solution needs to be a push button solution where it happens as part of a script that does a whole bunch of other stuff.  It can't be a solution where setting the variable is an isolated event that happens explicitly like, say, using "Import Records" under the File menu.

 

2. It can't use an http call because the external data source is a proprietary database that only accepts incoming connections through it's proprietary applications - in this case a CLI.

 

3. Because this is for distribution to a large set of end users this cannot rely on 3rd party software.  It must work with FMP as it is right out of the box.

 

4. The script that this is a part of iterates through all found records and performs it's functions on each one so this part of the script should work accordingly.  A user should be able to search their database for the records they want to run the script on and then press a button and the script just goes until it finishes.  It should require no further input or actions from the user.

 

Here's the rundown of the scenario and what I have so far.

 

There is a 3rd party database/application set that is an ultra secure, online medium for storing different types of media - .mov files, images, PDFs, etc...  People who use this 3rd party often have FMP databases with all kinds of metadata pertaining to this media.  This 3rd party has a proprietary Command Line Interface that operates through a shell to exchange metadata with users.  This metadata can be both imported and exported through this CLI.  There is no other way of communicating with this 3rd party's data base except through their CLI (and a few GUIs, but they are of no use here.)

 

I have developed scripts that export metadata from client FMP databases to this 3rd party by turning their records into XML files and running calls to the 3rd party CLI through Applescript.

 

This part is all done, mastered and in use.

 

What I am trying to develop now is a means of importing data from the 3rd party database into a client's FMP database.  While I can run the Applescript to get the 3rd party to export the metadata and then just run some greps and seds through Applescript to isolate what I want - namely the 3rd party's corresponding item ID - I don't know how to then set a field in the FMP record to that ID.

 

So, here would be the run down of how the script works so far.  The user presses an "Update 3rd party" button and:

 

1. Go to the first record of the found set.

2. Go to the first field of the found set.

3. Using calculated Applescript, run a shell command to create an XML file on the client computer.

4. Format the value of the first field as a line of XML and add to the XML document.

5. Go to the next field, wash and repeat until we have reached the last field of that record.  We now have a complete XML document with metadata from the FMP database that corresponds to an item in the 3rd party database.

6. Through Applescript, log into the 3rd party database using the proprietary CLI and update the item with the metadata in the XML that originated in the user's FMP database.

7. Through Applescript, log into the 3rd party database using the proprietary CLI and export metadata about the item in XML format.

8. Through Applescript use grep and sed shell commands to isolate the 3rd party item ID out of the exported XML.

9. import the item ID into a field in the active record.

10. Go to the next record.

11. Repeat until the last record has completed.

 

As the red probably makes obvious, it is step 9 in the process that I am stumped on.  Once I have isolated that ID as standard output, how do I get it into FMP?  Can I set an FMP variable to that SO?  Can I set a field directly?  Can I set an Applescript variable to that SO and then set a FMP variable to that Applescript variable?

 

Any help would be greatly appreciated.

 

Thanks so much!

 

-Dan

[Wim Decorte]

Check out the FM AppleScript dictionary, it allows for a value to be set directly in a FM field.  That should allow you to finish it all up.

[strngr12]

Hello,

 

Thanks for the reply, but I'm not getting it.  I'm stuck with a value at the end of a shell pipe:

 

Perform Applescript:

 

3rd party commands | sed 's/"//g' ~/tmp/export.xml | grep -Ei "<item id=" | awk '{sub("<item id="" , "" ); print}' | awk '{sub(">"" , "" ); print}' |

 

So now at the end of my pipe I have: 1234567

 

How do I get that value into a field in the current record?  Sorry, but I'm very novice with Applescript as I have only used it to do things from Filemaker and only to feed the 3rd party CLI commands to Terminal.

 

I appreciate any help.

 

Thanks,

 

Dan

[comment]

How do I get that value into a field in the current record?

 

You use AppleScript to do that, too - see the attached example.

PopulateFieldByAS.fp7.zip

[strngr12]

Thank you so much!