sub summary not summarizing...?

[ron G]

I am using FM 11 Adv with OSX Lion

It's been a few months since I sat down to tweak my solution and I think I have forgotten something pretty basic... (Yes, I have consulted this forum and several books; to no avail)

I have a membership application.

There are 2 tables: Members & Dues

Members:

PK_MemberID

MemberLastName

Dues Status (Late or Current)

Dues:

FK_MemberID

DonationAmt

SummaryDonationAmt (Summary of DonationAmt)

I have created a report with a subsummary which 'breaks' on STATUS. I have sorted on STATUS.

It works.

But, if I bring the SummaryDonationAmt field into the Summary 'band' it seems to just include the record immediately below the 'band' .... and sometimes *not*.

What am I missing?

Thanks

[LaRetta]

Hi Ron,

The report should take place in the 'many' side where the summary field resides and the Status field should (probably) be in Dues. However, I sense that you might need a slight structural adjustment (maybe even join table). Can you create an empty clone of your file, zip it and post it here? If you are uncomfortable with that, you can PM one of us to take a look. :-)

[ron G]

Thanks LaRetta for the reply.

The relationships is one (Members) to many (Dues)

When I base the report layout on Members, I get a single record for each member for their current Status. Great.

However, the SummaryDonation field doesn't work.

If I base the layout on DUES, the SummaryDonation field works but for each member get's listed for each year they paid dues.

In other words, the report includes ALL the payment records. (see below)

For example, Jim Smith has a STATUS of 'Current' because his most recent payment has a year that is = year(get(currentdate). BUT, with a report based on DUES, it shows:

(sorted on Members::Status. Status is either 'Current' or 'Late')

Current $25 (summaryDonation)

Jim Smith 2011 $10

Jim Smith 2012 $15

Late $60

David Crocket 2009 $10

David Crocket 2010 $10

David Crocket 2011 $15

Walter Brennan 2009 $25

Can I get the best of both? ie, ..... this...

Current $15 (SummaryDues)

Jim Smith 2012 $15

Late $40

David Crocket 2011 $15

Walter Brennan 2009 $25

I tried making a calulated field in DUES::Status = Members::Status .... but that didn't work either.

Should this really be this difficult?

Thanks

If this doesn't clear things up, I will gut and zip my application and upload it. But, I don't see any button to allow me to do that?

[ron G]

Hi Ron,

The report should take place in the 'many' side where the summary field resides and the Status field should (probably) be in Dues. However, I sense that you might need a slight structural adjustment (maybe even join table). Can you create an empty clone of your file, zip it and post it here? If you are uncomfortable with that, you can PM one of us to take a look. :-)

Hi LaRetta,

I've been thinking about hwat you said about Status being in Dues. I even went so far as to start to add a DUES::Status field. Then it occured to me, the Status also gets changed to 'Deceased' , 'Demit', 'Suspended', 'Expelled' .... etc. And, that the Status is really a part of the Member information, like their birthday.

Supporting what you suggested is this article: http://forums.filema...osts/be76a023b9

I am primarily reluctant to change Members:: Status to Dues::Status because I will then have to find every script and report that uses Members::Status and redirect it to DUES::Status. yuk..

Your thoughts are appreciated.

Ron

[Vaughan]

I made a membership application some years ago (FMP 8) for a community radio station.

A membership lasts a certain period of time and may have a "level" such as silver, gold etc. The member can have multiple membership records, but it's the LAST one that determines their membership status: if the last record is current (ie the current date is less than the membership expiry date) then the membership status is "current". If the last record's expiry date is before today then they are "expired". If the last membership's date is 31 Dec 4000 (the last date that FMP can handle) then they are life members.

Doing it this way means that their status changes automatically and does not require manual updating by an operator. It just works.

[LaRetta]

Hi Ron,

There is a difference between a Member Status and a Dues Status. Let's assume Joe Smith owes dues for 2012 but he is suspended on 1/15/2012. Does he still owe? If his Status is Suspended, how can he also have a Member Status of Past Due? Maybe he can (that would be business call) but that Status field should not hold two different types of information. Or what if Joe Smith is Suspended on 1/15/2012 but he is current on his Dues? You need to split the logic.

