1 2 Previous Next 23 Replies Latest reply on Jul 27, 2017 9:14 AM by philmodjunk

    SQL Question from sql beginner

    iconomydk

      I have an Sales-table connected to Line-Items-table via id’s

      My Sales can have 4 statuses – Quotation, Order, Invoice, Credit Note

      The Line-Items-table is connected to the Products table.

      I think everything totally normal.

       

      Now I am somewhere in the system looking at a Product_ID.

       

      Is it possible via SQL to calculate how many of this Product_ID are ”in order” ? (on Sales with Status: Order)

       

      Or do I need a field in the line-items table that keeps track of the status of the corresponding Sale to do this calculation ? ( would like to avoid this)

       

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

       

      Thanks

      Lars

        • 1. Re: SQL Question from sql beginner
          philmodjunk

          "(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.

          • 2. Re: SQL Question from sql beginner
            beverly

            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

            • 3. Re: SQL Question from sql beginner
              philmodjunk

              Hi Beverly,

              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

              • 4. Re: SQL Question from sql beginner
                beverly

                complete the query (with JOINS) and perhaps Lars can test if this is 'efficient' (or easy to do). Alternatives!

                beverly

                • 5. Re: SQL Question from sql beginner
                  iconomydk

                  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

                  • 6. Re: SQL Question from sql beginner
                    siplus

                    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:

                     

                     

                    Let ([

                     

                    list1 = ExecuteSQL("SELECT SalesID FROM Sales WHERE Status = ?";"";""; "Order");

                    list2 = ExecuteSQL("SELECT fk_SalesID From LineItems WHERE fk_ProductID = ?"; ""; ""; Product_ID)

                     

                    ];

                     

                    ValueCount(FilterValues(list1; list2))

                     

                    )

                    • 7. Re: SQL Question from sql beginner
                      philmodjunk

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

                      • 8. Re: SQL Question from sql beginner
                        iconomydk

                        Thanks Siplus,

                         

                        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

                        • 9. Re: SQL Question from sql beginner
                          beverly

                          perhaps! eSQL is just another tool (in an awesome toolbox) that sometimes is the best for the task and sometimes not.

                          beverly

                          • 10. Re: SQL Question from sql beginner
                            siplus

                            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.

                            • 11. Re: SQL Question from sql beginner
                              iconomydk

                              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"

                              • 12. Re: SQL Question from sql beginner
                                philmodjunk

                                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.

                                • 13. Re: SQL Question from sql beginner
                                  BruceRobertson

                                  Seems odd that sometimes you use / and sometimes you use \ .

                                  • 14. Re: SQL Question from sql beginner
                                    philmodjunk

                                    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.

                                    1 2 Previous Next