Simply embed into sub query
WHERE id_part IN (SELECT id_part FROM ServiceParts WHERE id_service =?) AND id_project =?
" ; "" ; "" ; $_service.id ; $$_project.id )
For joining, we need to know what name the fields have.
[H]ave been ORDERED by the boss to use the ExecuteSQL function from now on.
Um ... why?
I suppose because he is from the SQL world - and does not understand my CustomFunctions and Filemaker stuff
And doesn’t want to bother to learn, I suppose.
Has anyone bothered to explain the consequences / impacts of this decision? Performance, development time, maintainability ...
[quote]And doesn’t want to bother to learn, I suppose.[/quote]
:-) Seriously ?
He has far more important stuff to be getting on with - he runs these multi-million pound projects and is full hands on in most aspects - I am a very small cog indeed in his engine. I do Filemaker, and web, and in the past did some SQL - so it's logical for him to simply say "start using sql"
So, I am taking a FM11 solution that was 2 years in the building, and turning it into a FM14 application, this includes using SQL
Brilliant - of course, sub queries - I forgot about them.
Many thanks - excuse the late answer, I'm in Thailand just now and the electricity (as well as the internet) is a bit up-and-down
so it's logical for him to simply say "start using sql"
It may or may not be logical to say it, but it is not logical to use only that. ExecuteSQL() calls is just one of many tools to achieve the desired end result. It is NOT the best choice in all scenarios. The only right approach is to try the different options and pick the one that performs the best.
ExecuteSQL() can be extremely fast. But it will exponentially slow down the more you ask it to do (not linearly, exponentially). So the more JOINs you add the slower it gets so there definitely is a cross-over point where it does not make sense to use it.
Additionally, there are some things that are just better done using native FileMaker functionality. Relational joins, for example, are cached when you first open a file. So if it's on your Graph, duplicating the join in SQL just adds unnecessary overhead. And aggregation in SQL is still quite slow versus using summary fields.
You mentioned Custom Functions. Can you even replace that functionality using ExecuteSQL?
Anyway, there are a couple of things you can do to help the situation. Make sure you have no open records when you submit the SQL query (props to Wim for that invaluable tip). Use PSoS if appropriate. And try to reason with the boss.
Well, he will leave most stuff to my better judgement - so - if I change something, test it - and it is unbearably slow - then I set it aside, use the original method - and add a note to the upgrade documentation.
The sort of stuff I will change to eSQL are the simple queries asked about above.
I'm aware that it will simply NOT replace some of the stuff I have already done.
My boss is very reasonable - he just wants to move forward.
But thanks for all the tips -