9 Replies Latest reply on Mar 25, 2017 11:22 AM by philmodjunk

    Query about counting specific information in portal

    mdc91

      Hi there,

       

      Please excuse this if it is a bit of a newbie question.

       

      take this as a portal table (Assigned Services) in my main database (Bookings):

       

      ProductQtyPrice

       

      In a standard booking there will be 3 members of staff, and an optional assigned product is an extra member of staff.

      In my bookings table I'd like to have a row with the total amount of staff needed.

       

      In plain english my function would be:

       

      If no "additional staff  product" present in Assigned Services portal then total staff needed = 3

       

      if "additional staff product" is present in assigned services portal then the sum would be: Qty of Additional staff (from portal row) + 3 = x

       

      How would I write a function to calculate this?

        • 1. Re: Query about counting specific information in portal
          philmodjunk

          Sum ( PortalTable::Staff ) + 3

          • 2. Re: Query about counting specific information in portal
            mdc91

            Hi Phil - surely this would just give me the total of that field.

             

            Here is an e.g. portal table:

            Screen Shot 2017-03-25 at 11.13.12.png

            As there are 4 additional musicians I'd like to add that to the 3 original musicians equaling 7.

             

            However, in some instances/bookings there will not be an additional musician entry:

            Screen Shot 2017-03-25 at 11.14.52.png

            or in other words this row in the portal will not always be present.

             

            PS the portal titles are:

            Item | Price | Qty

             

            So basically I want to:

            Look up the items - see if "Additional Musician" is an entry

            if "Additional Musician is an entry then calculate:  the QTY relating to additional musician + 3

             

            This sum would be present in my Bookings table.

            • 3. Re: Query about counting specific information in portal
              philmodjunk

              Either use ExecuteSQL to return the number of additional musicians with WHERE clauses to specify the BookingsID AND 'Additional Musician' and add 3 or set up this calculation field, Qty musicians in the Assigned Services table:

              If ( yourField = "Additional Musician" ; Qty + 3 )

               

              in this second case you'd still need sum ( Assigned Services::Qty musicians ) to show this value from bookings.

              • 4. Re: Query about counting specific information in portal
                mdc91

                Hi - perhaps the Execute SQL would work.

                How would I construct the calculation?

                • 5. Re: Query about counting specific information in portal
                  philmodjunk

                  Without specific field and table occurrence names, constructing a query is problematic.

                   

                  ExecuteSQL ("

                  SELECT sum ( \"qtyfield\" ) FROM \"Assigned Services\"

                  WHERE

                       \"BookingsID\" = ? AND

                       Lower ( \"yourField\" ) = 'additional musician' ;

                  "" ; "" ; BookingsID ) + 3

                  • 6. Re: Query about counting specific information in portal
                    mdc91

                    Hi Phil,

                     

                    I am slightly confused - these are my table occurrence names as what I can assume you are wanting from your titles but I don't understand the ", /, etc in the function.

                     

                    QTY - Assigned Services::Qty

                    Services table - Assigned Services

                    Bookings id - will either be Bookings::BOOKINGS ID MATCH FIELD or Assigned Services::BOOKINGS ID MATCH FIELD

                    yourfield - Assigned Services::Item

                    bookings id - again either Bookings::BOOKINGS ID MATCH FIELD or Assigned Services::BOOKINGS ID MATCH FIELD

                    • 7. Re: Query about counting specific information in portal
                      philmodjunk

                      but I don't understand the ", /, etc in the function.

                       

                      All references in the query refer to your portal's table occurrence, Assigned Services, but SQL has its own rules for what makes a valid field or table occurrence name. A leading underscore, spaces in the name or a name that matches a reserved SQL word such as 'Date' all will produce a syntax error in the query. Enclosing the name in double quotes causes the SQL interpreter to correctly treat the quoted text as the name of a field or table. Since quoting a name that does not need to be quoted, does not produce an error, many of us just quote every name. In your case, not knowing every specific name was sufficient reason to put in those quotes. The \ is the "escape" operator that causes FileMaker to treat the following character as just a character and not (in this case) the operator that marks the end of a quoted string of text.

                       

                      To insert your names into the query produces:

                       

                      ExecuteSQL ("

                      SELECT sum ( \"Qty\" ) FROM \"Assigned Services\"

                      WHERE

                           \"BOOKINGS ID MATCH FIELD\" = ? AND

                           Lower ( \"Item\" ) = 'additional musician' ";

                      "" ; "" ; Bookings::BOOKINGS ID MATCH FIELD ) + 3

                       

                      Note:

                      There was a typo in my first post of this query. I've added double quotes before the ; in the next to last line of text.

                       

                      Other notes:

                      I used Sum so that if you put in "additional musician" more than once for a given record in bookings, this query will return the sum of Qty over those multiple entries. That gives you a touch more flexibility here. I used Lower (   ) here because, unlike FileMaker, text comparisons are case sensitive. 'Additional Musician' does not equal 'additional musician'. So I used Lower here to make the text comparison insensitive to case.

                       

                      And a final note:

                      I've tried to keep this as simple and flexible for you as possible, but in my own uses of ExecuteSQL, I do not write queries like this because they enclose table occurrence and field names inside a quoted string. Should I or another developer later rename a field or table occurrence used in this query, this query fails and returns a ? indicating that there is a syntax error in the query.

                       

                      The method that I use employs custom functions that both assist in the evaluation of the query and also in the creation of the query expression itself. If you have FileMaker Advanced and want to explore the methods that I use, see the "Flexible SQL" tab in "Button Bars as Value Lists" in:

                       

                      Adventures in FileMaking #2-enhanced value selection

                       

                      Each example in this file is a working example and comes with detailed documentation on how it was set up and how it works.

                      • 8. Re: Query about counting specific information in portal
                        mdc91

                        I do have FM Advanced.

                        The Execute SQL doesn't seem to work...

                         

                         

                        I'll also have a look at the link supplied!

                        • 9. Re: Query about counting specific information in portal
                          philmodjunk

                          doesn't seem to work

                           

                          Doesn't give me enough info to help you. I suggest that you get the "simple" query shown here to work before you try setting up a more complex version such as how I do it.

                           

                          Do you get a question mark result? Or does some other problem occur?

                           

                          You might copy paste the exact text that you've entered into a reply here so that I or someone can take a look at the specific query that you attempted to make work.

                           

                          If all else fails, I did describe an alternative method that does not use SQL to get this value.