Forum Discussion

MeaganMcOlin's avatar
MeaganMcOlin
Qrew Cadet
8 months ago

Pipeline Help - Aggragating Date Fields by Month

I need assistance with setting up a pipeline in my Quickbase app to aggregate data by month. This will be the first time I've used pipelines.

The tables involved are called:

  1. APP Credentialing Table: This table contains the raw data I need to aggregate. Namely, 3 date fields. 
  2. APP Date Summary Table: This table is where I want to update the aggregated counts. It has 2 fields. Month/Year (text) and Count (Numeric). 

Specifically, I need to:

  1. Retrieve and filter records from the APP Credentialing Table.
  2. Group these records by Month/Year and count them.
  3. Update the APP Date Summary Table with the aggregated monthly counts.

Could you please provide guidance on how to configure the pipeline to perform these tasks? If this even can work. 

Thank you!

Pipelines
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    8 months ago

    Instead 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!

     

    • MeaganMcOlin's avatar
      MeaganMcOlin
      Qrew Cadet
      8 months ago

      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. 

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        8 months ago

        OK, 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!  

  • DonLarson's avatar
    DonLarson
    Qrew Elite
    8 months ago

    Meagan,

     

    It is a common problem to have to report out on business objects that are not really related to each other except by time period.   Quickbase is not very good at that problem.    Here is a similar architecture to what you are proposing but based around the concept of Month End Close.   I have clients where there are a many table related to the Month End. 

     

    The difference with your solution is that there are two dates in the MEC Table.     As Mark mentioned your Months need to be built out well into the future and have no overlap of dates so your Parent record is always unique.

    I do not like to change Key Fields so I will use a Pipeline as you described.    There is a Search step that will have a Advanced Query that looks like this

    Assume that [First Day of Month]  is Field ID 5

    [Last Day of Month]  is Field ID 6

    {5.OBF.'Date'}AND{6.OAF.'Date'}

    The Search step of the Pipeline is looking for the record where your Date field sits inside that month.   Then the next step in the Pipeline will update the record to relate them.

    Reach out if this does not make sense.