Record Select Formula

[lm1340]

I am trying to find a formula that will choose records based on if any object meets a certain criteria.
Ex: Query pulls all customers with an outstanding balance; I want to include ONLY those customers that have also made an additional transaction since 3/1/2015, but the invoice should list ALL outstanding transactions.

If John Smith has an outstanding balance from 7/1/2014 and had additional transaction on 3/9/2015; the invoice should pull both transactions.

If Jane Doe has an outstanding balance from 7/1/2014, but has made no additional transactions, I don't want to include her in my record pull.

 

[pmax9999]

The simplest (but least efficient) approach would be to use a record selection that returns all records with an outstanding balance, group by Customer, then use the following Group Selection formula:

Maximum({Table.TransactionDate}, {Table.Customer}) >= Date(2015,3,1)

Note: This assumes the date you want to include is from 1 Mar 2015. If it is 3 January (which is what 3/1/2015 means to me) the last bit of that code would be "... = Date(2015,1,3)".

A more efficient but more complex approach would involve adding the transaction table twice, both joined to the Customer table. Use 1 of the transaction tables to identify the customers you want included (using a Record Selection formula that returns customer records based on balance and subsequent transactions), then use the second instance of the transaction table to display all the transactions for the relevant customers.

It is a little difficult to be specific based on the details you have provided but hopefully it points you in the right direction.

Cheers
Pete

 

[lm1340]

Thank you!

I actually got it with the simple and less efficient method. I appreciate your help.