4 Replies Latest reply on Nov 11, 2015 1:38 PM by okramis

# How can I calculate a sum for a found set based on a related field's first letter

Hey all! I can't seem to nail this... I want the calculation to look thru the found set of records only looking at the first letter of a particular field and summing the "Used" field of all the records with that letter.

Something along the way of: Sum the "Used" field of all the records in which the "Roll #" field starts with "A"

• ###### 1. Re: How can I calculate a sum for a found set based on a related field's first letter

Do you just want to see all the records that only start with A and a have the 'used' field summed, or do you want to see it as you have it and have the 'used' field for each group summed?

If it's the first way then just a find for records that start with the A in Role# should do the trick, sorted by roll number with the sum field in the footer should work

• ###### 2. Re: How can I calculate a sum for a found set based on a related field's first letter

Thank for taking the time to write Steve! That is true. And while it works, in this particular case the found set of records is filtered by day. The records shown above pertain to the first day and are sorted by Roll # but are mixed (they contain A, B, and S types) and must be shown together in the report. I wish to show at the top of the report the subtotals of each record subgroup seperately (A = Sum of A, B = Sum of B, S = Sum of S) . Like a summary but not at the top or bottom of each sorted group,instead at the top of the report.

• ###### 3. Re: How can I calculate a sum for a found set based on a related field's first letter

See if this is what you are looking for:

Summary Report in a Filtered Portal | FileMakerHacks

• ###### 4. Re: How can I calculate a sum for a found set based on a related field's first letter

Assuming you have a primary key "RecordID" in your table:

then you can fill 3 \$\$variables like this:

\$\$SumA = Let ( [

_ids = Substitute ( YourTable::IDList ; "¶" ; "," )

] ;

ExecuteSQL ( "

SELECT SUM(Used)

FROM YourTable

WHERE \"Roll #\" LIKE ? AND

RecordID IN(" & _ids & ")

" ; "" ; "" ; "'A%'" )

) //end let

do the same for \$\$SumB and \$\$SumS

Regards

Otmar