11 Replies Latest reply on Mar 17, 2015 7:58 PM by philmodjunk

    Omitting (or finding) records on the basis of the value of a summary field in a sub summary part

    mchancevet@gmail.com

      Title

      Omitting (or finding) records on the basis of the value of a summary field in a sub summary part

      Post

      Hello,

      I have an inventory file which tracks location, product information, batch information (specifics and expiry dates).

      Each location has a defined set of products with varying quantity requirements defined at the product level.

      For each product at a location, the required set can be made up of the same product via any combination of batchs. Batch data is recorded for each location.

      Each location is auditted and the quantities of each batch are tallied.

      I have a summary field in the table LocationContent::ProductTotal which sums the quantity of the product from all batches in that location.

      I need another field that performs a calculation that is basically defined as the quantity requirement for a product for a location, 'LocationRequirement::ProductQuantity'  minus the 'LocationContent::ProductTotal' field above to give the replacement amount. (this tells the staff member how many of product X to put in the location). It does not matter which batch any product is topped up with so long as the batch is recorded and tracked. I have a calculation field as defined above LocationContent::ProductReplacement

      Relationships:

      LocationRequirement::fkIDLocation= Location::ID

      LocationContent::kID=Location::ID

      LocationContent::fkIDBatch=Batch::ID

      Batch::fkIDProduct=Product::ID

      I need a report which lists all products in a location/s requiring product replacement.

      So far I have a list layout from table 'LocationContents'. The layout has a sub-summary part sorted by Product::Name. The sub-summary part has the field 'LocationContent::ProductReplacement'. So hopefully for a location I can see a list of batches and quantities for each product,  categorised by product and with a total quantity to top up for the whole product set. 

      But wait, there's more... 

      Most of the time, most locations do not need much replacement. Most of the product replacement  values are zero. I need to eliminate these from the found set and only display Products for which the summary of their batch records is less than the requirement for the product for that location.

      Any input warmly received,

      Regards,

      Morgan

       

       

        • 1. Re: Omitting (or finding) records on the basis of the value of a summary field in a sub summary part
          philmodjunk

          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.

          • 2. Re: Omitting (or finding) records on the basis of the value of a summary field in a sub summary part
            mchancevet@gmail.com

            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. 

            Morgan

            • 3. Re: Omitting (or finding) records on the basis of the value of a summary field in a sub summary part
              philmodjunk

              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.

              • 4. Re: Omitting (or finding) records on the basis of the value of a summary field in a sub summary part
                mchancevet@gmail.com

                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? 

                • 5. Re: Omitting (or finding) records on the basis of the value of a summary field in a sub summary part
                  mchancevet@gmail.com

                   

                  Some syntax questions.

                         
                  • My interpretation of the fm13 SQL reference pdf leads me to believe the query should read like

                  ExecuteSQL

                  (

                  SELECT l.TotalInLocation AS Tot

                  FROM LocationContent AS l

                  WHERE l.Location=Resource.LocationSearch

                  )

                         
                  • My interpretation of the fm13 function reference pdf leads me the believe the query should read like

                  ExecuteSQL(

                  "select (L.TotalInLocation

                  from LocationContent L

                  where L.Location = ?";

                  "";

                  "";

                  Resource.LocationSearch

                  )

                  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.

                  Morgan

                  • 6. Re: Omitting (or finding) records on the basis of the value of a summary field in a sub summary part
                    mchancevet@gmail.com

                    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).

                    ExecuteSQL(

                    "select sum (L.TotalInLocation)

                    from Product P join LocationContents L on P.ID = L.fkIDProduct

                    where L.fkIDLocation = ?";

                    "";

                    "";

                    Resource::KitSearch

                    )

                    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?

                    • 7. Re: Omitting (or finding) records on the basis of the value of a summary field in a sub summary part
                      philmodjunk

                      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.

                      • 8. Re: Omitting (or finding) records on the basis of the value of a summary field in a sub summary part
                        mchancevet@gmail.com

                        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.

                        Regards,

                        Morgan

                         

                         

                        • 10. Re: Omitting (or finding) records on the basis of the value of a summary field in a sub summary part
                          mchancevet@gmail.com

                          Thanks,

                          I think with the addition of 

                          fkIDLocation, fkIDBatch, fkIDProduct to the transaction table I should be able to make that work.

                          Morgan

                          • 11. Re: Omitting (or finding) records on the basis of the value of a summary field in a sub summary part
                            philmodjunk

                            That sounds right. Transactions becomes the "star join" table for the others.