I have a few databases that are linked by External Data Sources.
I use a front end file just for the UI.
I would like to know if there is a way to grab the External Database name for whichever table I am working in?
Thank you all.
ExecuteSQL ( "SELECT BaseFileName FROM FileMaker_Tables WHERE TableName = ?" ;
"" ; "" ; Get ( LayoutTableName ) )
Might be useful in a script. This will return the BaseFileName for the current layout's Table Occurrence (TO). Change to a different layout and get a possibly different result for that layout.
Have you tried this:
ExecuteSQL ( "SELECT * FROM FileMaker_Tables WHERE TableName = 'YourLocalFileTO'" ; "" ; "" )
It returns the following:
"YourLocalFileTO" is the TO in your UI file that points to the external table. You also can narrow it down with this:
ExecuteSQL ( "SELECT BaseFileName FROM FileMaker_Tables WHERE TableName = 'YourLocalFileTO'" ; "" ; "" )
I am trying to understand how to use this;
"YourLocalFileTO" <- Am I supposed to be hardcoding this for and placing this SQL Query on each table I want to do this on?
And for FileMaker_Tables, is that used as it is?
Im not well versed in SQL so excuse my ignorance.
One option is to create a script and set variables to this query for each table you want to check, if you're checking multiple tables.
The only thing you need to change is YourLocalFileTO in the query. BaseFileName and FileMaker_Tables do not need to be changed.
If your TO in the Relationship Graph (in the UI file) is called "Contacts_Data" for example, your query is:
ExecuteSQL ( "SELECT BaseFileName FROM FileMaker_Tables WHERE TableName = 'Contacts_Data'" ; "" ; "" )
Make note of the single quotes around the TO name.
If your file is hosted it will include the name of the server in brackets, ie "Data File (server.domain)"
Understood. I wil give that a try.
I didn't know you can pass variables into a SQL query.
Yes, you can pass variables into an SQL query, but in this case I meant in the script itself.
Set Variable [ $contactsSource ; "ExecuteSQL..." ]
Set Variable [ $invoiceSource ; "ExecuteSQL..."]
Your script then can check all the sources you want with individual SQL queries for each TO you want to check.
You can "pass" variables if they are the parameters. The "?" in the ExecuteSQL() and the variable is appended to the end. There woudl be two "placeholder ?" in the query.
ExecuteSQL ( <<query>> ; "" ; "" ; $parameterVar ; $parameterVar2 )
OR you can concatenate the TEXT of the query (it won't evaluate the variable otherwise):
" SELECT abc, defFROM xyzWHERE abc = " & $variable1 & " AND def = " & $variable2 & "
" SELECT abc, def
WHERE abc = " & $variable1 & "
AND def = " & $variable2 & "
Phils post worked very easily and got exactly what was needed.
Thank you all for your help! I appreciate it.
Retrieving data ...