I'd like to set something up where a user enters an invoice number in a field called 'invoice_no' on a filemaker table called invoices.

when they click a button (or even better...tab to the next cell) it goes out and grabs all the invoice information from an ODBC datasource (Sybase in this case) and imports it into the current record

I'm not sure how to say in the SQL statement

select * from invoices where invoice_no= ***insert the fm field here***

can anyone point me in the right direction pls.

Thanks

Note. This topic was move to General Support from our Custom Functions forum.

To get information "IN" to FileMaker, you'll use the Import command, selecting File / Import Records / ODBC Data Source...

In that import dialog you'll specify the data source and then be presented with a query builder where you can select the fields you'd like to import and build the SQL string you mentioned.

You're then presented with a standard FileMaker import dialog with your FileMaker fields on the right and your SQL data on the left. If you wish to create new records in FileMaker, you're all set: just line up the fields. If you'd like to update an existing record in FileMaker (for instance updating a FileMaker invoice with it's payment status) you'd use the "Update Matching Records in the Found Set" option, lining up at least one pair of SQL and FileMaker fields as match-fields to that FileMaker knows which record to update with which SQL data.

Hope that helps.

Thanks John.

I'd like to automate it so the users don't have to make any entries other than the invoice number..

for example...

they enter the invoice no 12345 in filemaker...press a button... that goes and grabs the related information from sybase and pastes into pre-mapped cells in fm.

should i be using a calculated SQL statement?

kellydigital wrote:Thanks John.

I'd like to automate it so the users don't have to make any entries other than the invoice number..

Of course. You can script the import I just described using the import records script step.