"(In FileMaker I could find all Sales with status Order – go to related line-item records - do a constrain found on the product id – and get a summary of Qty)"
Actually, you can also do that by going to a line items layout and perform a Find for all line items where their related sales record is an "order".
And you can do something similar in SQL by using a Join to link line items and sales so that you SELECT from line items but specify "order" for status field in sales.
If you are referring to using the function, ExecuteSQL(), then you ONLY get a text result. What you can/cannot do with that results has many factors. To note: the SELECT does not leave you on any "found set" directly in FileMaker, it is only a bunch of text put in a field (or variable). If the result is a list of record primary keys, for example, you may be able to use that list as multi line key to get to your found records. If, however you return something else, the text needs further processing.
You may do some native finds in FileMaker which would just be more efficient than SQL, even if you must create a few extra fields (or not).
Sent from miPhone
if you check the original post again, you should notice that Lars wants a count of the records so the function is a good choice for what he wants here.
The first part part of the query would be:
SELECT count(*) FROM
complete the query (with JOINS) and perhaps Lars can test if this is 'efficient' (or easy to do). Alternatives!
Yes - of course I could do the find directly from the line-items table - silly me - just tried to give another view to my problem.
And yes I want a SUM ( not a count) of the qty field of the given records, so I think Beverly that sql would be a good choice :-)
I want to show the users of the solution what is in stock and what is already ordered.
My problem is how to construct this SQL
My idea is:
Select sum (qty) from lineitems where ... and then my skills stop me
You can do that with a SELECT and a JOIN, but if you have tons of data it might be slower than the following, which uses SQL just for extraction and nothing else:
list1 = ExecuteSQL("SELECT SalesID FROM Sales WHERE Status = ?";"";""; "Order");
list2 = ExecuteSQL("SELECT fk_SalesID From LineItems WHERE fk_ProductID = ?"; ""; ""; Product_ID)
Like we said, you need a join clause to link in the sales table. I don't try to write those from my phone as I prefer to have FikeMaker's SQL reference open at the same time to be sure that I get the syntax right.
But it you can select Product Documentation from the Help menu to find and open this document to learn about Joins for yourself. (It's in the FileMaker Pro section. )
I like your approach, but if I read and understand your calculation, that would give me a count of lines with the product id.
But I need a SUM - the qty ordered on each line can be different than 1
perhaps! eSQL is just another tool (in an awesome toolbox) that sometimes is the best for the task and sometimes not.
uh sorry, no problem, grab the FilterValues() result and stick it in a global which is a key in a relationship to your LineItems ivia global = LineItemsIDs.
In LineItems have a summary , Total Of Quantity. Maybe you already have it.
Look at that summary through the relationship and you have your result.
I get your point.
However I would very much like the solution to be independent of "where I am" (can't remember the English term)
So if someone could just try to write a sql statement, that did the trick, I would be very happy.
So far I have been solving this with a lot of script triggers that keeps updating qty in order per product in a separate table. This is very fast, when you want to see numbers, but it somehow fails sometimes. In stead I would be willing to offer some speed with an "unstored sql calculation"
SELECT Sum ( \"Qty\" )
FROM \"LineItems\" INNER JOIN /"Sales/"
ON /"LineItems.Order_ID/" = /"Sales.Order_ID/"
You'll need to substitute your field and table occurrence names in place of what I used.
And I don't actually write my queries in this format any more as I don't want to "lock down" any field or table occurrence names by enclosing them in quotes. I use a more complex expression that preserves the basic query while still avoiding the enclosing these names in quotes.
Seems odd that sometimes you use / and sometimes you use \ .
Good catch. I used the wrong one.
SELECT Sum ( \"Qty\" )
FROM \"LineItems\" INNER JOIN \"Sales\"
ON \"LineItems.Order_ID\" = \"Sales.Order_ID\"
I probably did not need the double quotes but do so out of what I see as "best practice" for this query format as it's a real pain to get a ? result just because a name had an unacceptable character or happened to be a reserved word.