Thanks for posting!
It looks like you're trying to do something called an External SQL Source which is only supported for specific ODBC data sources. Please see Knowledge Base Article #6420 for more information on what's supported:
Unfortunately, there's not going to be a viable work around for this beyond just using the import option to get the data in a FileMaker table. Of course, with that scenario the data wouldn't update in real time as ESS does.
OK, thanks, at least I know I don't have to keep looking for an ESS solution. Do you think that will change in the next version?
Regarding the import option, when I connect, I am really performing a query. Is there a way to import all the tables and data at once?
I don't really have any info on whether or not they'll introduce PostgreSQL support for ESS in the next version but it wouldn't hurt to suggest it here:
I'd do it for you, but it has some questions that are specific to you.
In regards to importing everything from PostgreSQL into FileMaker all at once, it's possible with a script.
To do this, create a new script by going to Scripts -> Manage Scripts and clicking the New button. From there, add an Import Records script step from the left column into the script by double clicking it. Once it's been added, you'll see three check boxes towards the bottom of the script editor. One will be to specify the data source which is where you'll choose your ODBC data source. Once you select this, it'll prompt you for a query which you can make pretty easily with the Query Builder available on that dialog. The query is basically what FileMaker will give the ODBC data source in order to get your data. In the Query Builder, choose the appropriate table and column and then click the Insert into SQL Query button. Repeat this for each column that you'd like to import. Once you're done, click Ok to exit the Query Builder and Ok again to exit the Specify ODBC SQL Query dialog.
At this point, you'll need to tell FileMaker what data goes where. Click the check box that says Specify import order. Here, you'll see the fields that you're pulling in from PostgreSQL on the left under Source Fields and your FileMaker fields on the right under Target Fields. Make sure that the drop down menu above the Target Fields column is set to the correct table and then drag the target fields up and down to match the source fields. You can also make this script reusable with a few additional options. Select the option that reads "Update matching records in found set" and enable the option to "Add remaining data as new records". You'll need to specify a field to match based on. The match field is how you tell FileMaker whether the record that's being imported is a new record or an old record that needs to be changed. Pick a field or multiple fields that would be right for this and click on the arrow to the left of the Target field(s) you chose to change it to an equals sign. Once you've done that, click Ok to return to the script editor.
You'll also see any option there to Perform without dialog. This simply suppresses the dialog boxes that would normally come up during an import so it's up to you on whether or not to enable it. You'll want to add and setup an additional Import Records script steps in your script for each table that you're importing so repeat this process until you've got them all. Once you're satisfied with your script, you can close the script editor, save it and run it.
Please let me know if you'd like any clarification on the above.
Thank you, that worked out well. Importing may have some advantages. With a script, it really is not that big of a deal (although, creating the script and fields takes awhile, and I'll have to be aware if the source table changes). I left a request for PostgreSQL ESS support. Thanks again.
Can you suggest a plugin or a work-around that can be used to connect FileMaker to postgresql ?