Hi Gurus,

Trying my hand at the new Filemaker 12 executeSQL function, but can't seem to figure out how to do a count based on 2 conditions.

I have a projects table and I have a related project contacts table. A project can have many project contacts related to it and they can belong to different "types" of contacts. The choices I have for the types are Bill To, Ordered By, Ship To and Install To.

The user can add as many contacts to a project as needed, but if they have more than one "Bill To" type I want a script trigger to fire and ask them to choose which one they would prefer to use for the pulling in the tax rates and terms. The only reason I don't make the Bill To type unique (i.e. one bill to type per project) is that we occasionally have 2 different customers who are each paying half of the project cost.

When a new invoice is made related to the project the accounted can select which one to use for that current invoice (a project can have many invoices) and have the correct tax rates pulled in. They could then select the other bill to type for another invoice and pull in their tax rates.

I have created the usual script that grabs the current project ID and off to the side creates a new window, goes to the project contacts table, enters find mode, sets the project ID and sets the type Bill To, and does a find. If there is more than one record than I bring up a dialog box telling them to select one for the tax rates lookup and finishes the script by going back to the main layout and setting the customer ID in the customer tax rate lookup field in the projects table. The script trigger is on "object save", so this has to run every time someone adds / modifies the type field.

From what I have read, the new executeSQL function should simplify this.

I have created a calculation field in the projects table called

c_CountBillToContacts.

This is the calculation I think would work, but I'm not sure with this new function on how to set the variable for the current project ID the user is on.

ExecuteSQL (

"SELECT count (*)

FROM \"project_PROJECTCONTACTS\"

WHERE \"project_PROJECTCONTACTS::Type\" = 'Bill To'"

and

\"project_PROJECTCONTACTS::_kf_ProjectID\" = (the project ID of the current project that the user is on)

; "" ; "" )

Any help would be appreciated on how you would approach this.

Have you looked at this http://fmforums.com/...25-sql-builder/

also there are several blog posts regarding SQL features in this blog http://www.filemakerhacks.com/

In your query remove the table occurrences

ExecuteSQL (


"SELECT count (*)

FROM "project_PROJECTCONTACTS"

WHERE "Type" = ?"

AND

"kf_ProjectID" = ?"


; "" ; "" ; "Bill To" ; PROJECTS::k_ProjectID )