Summary Field Issue

Question

I am at my wits end and have decided to reach out for help. This is the same project that I was previously posting for assistance with API calls. I moved on from using API's and have revamped the design but, I am stuck on one piece at the moment. Hopefully, someone can explain where I am going wrong.

SETUP
- Meetup Credentials table with Time, Cost, Date, Name, # of Checkins, etc
- Membership table (parent to Meetup Credentials) with Name, Type, Payment, Balance, etc
- Check Ins table (child table to both Membership and Meetup Credentials tables) with lookup fields to each of the above fields

SCENARIO
It's possible for one Member to have multiple Check Ins to the same Meetup. A Check In is done when a Member wants to access the online event (whether it's about to start, already in progress, or has already occurred). Members are not charged for additional Check Ins for the same Meetup. I am trying to get a summary of how much has been spent by the member, so I can have the Balance updated accordingly. The problem is, I cannot get a sum of the cost for each unique Meetup a Member has Checked Into. I did create a formula field in the Check In table to combine the Member Name, Meetup Name, and Meetup Date into a string but, apparently, I don't now what to do to take advantage of that for capturing the proper cost. To give a visual:

MEETUP CREDENTIALS DATA
- MemberA, MeetupOne, 08-01-2020, $4, etc, etc, etc
- MemberA, MeetupOne, 08-01-2020, $4, etc, etc, etc (same data due to multiple check ins)
- MemberA, MeetupTwo, 08-05-2020, $4, etc, etc, etc

The Summary field I created is showing a total cost of $12 but, there are only 2 unique meetups for MemberA so, I need that total cost to be $8. Hopefully, someone can successfully guide me to a solution. Let me know if anything else is needed from me, in order to assist. Thank any and everyone in advance for help.

James

------------------------------
James Carr
------------------------------

MarkShnierYou · Answer

So if I have the story right, a member comes to a meet up but does multiple check-in's. The Issue is that they get charged for coming to the meet up and not for each check in.

So the only way to know how many meet ups an individual member went to is to have a table where the key field is a concatenation of the Related Member and the Related meet up. Table would be called Member Meet up Attendance.

I would set up the table with that key field as a text field and initialize it by running a summary report of all the check ins summarized by the field on the checkins table which is a formula text concatenation like

List("-", totext([related member]), totext([Related Meet up])

This would be a summary report. You would then copy that report over to the unique member meet ups table. That is just to do a one time initialization of the table.

Then on the Member meetups table you would put in a field called square [Member- Meetup Exists?] This will be a formula checkbox field set to the formula of

true

Then you would look that up down into checkins.

Then you were set up an automation so that when a checkin record is created and that look up field is equal to unchecked meaning that no member check and record yet exist in the automation would create one.

Then once you are that far you can either run reports off that member meet up table or you may just find it easier to flag check ins ends where the record id of the check in is equal to the Minimum Record ID of all check ins on the relationship where one member meeting has many checkins. In other words you will flag the first check in at a meet up as the one that you will charge four dollars for and ignore the rest on your reporting.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

Sign up

Login to the community