Jump to content
Sign in to follow this  
jbullydawg

Finds in Calculated Fields

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.