Will_Logic

Thanks, I hadn't used Char() function in FileMaker before, it's more readable also.

Thanks @consultant @Wim both your comments are helpful to make me recheck, think through again and confirm not losing my mind just yet.. ExecuteSQL can use with multi character delimiters yes, unfortunately for this one executing against another script-locked FM database, so forced to use BE_FileMakerSQL. The docs do indeed state BE_FileMakerSQL only accepts 1 character delimiters. However, I used this same code less than 2 weeks ago on other FM database and worked, I can see the text files output with <c> <r> delimiters in them. So I guess only thing is environment changed somehow. In case anyone else sees this and struggling to find 1 character delimiter that they can guarantee won't be in the data.. I suppose for a data delimiter, you really want the most obscure character you can find.. My data is large and varied - as I was just looking main FM code window, my first best guess was: ≥ and ≠ Probably 50% chance working without any exception for data I have, but I speak Chinese, and then realised maybe 1 Chinese character will be accepted by FM script as a 1 character delimiter (I kept thinking unicode codepoints more than 1 character, which indeed they are), lo and behold present: 䶫 and 䥮 which I'm fairly sure most native Chinese people won't understand without a dictionary! In fact wiktionary.org doesnt even have definition yet for either! Do have a few Chinese artwork names in this data, but even so chance of this coming up in more than 1 artwork is infinitesimal I think! Of course FM is in utf-8 coding by default, so any 1 unicode character (not code point) should work I think? Then after this, as you say use Substitute() on result, still within the FM script, perform a couple of Substitute() lines to convert these to save to CSV format.

Hi, I have been using Base Elements command BE_FileMakerSQL in FileMaker script to SQL select data, and return with '<c>' as field(column) delimiter, and '<r>' as the row delimiter. The below line worked before, I dumped a whole FM database with these delimiters showing in text files. But somehow, now if I run below line, it seems to have changed to ?only allow? 1 character for the delimiter, which of course doesn't make sense anyway with varied characters in the data: BE_FileMakerSQL ( $sql_select; "<c>" ; "<r>" ; $fm_file ) The string this returns now looks like for example: 352265<gwgwh<474848<<wegwgw<65755<gwgw .... i.e. it has only taken the first character of the delimiter, I changed delimiter string to test with eg "A≠" instead of "<c>", same problem, it then only inserts "A" as delimiter. Just wonder if anyone can guess what might have happened here? Thanks kindly any suggestions!

Thanks @IdealData, yes there are custom menus in the Foobar app, don't have any experience of how security settings work in FileMaker.

Thanks @IdealData I just realised I can actually access the Security Settings dialogue in Foobar databases, see picture, do I edit setting here to turn on ODBC access or something? FYI, SQL SELECT statements DO work in this database already with these settings (just not SQL TRUNCATE), from a script in external FileMaker database as I tried to explain above. i wonder if I can also turn on script workspace also by editing one of these settings? Thanks yr help anyway.

Hi, I have a script to truncate all the data out of the tables in an external FileMaker Database. I confirmed this works for one custom FileMaker database I tested with several tables. It uses the Base Elements plugin SQL TRUNCATE command to truncate all the data. However, we have many databases coming in written in a custom FileMaker application, let's call it Foobar, where the script workspace is disabled for each of these. For testing purposes, we would like to truncate all data from one Foobar database. I can run Base Elements SQL SELECT commands no problem on any Foobar database from my own separate FM database which runs the script, after I enter the correct username and password. However, the SQL TRUNCATE command in this script is not working at all on the Foobar database. As I said, Foobar is an application written entirely in FileMaker, which disables the script workspace. I enclose script below, wonder if anyone has a suggestion either a - how to get this SQL Truncate to work with Foobar database, or b - another quick way to truncate data from a Foobar database (there are about 100 tables or more in any Foobar database!). Any suggestion most gratefully received. Set Variable [ $open_dbs; Value:DatabaseNames ] Show Custom Dialog [ Message: $open_dbs; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ] Set Variable [ $fm_filecount; Value:ValueCount ( $open_dbs )] Set Variable [ $count; Value:1 ] Loop Set Variable [ $fm_file; Value:GetValue ( $open_dbs ; $count )] If [ PatternCount ( $fm_file ; "FileMaker" ) = 0 ] Set Variable [ $table_names; Value:TableNames ( $fm_file )] Set Variable [ $number_tables; Value:ValueCount ( $table_names )] Show Custom Dialog [ Title: "All Table Names This File: " & $fm_file & "¶Press cancel to Abort"; Message: $table_names; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ] If [ Get(LastMessageChoice) = 2 ] Show Custom Dialog [ Message: "Aborting"; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ] Exit Script [ ] End If Set Variable [ $table_count; Value:1 ] Loop Set Variable [ $table; Value:GetValue ( $table_names ; $table_count )] Set Variable [ $sql_truncate; Value:"TRUNCATE TABLE \"" & $table & "\""] Set Variable [ $truncate_table; Value:BE_FileMakerSQL ( $sql_truncate ; "," ; "¶" ; $fm_file )] Set Variable [ $table_count; Value:$table_count + 1 ] Exit Loop If [ $table_count > $number_tables ] End Loop Else Show Custom Dialog [ Title: "This database is the one running the script, so no need to truncate it:"; Message: $fm_file; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ] End If Set Variable [ $count; Value:$count + 1 ] Exit Loop If [ $count > $fm_filecount ] End Loop Exit Script [ ]

