I would not use a summary field nor a calculation based on a summary field for this. I'd set up a calculation field and relationships that produce this list from the context of the Products table. In your case, ExecuteSQL calculations to produce the location specific subtotals may be much simpler. Once you have totals on such a layout, you can perform a find for the records that meet your re-order criteria.
Thanks again Phil,
I'll have a try at that. I guess I need a new TO Product | SumBatch or similar and relate it to the Location table, maybe with a join TO of Batch. I'm confused on this but I think if I can get the TOs and relationships right I should be able to use the SUM function in the calculation field and point it to a Batch TO related to the Location.
And on the topic of confusion, I don't understand the ExecuteSQL reference. The FM Help on this indicates it is for ODBC or other external data sources. My aim is to have the audits conducted on mobile devices running a synched FMGo13 file. I'm missing something, However for clarity my adventures in filemaking have not taken me into integration with external data sources, yet. I'm a little scared of it. My background is veterinary practice (I started with FM to build a clinical record keeping system as my practice is very small and I could not afford the proprietary ones), I live on an Island with a very small population base. I found additional work in a research facility and my forays in FM encouraged my manager to get me working improving our systems. 2 years later and our entire administration financial/planning/ content/ inventory system is running on my work and getting noticed ( for the right reasons so far) by our parent faculty management. I guess ODBC and SQL can't be too far away for me.
There is an Execute SQL script step that is used with ODBC. There is an ExecutesQL() function that is used in a calculation--such as a calculation field to query your FileMaker Pro tables. This function was newly added with Filemaker 12. It can be useful in problems like this that I characterize as a "selective sum" where you want a sum not of all related records but just those matching specified criteria. As you have outlined, this can also be done by adding a new occurrence with match fields that match only to the records you want to sum, so you really have two basic approaches that you can use for this.
Ok I've been checking out the fm13 function reference and the SQL reference and this looks like the answer to my dreams (I may need to get better dreams). So far it seems to me using this method I could interrogate at the Product level for 1 location at a time. (i.e. the final report will be for 1 location at a time.) using the 'Where' parameter to define the location. I can't see any reference to passing in script parameters or variables ( to define the location) so it seems to me I may need a utility/ search field (global?) either on a resource table or the product table to define the location and pass it into the SELECT statement. Am I missing something?
Some syntax questions.
- My interpretation of the fm13 SQL reference pdf leads me to believe the query should read like
SELECT l.TotalInLocation AS Tot
FROM LocationContent AS l
- My interpretation of the fm13 function reference pdf leads me the believe the query should read like
from LocationContent L
where L.Location = ?";
I assume the difference is that one is a script step and the other is a function, however my excursions into syntax land always leave me befuddled and I often find the commentary/explanation of syntax wanting (for my needs). In the cases above the table alias syntax is completely different for each instance. Am I to take it that the "?" (in the 'Where' element of the Select statement; page 222 of the function reference) is some type of variable identifier and that it is defined in the arguments parameter of the function? Could I perhaps pass a variable in to the function in the argument parameter? I could not find reference to "?" in the SQL reference, but it seems absolutely critical to the Execute SQL function in the example given in the function reference.
Unfortunately, the function reference refers you to the SQL reference for guidance on SELECT statement syntax but they seem to have different systems to each other? That's not very handy.
I'll try it out in the data viewer.
My examples above fail to account for the fact that SQL does not recognise relationships.
I have tried the following but it does not seem to do the trick yet - (it is returning either the total for all of the individual product regardless of location or it is returning all products in the location, I have not elucidated which yet).
"select sum (L.TotalInLocation)
from Product P join LocationContents L on P.ID = L.fkIDProduct
where L.fkIDLocation = ?";
The function is context non-specific but is it record specific - Do I need to pass the current Product ID in to the function? how may I do so if that is the case?
To go back a few posts, all of your examples are calculations. None are script steps.
You seem to have the right idea, but I am not sure why you have named the field "kitSearch" when you want to specify a product ID and a location. ExecuteSQL() is oblivious to current record, found set and layout context except for the values you pass to as you do with the "kitsearch" field.
And as I can't see the design details of your database, but "total in location" reads like either a sum function calculation field or a summary field. A summary field would not be the correct choice here if that's the case.
If you have a series of transaction records each of which increase or decrease inventory at a given location, then your SQL query might follow this pattern. Say you have a cBal calculation field defined as: Qtyin - QtyOut. Then the Sum of cBal for all records for a given Product ID and location would return the desired sub total:
ExecuteSQL ("SELECT Sum ( cBal ) FROM Transactions WHERE fkIDPRoduct = ? And fkLocationID = ?" ; "" ; "" ; Product::kIDProduct ; Product::gSelectedLocationID )
gSelectedLocationID would be a global field so that you can use a value list to select a location and then a list or table view of all you products records will list each product once with a filed that shows the total at that location. This then is a layout where you can perform a find for all records where the value returned by this field is less than a specified value and there are ways to compare that value to a reorder point field as part of your calculation in order to find all records that need to be re-ordered, but with a different re-order point specified for each product (or each product at each location)
But I'm not sure if you have a table that corresponds with my example of a Transactions table here.
Yes my naming is an oversight. This file was initially begun by a colleague. I have renamed many items in the file to sit within the naming conventions I am attempting to assert. KitSearch was an omission in this process but was a pre-existing global field I could repurpose for this exercise.
I have the ExecuteSQL function behaving itself now. Thank you very much I can see this has been a very productive learning exercise for me. I like green eggs and ham (ExecuteSQL) , I like them Sam I Am.
My report from Products is working well.
I do not have a transactions table as such. At the point of replenishment /audit, a staff member could replace with
- the same batch
- a different batch
- a mix of batches
- create a new batch
- change the requirements and replace with a new product or delete. add a product
- leave unfilled and come back later.
Maybe I should explore this possibility and see if I can make it work for this solution.
I think with the addition of
fkIDLocation, fkIDBatch, fkIDProduct to the transaction table I should be able to make that work.
That sounds right. Transactions becomes the "star join" table for the others.