Finds in Calculated Fields

[jbullydawg]

I'm trying to run a report that displays all thos individuals that have account balances greater than zero. The search is taking place in a calculated field 'TotalOwed' which takes a person's TotalCharged and subtracts their 'TotalPayments'...both of these are Summary fields.

I have no issue showing the report with everyone's balance but it gives me everybody in the database and not just those with TotalOwed > 0. Are calculation fields not designed to respond to finds like this?

My script looks like this:

Perform Find (TotalOwed > 0)

New Window

Go to layout

Adjust Window

Sort Records

Enter Preview Mode

Again, this gives me the report I desire just with every record visible. Any ideas on how to only show those folks with TotalOwed > 0 if the Find doesn't catch them?

Thanks!

[Ender]

Yeah, certain revs of FileMaker 7 & 8 can't do this type of search on an aggregate calc. I think it will work in 8v3 and later.

Or you could use a scripted method to update the Total in a local field, or loop through the found set to pick records that match.

[jbullydawg]

Could you be more specific about updating the Total in a local field? I'm not sure I follow.

[mz123]

Make sure that the layout you are going to in the new window is the same table occurrence as the one you're doing the search in. You might want to try to run the search after the new window opens and you're in the right layout. Not sure if that's the issue or not, but that might be part of the problem.

[Ender]

If the Total is a plain Number field, it can be indexed and searched very quickly. The trick is updating that Total field. You could use a looping process to set its value for each Individual record, or use a Replace Field Contents.

The actual calc for those would be Total Charges - Total Payments, where Total Charges is an aggregate calc Sum(LineItems::Amount) and Total Payments is an aggregate calc Sum(Payments::Amount). This assumes you have a relationship to a Line Items table with items purchased and a relationship to a Payment table for payments received.

[jbullydawg]

You bring up a good point in all of this and it may be my design. here's what I have setup so please let me know if I'm off base:

Table = Transactions

There is one transaction per record per student. that transaction could be a single Charge or a single Payment. Students make multiple payments and are charged multiple times so there is a record for each of those transactions.

Do payments and charges need to be in their own table? If so, can I use the Student table to bring them together for purposes of an Account Statement, or Balance Due reports?

I'm a newbie so be gentle with your laughter.

Thanks.

[Ender]

Since you're trying to find Students with an account balance, it makes the most sense to base the find (and aggregate calcs) there. Transaction records themselves don't know about the balance--the balance has to be calculated outside their scope.

You can use Transaction records for both charges and payments. If you have some field to distinguish between them, you can use that field to filter relationships for each type of Transaction. You can have a relationship for Charges and a relationship for Payments, (both to a TO (table occurrence) of Transaction, but one filtered by Charges and the other filtered by Payment).

The report will then show Student records with aggregate calcs for Total Charges, Total Payments, and Balance.

If you need sub-totals by month or something, it's a little more complicated. The Find might be run in the Student table, with the result set being transferred back to the Transaction table for sub-summarizing.

[jbullydawg]

Finally worked it out.

In the Students table I created a calculation field called TotalOwed which looked like this:

Transaction::TotalCharged - Transaction::TotalPaid

...where TotalPaid and TotalCharged are summary fields in the Transaction table.

Also, I had to move the Perform Find portion of the script so that it executed AFTER I had gone to the report's layout. For some reason that then allowed the Find to work its magic on the calculation field.

Thank you all for your suggestions!

[Ender]

In the Students table I created a calculation field called TotalOwed which looked like this:

Transaction::TotalCharged - Transaction::TotalPaid

...where TotalPaid and TotalCharged are summary fields in the Transaction table.

You may have inconsistent results with that. Summary fields are meant to deal with found sets, but the relationship from Student to Transaction is not dependent on the found set.

I'd recommend using the aggregate function Sum() to gather the totals over filtered relationships to the Transaction table.