Thanks @IdealData I'm just going to dump the data though, possibly convert to JSON within FM first, from a number of databases same format, all with script workspace disabled. I dont think making a relationship will speed up dumping the data. I'm not doing report, just going to write to text file. I'm changing delimiters as there are commas and return characters in the actual data, so text file anyway. the focus of my question is really about data dumping speed.

Thanks again @Wim Decorte not sure if I did this exactly correct, but I tried timings on SQL Select of 5,000 rows of same (large) table using the native ExecuteSQL() function, without opening the external file first, versus the BE_FileMakerSQL() function, where I need to Open File script step first, and got 206.8 seconds vs. 300.3 seconds, ie about 30% faster. Just in case anyone can be bothered to look(!) I enclose the code I used, this 30% gain may not be worth it for what I'm doing. FYI this is selecting all fields of this table, and the SQL select string for first 1000 rows was 40,257,300 characters from FM Length() function. I'm not going to need the longer fields in the end, but I am just doing trying to do time comparisons now. I also enclose picture of the referenced external table in relationships graph - just checking if I did anything wrong? 🙂 1 - Here is the script where external file is opened, using Base Elements plugin command BE_FileMakerSQL - shows 300.3 seconds for this table: Open File [; "DataContacts11"] Set Variable [$offset; Value:0] Set Variable [$timer1; Value:Get(CurrentTimeUTCMilliseconds)] Loop If [$offset = 0] Set Variable [$offset_string; Value:""] Else Set Variable [$offset_string; Value:" OFFSET " & $offset & " ROWS "] End If Set Variable [$be_activity; Value:BE_FileMakerSQL ( "SELECT * FROM Activity" & $offset_string & "FETCH FIRST 1000 ROWS ONLY" ; "," ; "¶" ; "DataContacts11" )] # To mark progress, create empty file named "offset_" & $offset & "rows.txt" Set Variable [$progress_file; Value:BE_WriteTextToFile ( "C:\FileMaker_Script_Testing\FM_SQL_Select_Progress\offset_" & $offset & "rows.txt" ; "" )] Set Variable [$offset; Value:$offset + 1000] Exit Loop If [$offset > 5000] End Loop Set Variable [$time_seconds; Value:(Get ( CurrentTimeUTCMilliseconds ) - $timer1) / 1000] Show Custom Dialog ["Time in Seconds"; $time_seconds] Exit Script [] 2 - Here is the script using native ExecuteSQL() where file is not opened, ie restarting FileMaker and running this script first, gives 206.8 seconds: Set Variable [$offset; Value:0] Set Variable [$timer1; Value:Get(CurrentTimeUTCMilliseconds)] Loop If [$offset = 0] Set Variable [$offset_string; Value:""] Else Set Variable [$offset_string; Value:" OFFSET " & $offset & " ROWS "] End If Set Variable [$sql_activity_select; Value:"SELECT * FROM Activity" & $offset_string & "FETCH FIRST 1000 ROWS ONLY"] Set Variable [$activity_data; Value:ExecuteSQL ( $sql_activity_select; ","; "¶" )] #create empty file named "offset_" & $offset & "rows.txt" Set Variable [$progress_file; Value:BE_WriteTextToFile ( "C:\FileMaker_Script_Testing\FM_SQL_Select_Progress\offset_" & $offset & "rows.txt" ; "" )] Set Variable [$offset; Value:$offset + 1000] Exit Loop If [$offset > 5000] End Loop Set Variable [$time_seconds; Value:(Get ( CurrentTimeUTCMilliseconds ) - $timer1) / 1000] Show Custom Dialog ["Time in Seconds"; $time_seconds] Exit Script [] Thanks any comments.

