Qrew LegendInstead of getting into the effort and complexity of pipelines have you considered a simple relationship where One APP Data Summary has Many APP Credentialings?
Why don't you set the key field of your APP Date Summary Table to be the Month/Year (text) field. You can use excel to populate that table from now until say 10 years away. It's only like 120 records so very small record count.
On your APP Credentialings table make a formula to calculate the value of the Key field on the APP date Summary table.
Then just make a summary field on the relationship and you will always have a live total with no fancy Pipelines.
Feel free to post back with any questions or obstacles!
Hi Mark,
Thank you so much, I am going to try this approach. Sorry for the slow response, work piles up.
Follow-up question, how do I write a formula to calculate the value of the Key field on the APP date Summary table? BTW there are several date fields in the "APP Credentialing" Table.
Screenshots of both tables are attached.
Qrew LegendOK, thx for the screen shots. That was helpful.
You can set the Key field of the APP Date Summary Table to be that date field which is the first of the month.
However due to a technical limitation in QuickBase, unfortunately we can't simply make a formula field of one of your date fields to be the first day of the month. QuickBase will not accept a formula field which depends on a date as the reference field on the right hand side of a relationship when you intend to use summary fields.
So, I don't know how these individual records get into it but let's say they are entered one by one on forms. What you would do is you would create a formula field for the first day of the month like this
FirstDayOfMonth([CUH Privilege Expiration Date]) and then have a form rule to populate a "scalar" field (that means a data entry field) called perhaps ([CUH Privilege Expiration Date scalar]) with the value of the formula field. To initialize all your existing records, you can you copy paste in grid edit, or else you can make that scalar field be a formula field and then set it back to be a date field.
Then make the relationship based on that scalar field and make your summary field. Once you get that working for one date field you just repeat the whole process for your next field. A new formula field, a new scalar field a new form rule, a new relationship, and a new Summery field.
LMK how it goes!
