matching latest transaction to master file 1

[Alincon2001]

Boss wants report of tenant information: name, id, rent, etc. and latest payment transaction for each tenant. How can I link the files so that I get the latest tenant payment transaction from the history file,
for each tenant on the master file? (some may have not paid in several months)
Also, everything has to fit on one line because the report will be exported as an Excel spreadsheet.
Thank you for you help.
Richard Miller

 

[dgillz]

Link the master file to the history file by tenant ID. Do a record selection formula that picks only the payment transactions from the file (I am assumming there are other things in there besides payments). Write a report with all the fields you want. At this point, you will have ALL payment transactions, not just the latest.

To get the latest, Insert a summary by tenant and sort by payment transaction date. Then insert a running total count field, evaluate on every record, reset=change of grouip-tenant. Then conditionally suppress the details section of the report if {#count}>1.

Post if you have questions.
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[Alincon2001]

Thank you. I had a little trouble with
the suppression formula, but your solution
works well. Some of the tenants had multiple
payments on the same date, and the report only
shows one, but I don't see how that can be
overcome.
Thanks again
Richard Miller

 

[dgillz]

Do another level of grouping of payments by date by tenant. Then make the running total field evaluate on change of group date, reset on change of group tenant.

Then conditionally suppress the group subtotals if {#Count}>1
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office