How to reference another open database with BE_FileMakerSQL command

[Will_Logic]

Hi, I'm new to FileMaker scripting, and trying to basically select all data from another database created with a 3rd party application which is itself made with FileMaker. This means Script Workspace is disabled in that database.

So I can open an .fmp12 file from that database with script in blank FileMaker file:

Open File [Open hidden:Off; "Provenance"]

And the following line works fine to get FileMaker tables from my blank database:

Set Variable [$sql_test; Value: BE_FileMakerSQL ( "SELECT * From FileMaker_Tables" ) ]

Using the DatabaseNames function, I get following as names of the open databases:

TestScript (the one I'm running the script from)

Provenance
Inventory

Then I'm trying to get the same table, for testing, from the Provenance file's database, but cannot figure out how to reference it. I tried:

Set Variable [$sql_test; Value: BE_FileMakerSQL ( "SELECT * From FileMaker_Tables" {;;; "Provenance"})]

But then "Specify Calculation" dialogue window says "The specified table cannot be found" and won't accept this.

To query the tables names in database "Provenance", I used:

Set Variable [$tables; Value: TableNames ( "Provenance")]

which gives me:
Inventory
Provenance
Settings
 

This gets specified tables cannot be found also:

Set Variable [$sql_test; Value: BE_FileMakerSQL ( "SELECT * From FileMaker_Tables" {;;; Provenance })]

As does:

BE_FileMakerSQL ( "SELECT * From Inventory" {;;; "Provenance"} )

Any suggestions / advice how to reference external .fmp12 databases and Tables would be very grateful, thanks!

Will

[comment]

I wonder if I should answer this. It sounds like you're trying to hack a solution to which you don't have access privileges.

[Will_Logic]

No, When I open the Provenance file with script (see my question), it still asks me for user and password anyway which I need to enter. The application is in a program called ArtBase, you can google if you're bored 🙂 I have full access to the files, and can export from the export menu within ArtBase. But this is slow, and difficult and tedious to do all the table field mappings and so on without a script. As the Script workspace is disabled, I want to do it from external file. Thanks if any advice with the reference syntax with the BE_FileMakerSQL command! The help seems to say this command can query other open databases, and I need to enter user and password anyway to open the external database before I can query it.

Edited October 21, 2019 by Will_Logic
Further clarification password access to other db

[comment]

Well, then try:

BE_FileMakerSQL ( "SELECT * From Provenance" ; "," ; ¶ ; "YourFilename.fmp12" )

This should give you a simple CSV of the data in the Provenance table.

 

Edited October 21, 2019 by comment

[Will_Logic]

Fantastic Mr/Ms comment, works!  😍 I had misunderstood the optional parameter notation of {} in the workspace help / zendesk website BaseElements plugin reference page (very very minimal!) as part of the actual syntax. Also, I note the return character does not require "" but can work with them. From a background of basic to middling level Python programming, FM script certainly seems idiosyncratic, would have been very frustrated without your expert help!

in terms of picking up references to FM files, I suppose FileMaker looks first in current folder then outwards, and returns first file it finds with that name? Just curious, my book doesnt seem to mention this.

Also not obvious how to just copy and paste script commands from the window in Filemaker Pro Advanced to me, and I'm probably being dumb but can't see the Tools menu mentioned in Learn FileMaker Pro 16 book which has the debugger, so debugging by showing message boxes! but obviously FM is still under very active development and being used by many organisations.. I wonder if a docs website with syntax and crucially, simple and complex examples, for all the 'steps' and 'formulas', like all the main programming languages have, might help people. Sorry if there is one and I missed it.

[Josh Ormond]

What version of FileMaker are you using? Do you have FileMaker Pro or FileMaker Pro Advanced?

Couple of notes:

1. If you update your profile with the version you are using, that gives us a reference point to better answer your questions.
2. If you are using v17 or 18, there is ONLY FileMaker Pro Advanced. And you have to go into Preferences and check the box, "Use Advanced Tools". Then restart FileMaker. You will then see the "Tools" menu.

[comment]

46 minutes ago, Will_Logic said:

I had misunderstood the optional parameter notation of {}

Filemaker Help uses the same notation. They probably did not invent it. Excel has a similar syntax using square brackets.

 

46 minutes ago, Will_Logic said:

Also, I note the return character does not require "" but can work with them.

True, a single carriage return can be unquoted. This is a heritage from times when Filemaker branded itself as "the mom and pop store's database" and tried to make it easy for non-programmers. Thus there are quite a few things you'll find "idiosyncratic" if you're coming from a programming background.

 

46 minutes ago, Will_Logic said:

in terms of picking up references to FM files, I suppose FileMaker looks first in current folder then outwards, and returns first file it finds with that name?

If we are speaking in the context of the BE_FileMakerSQL() function, this only looks at files that are currently open - so it doesn't look in the file directory at all. I don't know what it does if there are two files open with the same name.

 

46 minutes ago, Will_Logic said:

I wonder if a docs website with syntax and crucially, simple and complex examples, for all the 'steps' and 'formulas', like all the main programming languages have, might help people.

There is the help.  It's far from perfect, but it does have all the functions and script steps (and more) in the Reference section.

 

Edited October 21, 2019 by comment