building a relationship with SQL

[technoranger]

I have a fairly extensive FM Pro solution used by about 30 staff to document and print (via XSL) student information. It's served using Server 8 Advanced. Recently, our state developed an online system for conducting and recording student meetings - about 6,000 a year. Staff are currently re-entering a large chunk of data back into my FM solution, along with a set of information not contained in the meeting data. I receive a SQL backup of the state hosted meeting data nightly and restore it to SQL Server Express. I'd like to retrieve a subset of my SQL data into my FM solution to keep folks from entering the same data twice, and do it as automatically as possible. I know I can import via ODBC, but is there any way to build a relationship to the SQL data as an external set of tables?

I'm open to any and all solutions

[Genx]

Well seeing as FM is ODBC compliant can't you run a function in SQL to put the data in to FM?

[technoranger]

I can explore that - my depth of experience with SQl goes back all of 3 months or so.

Thanks

[fmsavey]

Create a table in FM that mirrors the data definitions in the SQL table. After the nightly restore to SQL import the newly modified records in the SQL table into your FM table. Now you can work with it in a familiar environment. By the way, you don't even have to create the mirror table, FM will do it for you during the ODBC import by choosing "create new table" as the target. FM will name the table the same as the Data Source Name but you can change that easily.

[technoranger]

fmsavey, that is too cool! I'm trying it now and it will make this process a world easier.

Thanks!

[Genx]

FM Mag recently posted an article about this, if you don't have a membership already, get it, it's like $15 every 3 months... http://www.filemakermagazine.com/ ...

I haven't had a change to watch it yet but their videos usually cover everything you want to know at least to get you started.