So Member is Deceased, Suspended, Active (no current or late past due consideration in this field).

Now to Dues ... you should create a record (Type = Charge) when a charge is incurred (for year 2012 for example) and enter the dollars in an Amount field (as a plus). Then when payment is made, create another new Dues record as Type=Payment and dollars are minus. Then calculation in Members called cDue of Sum ( Dues::Amount) will show you who still owes.

Your report can then search for Active Members only (if you wish) and/or cDue > 0

One final consideration ... when are Dues due? Is it before the year starts or do they have a grace period before they are considered past due? Those business rules should be included in your logic as well. If someone joins on 1/3 and they are invoiced for 2012 and they are given two weeks to pay, you might not consider them 'late' until 1/18/2012.

ADDED: Ron, you may get two separate posts from me. My system crashed on the first one and forums showed that it was not saved (even after going to menu and back and refreshing) so I created it all again ... with a bit different input and wording. Then after I saved the second one, I see the first one is there ahead of it. Geez. LOL. The second one is better. :laugh2:

Edited January 25, 2012 by LaRetta

[ron G]

LodgeMembership777.fp7.zip

(This app isn't very pretty but... it isn't finished yet)

Hi LaRetta,

I will try and explain in more detail how I have set up the app.

The script I've been working on is Rpt Dues by Year. It works with the report Dues By Year Paid and Member.

(However, my inability to get the subSummary values working promptly means these two items do not work)

MEMBERS is the first layout you see when you open the app.

There is a MEMBERS::TYPE field. It is a dropdown list that holds these values:

Yearly

Life

Honorary

50 Year

When a Member record is created, picking the MEMBERS::TYPE is required.

Depending on the TYPE, when the treasurer goes to the Dues Portal and enters the date of payment, the system looks up if and how much dues are for the previously designated TYPE in the LODGE SETUP layout and enters it into the portal. This works great.

And there is a MEMBERS::STATUS field. This field is NOT capable of being entered directly. It's contents are entered depending on the results entered in other fields.

HERE ARE THE MEMBERS::STATUS contents:

Late

Current

Deceased

Expelled

Suspended

NPD (Non Payment of Dues)

When someone dies, we enter the date of passing in the appropriate field. Doing so, changes (by script trigger) the STATUS to "Deceased"

When someone is 'Expelled' or 'Suspended'... same process. That is, entry into a tabbed portal in those areas causes

Members::STATUS to show "Expelled" or 'Suspended". (This is found under the MEMBERSHIP tab and then in the sub tab designated as D-D-E-S-NPD (As in Deceased-Demit-Expelled-Suspended-NPD).

A status of 'Late' or 'Current' is determined by a script that first finds all the 'Late' or 'Current' member records and then

looks at MEMBERS::PAIDYEAR (the year they last made a dues payment) and compares it with Year(get(CurrentDate)). If PAIDYEAR is less, STATUS for that member becomes "Late'. Otherwise it is "Current". This script is run as part of the STARTUP script.

I agree that the 'Current' and 'Late' designations really apply to DUES and NOT Membership. (It just seemed so innocent at the time... ha).

What do you think of duplicating the 'Late' and 'Current' values in a new field called DUES::'CurrentLate'?

