Generate API Call using FileMaker

[MeltDown]

I have a FileMaker Pro db used for student registrations. My org is purchasing a remote-training software based on Access (front-end) and SQL Database (backend), using ASP pages to generate API calls. We need to determine ASAP whether I can begin registrations in FileMaker, and then automatically send the correct API call to the Access database. User interfaces are web-based - I use CDML, and the Access database uses ASP. I know nothing about API or ASP, but I have the coding for both sitting here in front of me. Would it be possible to have FMPRO automatically create an ASP page or perform an API call to another database?? Any help is greatly greatly appreciated!!!!

[Anatoli]

See the FM help about script steps.

But FM and ASP will be the most difficult task in FM Universe with huge workarounds.

For this combination of technologies/databases and web the best will be Lasso or PHP.

[Anatoli]

Correction

But FM and ASP will be the most difficult task in FM Universe with huge workarounds

or

very easy task with http://www.datavations.com/dvpages/products/fmconnector_asp.cfm

The cost is quite high and it is still just slow ASP. And the coding will be 2-10 times bulkier, than Lasso.

I would personally go with Lasso or free PHP.

[Christian Coppe]

I have tested an interesting experience, but not yet fully validated : An FMP database, the ODBC equivalent created (mydatabase.fp5.dsn). You allow the user to create/update/delete records in FMP. You open Access. In the menu, File, External Data : you open your ODBC FMP mydatabase.fp5.dsn. It becomes an Access table, linked in real time (you need a field with a unique identifier : status(CurrentRecord)). Then you try to add a new record in Access : your FMP database contains the new record... Try to update it : your FMP record is updated... Delete the record : it is deleted in FMP. By this way, it seems that all you do with Access is recorded in FMP. Is it your goal ?

I use that because I have an interface software working only with Access, and all the rest is developed under FMP : successfull up to now. Today, I cannot guarantee the robustness of this solution, and the validation is on-going. But try to test it, and let me know your comments.

[MeltDown]

Thanks for the Help! I do need to set up something very similar to what Christian is testing...The data in the non-FMP system is being stored in SYBASE tables, going through an ACCESS Front-end via the ASP pages. I don't know if the same technique Christian is using will work, but its worth a try. Christian, could you provide any more specific info on how you set up what you are doing? Are the Access and FMP databases you use located on the same server? My FileMaker system is currently in FMP 5.0 on a MAC OS9, and the SYBASE/ACCESS system is on a Microsoft Windows Server.

Anatoli - Thank you very much for your suggestions. I may be able to get the Datavations product because it is more politically acceptable to our department managers than LASSO. I don't know either LASSO or PHP and no matter which way I go the learning curve is pretty challenging. Your help is greatly appreciated!

[Christian Coppe]

Indeed, FMP and Access are on the same server, same OS (W2000). Take note that, when you proceed to a change in the structure of your FMP database (field definition, relationship, etc.), you should suppress your external table in Access and re-create it, otherwise the relationship is broken. I suppose that it doesn't work with two different OS, but I'm not sure. Also pay attention to the FMP password, as it can be requested by Access when an SQL request is sent to the table...

Christian

[MeltDown]

Christian - this is very helpful. I am thinking that I could open the FMPRO database on the W2000 server as a guest, and then do what you are doing. Can you tell me which versions of FMPRO and ACCESS you are using, and which ODBC drivers you are using to connect the databases? Thanks!

[Christian Coppe]

FMP 5.5 normal edition, Access 2000. The ODBC driver is the one of Filemaker. You find it in the list of ODBC drivers. In the ODBC utility, make file connections, instead of user or system connections, it's simpler. Ensure that each FMP file you want to share contains a file with a unique identifier. Create an ODBC connection for each FMP file you want to open with Access, then you search the ODBC files in the external tables of Access, not the "normal" files. The FMP file should be opened before to proceed. Access will ask you the table name (the FMP file name) and the unique identifier. That's all. Ensure to open the table in Access, in order to see if there is no password problem.

If you make a change in your FMP file, you should destroy your Access table and re-create it, otherwise you will encouter some problems.

Christian