AnsweredAssumed Answered

Write an SQL query

Question asked by rkutcher on Feb 12, 2019
Latest reply on Feb 12, 2019 by steve_ssh

I'm trying to define a field calculation using Execute SQL.

 

I have two tables - Report and Charges - Report is the parent, Charges is the child

 

There is a filtered portal  on a Report layout.  I have a  summary field in Charges that shows the sum of a Field.  This summary field always shows the summary of all child records.  The filtered portal filters the Charges records so the only records where Field = "No" are shown.

 

I want to know the sum of only the filtered records.  In other posts I see that Execute SQL is a way to summarize the filter records and, as I understand it, I have to write an SQL expression that achieves the same result as the filtered portal.

 

I have been going over the Filemaker Execute SQL instructions and see the examples that show functions like SELECT, FROM, WHERE, etc.  The functions are always shown in all caps.  When I try to write the SQL expression and use the all caps functions I keep getting err]or messages like Table not found, field not found or not enough functions.

 

What I think I need to do is SELECT the records in Charges WHERE the Field = "No" and then SUM those Fields

 

Can anyone help me translate what I think I need to do into anSQL expression that suits the format following:

 

sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... }

Outcomes