It seems that doing so would allow me create a layout report based on DUES with a SubSummary based on DUES:CURRENTLATE and thereby be able to bring in all the Summary Fields from Dues? In fact, I could keep the MEMBERS::STATUS field showing 'Current' and 'Late' (these values are added by script) but also change the script to put the same values into DUES::CurrentLate ? Since MEMBERS::STATUS and DUES::CurrentLate show the same 'Current' or 'Late' Values I can run reports based on Dues or Members using either value. (I still am perplexed why the sub summary doesn't work better on related tables).

Hi Ron,

There is a difference between a Member Status and a Dues Status. YES Let's assume Joe Smith owes dues for 2012 but he is suspended on 1/15/2012. Does he still owe? He doesn't have to pay. But, if he is 'Reinstated', he will have to make up back dues. When the 'Reinstated' dues are entered, his STATUS, via script, is changed to "Current". If his Status is Suspended, how can he also have a Member Status of Past Due? Answer: He doesn't get a MEMBERS::STATUS of 'Late' He gets a 'MEMBERS::Status of 'SUSPENDED'. Maybe he can (that would be business call) but that Status field should not hold two different types of information. (Seems right to me) Or what if Joe Smith is Suspended on 1/15/2012 but he is current on his Dues? Answer: He would get a STATUS of SUSPENDED and he would have to work out with his organization how to handle the dues apportioned after the Suspension. You need to split the logic.

I know all this is somewhat vague but there are over a 1000 local organizations and each one gets a lot of latitude in how to 'book' refunds etc.

So Member is Deceased, Suspended, Active (no current or late past due consideration in this field).

Now to Dues ... you should create a record (Type = Charge) when a charge is incurred (for year 2012 for example) and enter the dollars in an Amount field (as a plus). If I understand you correctly, 'Charges' are NOT incurred. Each member decides if they want to be a member each year. Think of it as a CASH vs an Accrual accounting system. ie, revenue is recognized when received not when it is 'due'. Then when payment is made, create another new Dues record as Type=Payment and dollars are minus. Then calculation in Members called cDue of Sum ( Dues::Amount) will show you who still owes.

Your report can then search for Active Members only (if you wish) and/or cDue > 0

One final consideration ... when are Dues due? January of each year. Is it before the year starts or do they have a grace period before they are considered past due? Since it is a Cash system, it doesn't matter. If someone is late and pays in March, for example, their dues are recognized when received and they pay a full 12 months. Those business rules should be included in your logic as well. If someone joins on 1/3 and they are invoiced for 2012 and they are given two weeks to pay, you might not consider them 'late' until 1/18/2012. I have it set up that EVERYONE is 'Late' on Jan 1. When their dues are booked, the system changes their STATUS to 'Current'. Producing a report showing who is 'Late' or 'Current' is what I am trying to do.

ADDED: Ron, you may get two separate posts from me. My system crashed on the first one and forums showed that it was not saved (even after going to menu and back and refreshing) so I created it all again ... with a bit different input and wording. Then after I saved the second one, I see the first one is there ahead of it. Geez. LOL. The second one is better. Oh no!!! You must be running Windows? I hope this 'missive' doesn't bore you to tears....

I really appreciate your thoughts.

Regards,

Ron

[LaRetta]

Account and password?

[ron G]

Account and password?

RE: account and pw.

Just type in: admin and press enter.

Although I don't want and have designed the app for 'accounts and passwords' I was told (in a different forum) that this 'demand' by FM for account and pw will only happen once. That seems to be the case. Plz let me know if you can't get it.

Thank you

Ron

[Vaughan]

Although I don't want and have designed the app for 'accounts and passwords' I was told (in a different forum) that this 'demand' by FM for account and pw will only happen once.

Err, no.

All files must have at least one active account. If you want the file to open without prompting, go to the File Options (in the File menu) and set the option to automatically open the file with an account name and password.

[ron G]

Err, no.

All files must have at least one active account. If you want the file to open without prompting, go to the File Options (in the File menu) and set the option to automatically open the file with an account name and password.

Perhaps I misunderstood in the first place. This is what I've got and according to LaRetta, it prompts..

[Vaughan]

The file you posted is set up so it won't prompt for password. Dunno what's happened for LaRetta.

With regards to your file, I see you've used custom menus and removed the Manage Database etc menus. If you want to prevent users from modifying the file tables and fields then create another account with restricted privileges, and set this account to open by default.

It's worth spending time to understand FMP's security model, and the necessity for using it.

LodgeMembership777.fp7.zip

[ron G]

The file you posted is set up so it won't prompt for password. Dunno what's happened for LaRetta.

With regards to your file, I see you've used custom menus and removed the Manage Database etc menus. If you want to prevent users from modifying the file tables and fields then create another account with restricted privileges, and set this account to open by default.

It's worth spending time to understand FMP's security model, and the necessity for using it.

You're right. I need to up my understanding of FM security model.