Creating a Report to see if person paid dues

[johnsonm]

I'm creating a database for an group that asks other individuals for 2 seperate Dues, as well as 4 different additional donations. I setup two files; Dues.fp5 and Payment.fp5.

Dues.fp5 keeps track of the 170 individuals. The number of individuals will not change more than a few records a year. The main layout contains all the necessary fields (name, add, ph#, etc.) and a portal to Payment.fp5.

Payment.fp5 is a list of all the dues and/or donations that each individual makes. It includes the fields PaymentType, PaymentAmount, Date, CheckNumber.

The database is linked and working great. It allows the group the ability to keep track of multiple donations per individual, as well as print out reports for each Type of payment (dues & donations).

Now the end user would like to be able to print out a list of the 170 individuals with fields that state whether or not they have paid the two types of dues for a given year.

Ideally, it would output something like:

CustID Name Type_1_Dues Type_2_Dues

000001 Joe Schmo yes yes

000002 Bob Slob NO! yes

...

The added ability to Bold or underline the NO's would be nice. As well as creating a single list to see all the NO!s, for instance.

How would I go about starting this? Would I have to do it in the Payment.fp5? I'm having a hard time because when I try to create this report in Dues.fp5 it will only display the first record that was inputted (ie. if Type_2_Dues was inputted first it won't show Type_1_Dues in a report). When I try to create it in Payment.fp5 I run into an issue because there are multiple transactions per individual, so creating a simple "if/then" statement becomes difficult.

Could someone help me get this started? If you need more information let me know!

Thanks,

Mike Johnson

IS Support Specialist and Neophyte File Maker

[Oldsneekers]

On quick inspection of the problem, I think the easiest way to prink out the list requires two additions: First make a calc field for each type of dues that needs to be paid then make a printing layout with the yes no calc fields in the appropriate column. (The answer may require a different solution then presented here if you do not have different fields for each type of dues and payment.)

The calc field is as below. (I have a habit of adding a Cc the end of calc fields so that I know they are calculation fields.)

eg: field called DueAPaidYNCc and the calc is

Case(

Paid.fp5::PaidField=Paid.fp5DuesField, "yes",

IsEmpty(Paid.fp5::PaidField)=1, "no",

Paid.fp5::PaidField<Paid.fp5DuesField, "part").

If you want to make the "NO's stand out you would make 2 calc fields and format the No field font to bold. DuesPaidNoCc calc field would have the calc Case(IsEmpty(Paid.fp5::PaidField)=1, "No"). You would then overlay this field over the DuesPaidYesCc calc field that would have the calc Case(Paid.fp5::PaidField=Paid.fp5DuesField, "yes") and its font is formated to plain type. Be sure to make the top field have an invisible backgownd so you could the the yes field underneath.

Prin in the Dues file since this is the file that has the demographic info. The match field in payment file for the above relationship for you file structure is probably the calc of the person ID number, the year of the dues, and possibly the dues type (MFDuesPaid {IDNumb&" "&Year&" "&DueType"} . This is just an off-the-cuff solution suggestion and some tweaking may be needed. (notice the disclaimer).

Good luck.

[johnsonm]

Thanks for the fast reply!

I understand the basics of what you're saying, but unfortunately I am using a PaymentType field, instead of PaymentDuesA, PaymentDuesB fields.

So, do you think I could setup a script so that for each individual it would calculate (in Dues.fp5):

[Calculation in Dues.fp5: DuesAPaidYNCc]

!-- First Check if PaymentType = Type_1_Dues for all records in Payment.fp5

!-- that relate to each individual record in Dues.fp5. !!!This is the key!!!

If(

Payment.fp5::PaymentType="Type_1_Dues",

Case(

Payment.fp5::PaymentType=Paid.fp5DuesField, "yes",

IsEmpty(Payment.fp5::PaymentType)=1, "no",

Paid.fp5::PaymentType<Paid.fp5DuesField, "part")

)

My problem is still that I'm not sure how to go through all related records in Payment and get them to print out in Dues correctly.

ex: Let's say 'John' paid Dues B and Donation 1. He did NOT pay Dues A. This means he would have 2 entries in the Payment.fp5 file. The calculated fields would have to go through both Payment.fp3 records that relate back to 'John'. Record 1 in payment.fp5 says that John paid Dues B- DuesBPaidYNCc would have to return "yes". Donation 1 is skipped by the calculation because it is not one of the 2 types of Dues. After this is complete there would have to be a check

IsEmpty(DuesAPaidYNCc)=1,"no"

IsEmpty(DuesBPaidYNCc)=1,"no"

So how can this calculation find related records to the 170 individual records and perform the calculation x # of times based on how many related records there are? I think once I understand that I'll be able to figure this out on my own.

Thanks again!

-Mike Johnson