Thanks kindly, @Wim Decorte, sounds like this could speed up process, but sorry, I don't know what a TO is! So I create a new table or something in the file where I am running the script, which itself is some kind of mirror or reference to the different table I want in a different file in the external database which I wish to query? Then I put this new table name in the ExecuteSQL() query? By the way, currently I am using the Open File step to open this external database, then I can specify the file and table names with BE_FileMakerSQL function, which requires the file to be open in order to reference it. If the file has not been opened with Open File, then if I put filename into BE_FileMakerSQL, it fails because doesn't know which file I am referring to. Also, this db prompts me to enter password credentials after running the Open File step, which I have. Tx kindly, sorry if my original question not clear.

Thanks @Wim Decorte but my understanding is ExecuteSQL() function (not step) can still only access a table in the current 'context' file, ie same database in which the script is running. https://fmhelp.filemaker.com/help/17/fmp/en/index.html#page/FMP_Help/executesql.html I don't see any way to specify/reference an external FM file using this function? I am having to run this script in a file external to the database I am querying, which I can do with BE_FileMakerSQL for example, but then only by opening the files first to get the reference.

Thanks kindly @Wim Decorte , this is really good to know, but unfortunately the database is built in a program on top of FileMaker, and this program stops access to the script workspace within the database. Therefore I cannot use the native ExecuteSQL() function. So I need to use FM Open File command first on the external database (which then prompts me for credentials which I have), as the only way to gain a reference to the files and tables. Then I can only use command like Base Elements or MBS, my main goal is to dump large parts of the data out in one script running one time, so I guess in this case means I can't avoid the problem of FM 'sending whole table' as you describe in your post, and this slowing down SQL SELECT execution presumably by many times over? I'm not using FileMaker Server here by the way, just FileMaker Pro (non-Advanced version). I don't think there is way to reference external FM database table without there being a record open?

Hi, I am using the Base Elements plugin Execute SQL command to select SQL on a table in another FileMaker file. For a file with many fields and quite a lot data in some fields, this times as about 1 minute just for SQL SELECT command for 1,000 rows. I haven't used MBS (monkeybread software) plugin yet, but I wonder if anyone knows if it is significantly faster? I.e. BE_FileMakerSQL vs. MBS(FM.SQL.Execute..) - is one significantly faster? Here's the code to select 1,000 rows for reference, $timer_seconds comes as about 60 seconds here. If I export same table internally in FileMaker using the menu export to Excel, it takes 29 minutes for all 41,000 rows, so timing seems similar for the Base Elements SQL. (NB there is some flag going on here in the FileMaker database so internal export gets 41,000 rows but script SQL SELECT COUNT(*) gets 200,000 rows for same table, but I don't think that is relevant to my question). Set Variable [$time1; Value: Get(CurrentTimeUTCMilliseconds)] Set Variable [$part_ca; Value: BE_FileMakerSQL ( "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; "&≠≠&" ; "EOR≠≠EOR" ; "DataContacts11" )] Set Variable [$timer_seconds; Value: (Get(CurrentTimeUTCMilliseconds) - $time1) / 1000] MBS command I think would be something like: MBS( "FM.SQL.Execute"; "DataContacts11"; "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; ...delimiters here I'm assuming.. ) We only have base FileMaker here on machine I am using, which can run the Base Elements plugin, but to run MBS plugin, needs FileMaker Pro Advanced to install it I think, am getting several databases in roughly same FM format where script workspace disabled, hence need to use SQL select run from script in my own external FileMaker file. Might consider getting FMP Pro Advanced if MBS is faster. Tx any suggestions!

