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!
By Peter Barfield
Well, I'm new to SQL and have used it in it's basic select mode.
Now I have been asked too create a dashboard using data from different tables to display. Which on the most part is OK and working great.
I have had a request for a top customer list for the month and previous month (say best 5 ranked from 1-5.) and not together so 2 seperate views
I am able to pull customers and SUM totals (seperately)for the month using SELECT DISTINCT I am able to get a list of the customers for the month. How would you then get a total or SUM of the invoice amounts for the given month with the customer name and their total ? also is there a similar function to FRACTION in filemaker?
Listed is my SQL Statement that just returns a ?
Which I understand to mean a syntax error however, being a SQL "Virgin" any halp would be appreciated. I know I am able to do this natively through filemaker however, as I said this is for a dashboard that uses data from a number of tables.
Here is my calc that is obviously wrong.
Currently working with Filemaker 15 Advanced.
Let ( [
ReturnSub = "\n" ;
SQLResult = ExecuteSQL (
"SELECT DISTINCT (a.\"Customer Name\"), SUM(a.\"InvoiceTotal\")
FROM \"Invoices \" a
WHERE a.\"Invoice Sub Total\" > ? AND a.\"InvoiceMonth\" = ? AND a.\"InvoiceYear\" = ? AND a.\"Job Status\" <> ?"
GROUP ON a.\"Customer Name\" ASC;
" " ; "|*|" ;
"0" ; Month(Get(CurrentDate))-1 ; Year(Get(CurrentDate)); "Giveaway"
) ] ;
Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] )
By Calvin Tomm
In list view I want to be able to "grab" an object so I can move the record up or down in the found set. Is there a way to do this? It would be just like how an item on an list in an iPhone gets moved up or down. For example, on a play list in Music, I can change the order of songs to be played.
Also, I would like to have this capability when I move my solution over to FileMaker Go
I recently amended a db from an old Lynda course, controlling shore excursions for river cruise ships. For my limited expertise (I am still a rookie at the very best) the construction is somewhat complex as there is many vessels, different routes, different charterers and consequently different shore excursion programs and prices. In some cases the shore excursion program of different vessels and operators is similar but not the same and so on.
I have 7 different tables partly related (pls see graph attached):
For every journey ::customers and ::trip are assigned a tour code and date (“tour code” is not unique as journeys repeat themselves during the season, “Tour Code” in conjunction with “Date” is unique)
Every tour operator/vessel has many trips on every journey. All table occurences and fields are uniquely defined by unique id.
Now I want to create an opening layout reflecting all relevant cruise information such as passenger list from ::customer and ::trip (shore excursions) for the specific journey, as well as other information in text fields.
But I seem to be unable to get the right relationship and layout setups together. I tried all possible relations and layout setup combination but nothing worked so far.
On the same layout I was able to either retrieve the passenger list from ::customer for each journey or the offered shore excursion (trip) list from ::trip but not both together on one layout.
What is it, I am doing wrong? Any ideas are appreciated
Apologies in advance if this is a vague question, or if it's been extensively documented, but I could do with some basic advice on moving filemaker tables to SQL. In terms of database development, I have only really known FileMaker.
We have a 60 table, 25 user FileMaker database which takes care of most if not all of the company's operations.
There is now talk of allowing other platforms (such as PowerApps/PowerBI) access to the data. One such reason is other people are interested in making apps/applets that serve specific functions. This would be simple if the data were held on a SQL database.
So if all the FileMaker tables were to be moved to SQL, what sort of a task is that in terms of keeping the existing FileMaker gui functioning?
Or. is there a simpler way to grant other platforms access to filemaker tables?
And of course, what are the downsides?