How to create a pipeline to count records in a table
I want a pipeline to do the following:
- Go to a table in an application A
- Search for records meeting a certain criteria in that table (specifically, the number of records that were completed in the previous month)
- Count the number of records found
- Go to application B
- Create a record in a table in application B
- Put the count of the records found in application A in a field on the form (fields: 1 application A name, 2 is count of records found, 3 date the pipeline ran)
I want the pipeline to run this monthly on a scheduled date. Can someone kindly tell me the steps I would need to include in the pipeline to make this work? High-level is all I'm looking for. Thank you!
I suggest a different Approach. Make the Key field of the Summary Table B be in the format Application Name|YYYYMM. It will be helpful to also create a checkbox formula field called [Application Name|YYYYMM exists?] with a formula of true.
For example Projects|202506
Make a formula field called [Application Name|YYYYMM formula] on the Table A to calculate that same field value
e.g. List("|",
[application name],
ToText(Year([Date Completed]) & PadLeft(ToText(Month[Date Completed])),2,"0"))
We will want to build a relationship to the parent table in order to do the summary counts, but there is a quirk or limitation of Quickbase that you cannot have the reference field on a relationship depend on a date field.
So after checking that the formula field seems to be calculating correctly, duplicate that field, and then change it back to a text field and change the name simply to
[Application Name|YYYYMM]
Make a relationship to the Summary Table B using this text field as the Reference field on the right. Now go to the relationship and simply make a normal relationship summary field to count the number of records. Also lookup [Application Name|YYYYMM exists?] down to Table A.
Next step is to initialize the summary table by making a summary report based on [Application Name|YYYYMM formula] to count the number of records.
Use the "More button" to copy the results of the summary report over to the Table B.
OK so that should all be working for you except how do we maintain Table B dynamically going forward?
There are Two issues. The first us that you should make a form rule so that when
[Application Name|YYYYMM] does not equal [Application Name|YYYYMM formula] then action is to change the value of [Application Name|YYYYMM] to the value in the field [Application Name|YYYYMM formula]
Now you can make an easy pipeline which triggers on new events and where [Application Name|YYYYMM exists?] is no and have it create the missing parent record in Table B.
Feel free to post back if you gets stuck at any of these steps.
