Combining data from SQL database with local FM data

[Juerg]

Hello

 

My company has two mechanisms by which orders are placed - online and by email/phone. The online orders are retrieved by pulling XML down from a remote server, parsed and ingested into a Filemaker database. The email/telephone orders are booked in through some software which runs on our local server as an MS SQL database.

 

What I would like to do is consolidate both sets of data into one database so that I can view all of our orders regardless of how they entered the building.

 

The online orders have been taken care of, I just need help with adding in the orders from the SQL database. Is the best approach to copy the data into FM and then periodically sync with the master SQL data to check for cancelled and updated orders? I'm really struggling to get my head around how I should go about doing this so any pointers would be much appreciated.

 

I should say that I'm not after any code necessarily, just some advice on what I should consider and what the possible options are.

 

Thank you,

 

Juerg

[Wim Decorte]

You have two basic options:

 

- import from an ODBC data source (your SQL Server): this duplicates the SQL data in FM, so here you'll have to make sure to sync up from time to time (redo the import, with the "matching" option)

 

- use the FM ESS feature (provided that your SQL Server is ESS compatible): that lets you treat the SQL Server tables as native FM tables so there is no need to duplicate data.  You can just "read" from the SQL Server data