Running sum from portal records

[4Justme2]

I have a portal from a table REFERRALS_2_SERVICES on a layout.

With the following fields 4:

ID_R2S.pk (number, Indexed, auto-enter serial

Quarter (text) Sample data: Q1

Year (Number, in the Range 2012 to 2022, Max 4 characters) Sample data: 2012

Quarter_Year (text, Indexed, auto entry calculation replacing existing value) Sample data: Q1 2012

Service (Text)

I may not have formatted the fields Quarter and Year correctly but it's the only way I could think of.

I like to maintain several running counts calculations.

Result A: I want to keep a running count by Quarter-Year (the number of times a new record is added that matches a Quarter-Year)

Result B: I want to keep a running count by Quarter-Year-Service (the number of times a new record is added that matches a Quarter-Year-Service)

Not sure where to go from here. Any suggestions would be appreciated.

[LaRetta]

Hi 4JustMe2,

REFERRALS_2_SERVICES

I am unsure of the perspective here. Can you explain where this information will be displayed and explain its purpose? Do these fields reside in Referrals or in Services? Normally when I see Quarter fields and year fields, I would suggest that you use a true date result since it is then easier to use for this and other things.

Also, do you want the running totals to be for the found set (and for which table) or for all records (and for which table)? Would they display in portal?

[4Justme2]

Those are good questions. There's a specific reason why I can't use an actual date this in these tables. Referral services are often sensitive by nature. The organization could be referring someone to battered wives shelter, for example. I have to build this portion of the system about Quarters and years because there can be no link what-so-ever back to the person being referred to this service. If the court system were to requisition our records there should be no way they can link that person to the service their are being referred to. This protects the potential victim--ensuring their privacy.

We track the number of referrals made by staff member.

The layout is based on the context of the STAFF table. The portal is drawn from the REFERRALS_2_SERVICES table. Per staff member, I'm looking for a running total of all referral made via the portal by a particular staff member;

Another words, the portal would show 5 rows of referrals made by Sally Qtr1 and 3 referrals so far for quarter two. Somewhere on my layout (it doesn't have to be in the portal necessarily, I'd like to see a total number for Qtr 1 of 5, and Q2 of 3 on Sally's record. Secondly, I'd like to see the totals categorized by the type of referral made (2, child abuse; 1 battered wives), etc.

These of disturbing subjects that I'm talking about so casually--for that I do apologize.

[LaRetta]

Check out this demo by Comment showing how to aggregate portal data. It should work nicely in this instance.

http://fmforums.com/forum/topic/71906-getting-more-out-of-filtered-portals-3unique-values/

[4Justme2]

That's excellent! Very helpful. Many thanks... mp