Formulas + Webhooks = Dynamic Record Creation
Picture a simple scenario, your company uses Quick Base to track PTO Requests and manage timecards. When an employee enters a request, you’d like to automatically generate a timecard for each day they’ll be out.
Think of another, where you are setting up a new project. Part of the set up is picking a number between 1 and 25 representing the # of Milestones. When that project is set up, you want the milestones to all auto-generate so you can assign the respective dates and descriptions in grid edit rather than adding them all one by one.
From a technical standpoint – that kind of automation is tricky because of the ‘unknown’. You don’t have any clue what days or how long someone will be out for PTO. You don’t know how many milestones you will have for your project until you save it.
What options do you have then?
✓ Users could add them one by one via the form or via grid edit (data entry by the user)
✓ You could have a custom code that looks at your data to determine how to create your time cards or milestones
✓ Use an integration tool like Workato to dynamically create time cards or milestones
An alternative to all of the above though, is to combine a little creativity with Quick Base formulas to build out a pseudo-CSV, and combine it with Webhooks to dynamically generate your set of child records.
To explain that a little more, first it helps to break down exactly what a CSV means in this context. Most would think of an excel or txt file, but really a CSV is just a list of values, separated by columns and line breaks. With that in mind, you can easily incorporate both of those into standard Quick Base formulas, utilizing common formula-text fields and if() statements, to generate the pseudo-CSV mentioned above.
Let’s say for our milestones example, my new project will need exactly 17 milestones. In my project set up, I have a numeric field for [# of Milestones]. Using If() statements in a formula-text field, you can create something like this:
if( [# of Milestones]>=1,[Related Project] & "," & "Milestone 1" & "\n", "") &` if( [# of Milestones]>=2,[Related Project] & "," & "Milestone 2" & "\n", "") & if( [# of Milestones]>=3,[Related Project] & "," & "Milestone 3" & "\n", "") & if( [# of Milestones]>=4,[Related Project] & "," & "Milestone 4" & "\n", "") & ..... if( [# of Milestones]>=25,[Related Project] & "," & "Milestone 5" & "\n", "") &
EDITORS NOTE: It is important to note that you configure this strictly using Formula-Text, not Rich Text, Number etc. Standard text inputs will allow you the entry for commas and and line breaks, without any additional input or formatting issues.
The output of which, if I want 17 milestones, will look something like this:
**For this example - 1111 is being used for [Related Project] 1111,Milestone 1 1111,Milestone 2 1111,Milestone 3 1111,Milestone 4 ... 1111,Milestone 17 //END
Remember that because a CSV is a list of values, separated by commas, make sure when you set it up that you are sure to include & “,” & between each of data point. In addition, each ‘row’ needs to end with “\n” to tell Quick Base that you want to start a new row after that.
So now a simple Quick Base formula dynamically creates a CSV of my 17 milestones. With that in hand, Quick Base natively allows you to create an API_IMPORTFROMCSV webhook that you can pass that example into. Webhooks in this instance allow you to POST your newly created CSV to the Quick Base API. It is import to use API_IMPORTFROMCSV, as this allows you to create/update any number of records, rather than doing single ADDRECORD / EDITRECORD calls. Your setup is such that your formula is added as part of the like below
<records_csv> <![CDATA[ [My CSV Generation Field goes here] ]]> </records_csv>
And voila, a simple Quick Base formula can handle creating your milestones for any number between 1 and 25. This simple but powerful technique can be used in all sorts of circumstances where you don’t always know what your child records will look like. Some other examples you might use it for:
✓ Use formula logic to set up different Task Templates for different Project Types when a new Project is created (i.e. Project Type 1 creates tasks A,B,C. Project Type 2 creates tasks D,E,F)
✓ Generate a dynamic schedule for each month starting from the current month for 12 / 18 / 24 months when you set up a new forecast
✓ Set up assignments for any number of people used in a List-User field automatically
The options though are really only bound by what you can put into a formula. The best part is that there is no custom code or additional services needed, and you can easily make updates by just updating your formula.
To see an example of this, head over the the Quandary Quick Base Knowledge Center to test it.
- Author: Chayce Duncan (firstname.lastname@example.org)
- Date Submitted: 1/2/2019