Great thanks so much again Comment! So for my query where I want a variable table name, this does work: BE_FileMakerSQL ( "SELECT FieldName FROM FileMaker_Fields WHERE TableName = '" & $table & "'" ; "," ; ¶ ; $fm_file ) where $table must be a table name, and $fm_file must be an open FMP12 file name (database name). Another mistake I made was to leave $table inside Quote() function, which adds extra " double quotes which breaks it. In this case, even if the table name begins with a digit, as we're already enclosing in single quotes as a value inside SQL expression, it will be treated as string not integer (welcome correct me if wrong anything there! this my understanding anyway). However, when you want select * from $table you need the Quote() function round the table variable in case the table name begins with integer, as Comment advised before. FYI, I checked following MySQL type query doesn't work, but as above does, don't need it: BE_FileMakerSQL ( "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS" ; "," ; ¶ ; $fm_file ) Thanks for tip on breaking up the code also, used to do in Python, getting used to how to do this in FileMaker(!), so I guess this is indeed easier to follow: Set Variable [$sql_query_string; Value: "SELECT FieldName FROM FileMaker_Fields WHERE TableName = '" & $table & "'"] and use this string to construct BE command. Thanks kindly.

Hi, I may be running out of lives soon, but after Comment's very helpful answers to previous, have a possibly final sticking point. I want to return all the Field Names in a given external FMP12 table, ie again I must use BE_FileMakerSQL function or something else, as ExecuteSQL won't work. Following produces error (returns '?'): BE_FileMakerSQL ( "SELECT FieldName FROM FileMaker_Fields WHERE TableName = " & Quote($table) ; "," ; ¶ ; $fm_file ) So I assume that FileMaker_Fields is accessible to Execute SQL but not to the Base Elements plugin function BE_FileMakerSQL. Following doesn't work either, trying the standard SQL query to get field(column) names from a database table: BE_FileMakerSQL ( "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = " & Quote($table) ; "," ; ¶ ; $fm_file ) So I assume information_schema table is beyond the range of Base Elements SQL commands perhaps? I note from the ZenDesk Base Elements website, there was a function BE_FileMaker_Fields, which has since been deprecated and then removed, looks like this would have done what I need - https://baseelementsplugin.zendesk.com/hc/en-us/articles/205350587-BE-FileMaker-Fields Otherwise, the script function FieldNames will only take a FileMaker layout name as parameter, not a table name. Maybe I could download a previous version of Base Elements just to get the BE_FileMaker_Fields function, but then some other stuff in my code might not work as requires latest version. Thanks any suggestions?

Yes this works best, thanks! It didn't help I had got variable name of my table wrong though..

Hi again, very grateful for help on last question with this Base Elements function. Now I am trying to cycle through all tables in an external .fmp12 file, and SQL select all from each table. I want to get external data from tables in an application built in FileMaker, which disables the script space in that database. As mentioned in my other topic 105263 here, this involves opening the tables with Open File step, which will then ask me for user and password which I have. Once the files are open, script can access tables with BE_FileMaker SQL function (but not with ExecuteSQL), as my script is running from a different file. For a static table name, this code works when $fm_file is set to relevant database: Set Variable [$sql_select; Value: BE_FileMakerSQL ( "SELECT * FROM ITEM" ; "," ; ¶ ; $fm_file )] and I get a non-null result set in the string. But now I want to set ITEM as a variable table name, and don't know how to get the right syntax. I tried following, query returns ? for same table and database as above: Set Variable [$sql_select; BE_FileMakerSQL ( Evaluate ( Quote( "SELECT * FROM "&$table_name&"" )) ; "," ; ¶ ; $fm_file )] Thanks kindly any suggestions or comments. (Fyi looked at this page for some hints about using Quote and Evaluate, not sure if need Evaluate here or not: https://community.filemaker.com/en/s/question/0D50H00006h99Tu/how-to-pass-down-variables-in-befilemakersql ) Actually, I'm not clear yet if the BE_FileMakerSQL() function and FileMaker native ExecuteSQL() function take exactly the same syntax, not sure if this is relevant here though.

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.

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.

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