Discussions

Expand all | Collapse all

Summary Field Issue

  • 1.  Summary Field Issue

    Posted 08-12-2020 15:47
    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
    ------------------------------


  • 2.  RE: Summary Field Issue

    Posted 08-12-2020 16:17
    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
    ------------------------------



  • 3.  RE: Summary Field Issue

    Posted 08-12-2020 18:11
    Thanks, Mark.  I had my fingers crossed for something less involved.  :p  While reading through and thinking over your explanation, I came up with this question.  When additional people need to be added for future check ins, will I have to have them added to the Membership table and update the Member Meetup Attendance table before the solution will work for them?

    Can automation be setup to remove duplicate records (if any) once per day?  The likely that multiple check ins by the same individual for the same meetup in the same day is extremely slim so, if this automation can occur (and be setup in a less complicated fashion), this might be a good alternate course of action until I get the other bells and whistles in place.

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



  • 4.  RE: Summary Field Issue

    Posted 08-12-2020 18:19
    Well yes for this

    When additional people need to be added for future check ins, will I have to have them added to the Membership table

    I presume that in your credit app a member cannot do a checking unless they already exist as a member. 

    as for this

    Can automation be setup to remove duplicate records (if any) once per day?

    Are you asking to have a duplicate check in records deleted if so yes an automation can remove records which are duplicated where the same member has more than 1 check in for a member meeting record.   The automation would retain all these a check in which had the Minimum Record ID  for the check-ins related to the member meetings table  and delete the rest.  




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



  • 5.  RE: Summary Field Issue

    Posted 08-12-2020 19:01
    You presume correctly, regarding a member not being able to check in unless they are on file.  I just wanted to make sure I didn't miss anything with regards to adding additional people that can potentially check in.

    As for the automation, that sounds good (and helpful to learn how to do).  Is there documentation on it and/or some instructions you can provide?  I have been searching and haven't found anything yet.

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



  • 6.  RE: Summary Field Issue

    Posted 08-12-2020 22:05
    There is some help here and there are probably some videos n Quick Base university

    https://help.quickbase.com/user-assistance/about_quick_base_automations.html


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



  • 7.  RE: Summary Field Issue

    Posted 08-12-2020 23:12
    Thanks for the reference material.  Meanwhile, here's what I just finished putting together but am still figuring out.

    I created another table called Original Check Ins.  This is a child table to both, the Membership and Meetup Credentials tables.  This new table only has a Check In ID field (concatenated field I mentioned above), Related Meetup, Related Member, and a lookup to the Meetup Cost.  I am using an Automation to populate this information whenever a record is added to the Check Ins, which will fail if someone checks into the same Meetup on more than one occasion.  The Membership table can now use a summary of the Meetup Cost from the Original Check Ins table for calculating the Balance.

    Assuming (and hoping) you followed what I did, do you have any thoughts?  Again, the likelihood of someone checking in to the same Meetup on more than one occasion is not high but, I can see it happening.  Members will be sent an email with the Meetup credentials to reference in the future, which will help cut down on multiple check ins.

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



  • 8.  RE: Summary Field Issue

    Posted 08-13-2020 09:00
    Ok, good for you for figuring out the Automation.

    i suggest making a formula checkbox field on the Original Checkins table called [Original Checkin exists?] with a formula of true.

    look that up down to check ins.

    then have a filter in the Automation Trigger to not try to add a record to Original Checkin, if one already exists, as opposed to designing the Automation to fail and throw errors.  If there are too many errors in too short a period it may turn itself  off, and also you get those failure emails that you don't really need to see. Better to not have the errors.


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



  • 9.  RE: Summary Field Issue

    Posted 08-13-2020 16:03
    I'm assuming you are saying make the Original Check Ins table the parent of Check Ins, so that the checkbox can be looked up to there?  The Check Ins table is also a child of the Membership and Meetup Credentials tables.  I am not getting the checkbox to be true in the Check Ins table with this setup so, I am missing something but, at the moment, not sure what.  I like the idea, though.  I just need to get it worked out.  If I figure it out before I hear back from you, I will let you know.

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



  • 10.  RE: Summary Field Issue

    Posted 08-13-2020 16:04
    , The idea is if the record that was created can be a child of the parent then it is able to tell if a parent needs to be created and doesn't just automatically create one which can cause an error on duplicate key.

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



  • 11.  RE: Summary Field Issue

    Posted 08-13-2020 17:50
    I get the gist of what you're saying.  I just don't have a full understanding of the concept yet.  I may just be confused due to some clean up I have to do for unused fields, some of which came from my efforts with relationships no longer in place.  I have to break from it for now but, I will tackle it again soon.  I'll let you know if I need anything else.  Thanks, again.

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



  • 12.  RE: Summary Field Issue

    Posted 08-14-2020 17:36
    Mark,

    I cannot figure this out.  I keep trying different relationships and cannot get the lookup to work out.  Before you gave the recommendation on the checkbox, here were the relationships I had in place:

    Original Check Ins - child of Meetup Credentials
    Original Check Ins - child of Membership
    Check Ins - child of Meetup Credentials
    Check Ins - child of Membership

    I cannot figure out what the relationship(s) should be in order to look up the checkbox from the Check Ins table and determine whether or not the Original Check Ins already exists.  Can you give me some more direction?

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



  • 13.  RE: Summary Field Issue

    Posted 08-14-2020 17:55
    Can you manage to have the check ins be a child of original Checkins?  And have the reference field be a formula?

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



  • 14.  RE: Summary Field Issue

    Posted 08-14-2020 18:28
    GOT IT!! 

    I tried that at first but couldn't get the results I was looking for.  Tried making the relationship the other way, and also tried going both ways.  Leaving the Check Ins as the child of the Original Check Ins and using a Formula - Numeric field instead of a checkbox.  For some reason, I couldn't lookup the checkbox.  My formula is a 1 instead of true.  I am using a dynamic form rule in the Check Ins form to make the Related Check Ins field value equal the Check In ID field I have.  That brings whether there is a value of 1 or not in the Original Check Ins Count.  I named it this instead of Original Check Ins Exists, due to the type of field.  

    Anyway, now I have the Automation looking for whether or not the Original Check Ins Count is less than 1 to determine whether or not to add a new record to the Original Check Ins table.  I hope the explanation for what I did makes sense.  Let me know what you think, when you get a chance.

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



  • 15.  RE: Summary Field Issue

    Posted 08-14-2020 19:23
    I have been testing the automation and, while it works for the scenario to add a new record, it does not indicate that it is triggered when there is a scenario for it to not add a new record.  Is that how it's supposed to work?  Only count that it's triggered when the criteria is met to take action?  If so, then I'd say I am good to go, and can continue my other developments.  Otherwise, I have more troubleshooting to do.

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



  • 16.  RE: Summary Field Issue

    Posted 08-14-2020 22:51
    Yes the count of the Automation only increments if the conditions are met and it fires.  So it should not increment the counter for the # of  runs  in the 2nd duplicate Checkin.​

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



  • 17.  RE: Summary Field Issue

    Posted 08-15-2020 07:54
    Outstanding. I am all set, then. Just gotta finish up. Thanks, again, Mark. I really appreciate your help.

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