Sum ( PortalTable::Staff ) + 3
Hi Phil - surely this would just give me the total of that field.
Here is an e.g. portal table:
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:
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.
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.
Hi - perhaps the Execute SQL would work.
How would I construct the calculation?
Without specific field and table occurrence names, constructing a query is problematic.
SELECT sum ( \"qtyfield\" ) FROM \"Assigned Services\"
\"BookingsID\" = ? AND
Lower ( \"yourField\" ) = 'additional musician' ;
"" ; "" ; BookingsID ) + 3
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
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:
SELECT sum ( \"Qty\" ) FROM \"Assigned Services\"
\"BOOKINGS ID MATCH FIELD\" = ? AND
Lower ( \"Item\" ) = 'additional musician' ";
"" ; "" ; Bookings::BOOKINGS ID MATCH FIELD ) + 3
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.
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:
Each example in this file is a working example and comes with detailed documentation on how it was set up and how it works.
I do have FM Advanced.
The Execute SQL doesn't seem to work...
I'll also have a look at the link supplied!
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.