Skip to main content
Question

Pipeline Help - Aggragating Date Fields by Month

  • July 26, 2024
  • 0 replies
  • 221 views
MeaganMcOlin
Forum|alt.badge.img+7

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!

    This topic has been closed for replies.

    MarkShnierYou
    Forum|alt.badge.img+22

    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!

     

    Mark Shnier. Your Quickbase Coach
      Forum|alt.badge.img+15
      • DonLarson
      • Registered
      • July 29, 2024

      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.

       

       

        Terms & ConditionsAccessibility statement