mac | pc Send data from FM to web API directly

[Justin Close]

I am working on a project where there is a need to share some data with a 3rd part.  Currently, the workflow is to export the records to a CSV and then upload the file to a web API that the 3rd party set up.  We would like to send the data directly to the website from inside the solution, instead of having to go through the steps of exporting and manually uploading the file.

 

I am not sure what this new automated-API will quite be like at the moment, as I am working with the web developer to create it in the first place.  But I would like to get a better understanding of the possible FileMaker side of things before trying to work that out with them.

 

Is this possible to do with 13?  (Note:  this is just FMP client and local file, not a hosted/server solution.)  Is it possible to use "Insert from URL" (even though I am really exporting) to pass the data via POST?  or "Open URL"?  Seems like maybe it could be done with a web viewer, if you built the data object into a variable and used javascript to make the connection?  Would be nice if there were a full/native FM way to do it, though (easier to maintain I would think).  Would this require a plugin (prefer not - cost issues)?  Is it perhaps possible to export a file to a URL-path (then maybe I could just re-use the API that currently is in place)?

 

I would assume that many folks have done this before I would like to hear from the voice of experience. 

 

 

Thanks,

Justin

[Lee Smith]

Automatic message

This topic has been moved from "FileMaker 13 General Discussion" to "Importing & Exporting".

[doughemi]

This is a fairly straightforward operation if you approach it as the website "pulling" data from the database via the FileMaker PHP API interface, rather than FM "pushing" data out via an export.  This is known as FM Custom Web Publishing, for which you can do a web search for much more information.

 

This requires FileMaker Server; it cannot be done with standalone FileMaker Pro.

 

If you can give a more detailed description of the data to be passed, a more specific outline of the process can be provided.

 

If you are seriously limited by budget, you could generate html code in a calculation field and export that field.  This is a real kludge, and will make the FM purists shudder, but I have had such a system running for several years.

[mrubenson]

Give the plugin BaseElements a look - to me it seems https://github.com/nickorr/BaseElements-Plugin/wiki/Functions#BE_HTTP_PUT_FILE is what you need. 

[gpicard1]

You could connect to the MySql database the website accesses using an ODBC driver.  You can do this with just the client version of FileMaker Pro, I believe.    And you could create a script which changes the information showing on the website automatically by running the script.  But I don't think you can schedule the script to run unless you are using Filemaker server. But other than manually running the script, I think the rest can be automated.

[Justin Close]

Mrubenson:  the BaseElements plugin is a possibility.  I will review that option - nice that it is free, thus fits my restriction. 

 

GPicard1:  The ODBC technique is intriguing.  I don't know if the owner of the website wants to expose that directly or not, though.  I will inquire with them about it.

 

To explain a bit further, as Doughemi was requesting, if this is helpful for anyone:  the data being passed is just a CSV list of some of the fields for the current found set of records.  The client is generating specific sets of found records, and then sending that list off to this 3rd party; this could be several hundred to several thousand record's worth of data.  I am just trying to automate the 'sending' part of this process so they can press a button in the FM solution and directly submit the data to the 3rd party, instead of having to go through a more tedious process:  export the file, go to a special page on the 3rd party website, login that site, pick the 'upload' function of the website, and send the file.

 

--  J

[Justin Close]

I seem to have had success with just using built in FileMaker script steps on regular FMPA 13 (as opposed to server). 

 

I used the 'Insert from URL' script step, using a URL similar to this:

httpspost://some.site.net/some/path/toTheAPI?aparam1=something&aparam2=other stuff&paramData=large CSV formated string

I can't tell you exactly how the other end of the process works, the actual API itself.  I just described to the web guy what I wanted and he set it up. 

 

It has worked well so far.  I have been able to send decent sized sets of records:  ~1330 records, ~51KB total size.  I haven't really stress tested it with anything larger yet, but I hope to get around to it.

 

-- J

[DaMayan]

I seem to have had success with just using built in FileMaker script steps on regular FMPA 13 (as opposed to server). 

 

I used the 'Insert from URL' script step, using a URL similar to this:

httpspost://some.site.net/some/path/toTheAPI?aparam1=something&aparam2=other stuff&paramData=large CSV formated string

I can't tell you exactly how the other end of the process works, the actual API itself.  I just described to the web guy what I wanted and he set it up. 

 

It has worked well so far.  I have been able to send decent sized sets of records:  ~1330 records, ~51KB total size.  I haven't really stress tested it with anything larger yet, but I hope to get around to it.

 

-- J

 

Using a post via Insert From URL is a fine way to do this. 

 

However bundling everything in one large string wouldn't be my first choice for a couple of reasons:

 

- One bad record/field will most likely invalidate the whole export.

- The limit on the post charcount could be reached or even changed at any time. 

- No confirmation or logic possible on a per record basis.

 

What i would prefer is using a loop with a single call per record. That way the result of the URL call could give you a confirmation on the receiving end.

If something goes wrong you can find the record quickly and all other records will not be affected.

If there is any hiccup or outage a half completed sync will just continue on the next scheduled call.

 

But it also depends or the usage. A large number of updates every hour could fill this method up. But it's great for a continuous scheduled sync with a handful of updates at a time. Also most online APIs will work with a one-at-a-time model.

[Justin Close]

Interesting suggestions.  I will see about implementing them.  I haven't had a chance to test for an upper limit on character count for the Post URL; that has been a lingering question, and I like the method you suggest for getting around it.