Modify the add child record button to also change a field on the child record

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
I have a table of audit findings and I'm  creating a child table of workflow records. I'd like a formula url button on the audit finding for each action of the workflow process, ie, Send to Response Owner, Send to SME, Send to Auditor...
Clicking each button would add a record to the Workflow child table and then update the 'Action' field (fid 11) on the workflow child form with the name of the button clicked ie "Send to Response Owner"

how do i modify this formula url so when the button is clicked it adds a record but also updates the 'Action' field

URLRoot() & "db/" & [_DBID_WORKFLOW] & "?a=API_GenAddRecordForm&_fid_8=" & URLEncode ([Record ID#])& "&z=" & Rurl()

Thanks!
Photo of Trish Wehrle

Trish Wehrle

  • 160 Points 100 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of Sam C

Sam C

  • 322 Points 250 badge 2x thumb
Field values can be added to these statements using the _fid_X=YOUR_VALUE syntax. In fact, you are already doing this when you enter the Record ID for the parent record here:
&_fid_8=" & URLEncode ([Record ID#])

So in your case to add another field you would just another statement like this for your other field:

&_fid_11=" & URLEncode ([Action])

Which would end up looking like this:

URLRoot() &
"db/" & [_DBID_WORKFLOW] &
"?a=API_GenAddRecordForm" &
"&_fid_8=" & URLEncode ([Record ID#]) &
"&_fid_11=" & URLEncode ([Action]) &
"&z=" & Rurl()

As a sidenote, you may have noticed I split this formula onto multiple lines. This is perfectly fine, and I would suggest doing so when you have longer formulas like this so you can see exactly what's going on.


(Edited)
Photo of Trish Wehrle

Trish Wehrle

  • 160 Points 100 badge 2x thumb
Thank you so much for the response. So if i wanted the 'Action' field to be populated with the words "Send to Response Owner", how would i do that?
Photo of Sam C

Sam C

  • 322 Points 250 badge 2x thumb
This is a bit more of a long/technical answer, but hopefully it helps teach a man to fish more than just giving you the fish :). If you follow the first example you should be able to do what you are asking with no trouble.

What you are actually doing here is building a URL with parameters, pointed to your QuickBase database. When you click that button, you are in effect sending a request to QuickBase, which it then interprets. Each piece of the URL is a variable that gives information to the server to process your request. You can read it like:

URLRoot() & // At my QuickBase domain
"db/" & [_DBID_WORKFLOW] & // At the Workflow Table
"?a=API_GenAddRecordForm" & // Do the action "API_GenAddRecordForm" (open a new record page)
"&_fid_8=" & // Fill in the field with field ID 8
URLEncode([Record ID#]) & // With the Record ID# from this record. URLEncode the value so it doesn't get jumbled up
"&_fid_11=" & // Fill in the field with field ID 11
URLEncode([Action) & // With the Action value from this record. URLEncode the value so it doesn't get jumbled up.

"&z=" & Rurl() // And when I save this form, redirect me back the page I'm on right now.

Any field you want to pre-populate in the form is added with whatever value you put after it. You can do this for any number of fields, and with static or formulated values. So for example let's say I have a table Prides, and each Pride has many Lions. The Lion table has an field Type, with a field ID of 12. I want a button that lets me add a new Lion to a Pride, and every time fills in the Type of Lion with "Cub".

Based on the example above, I know I need to update my Add Lion formula field with a new parameter so it fills in when I open the form. I can add parameters to my search using the "_fid_X=YOUR_VALUE" syntax I mentioned in my first post.

My field ID is 12, so first I replace X with 12:
"_fid_12=" & YOUR_VALUE
Now I need to give it a value. I want the value to always be "Cub". First I'll URLEncode it to make sure its safe to add to my formula:
URLEncode("Cub")
Then I'll add that to my formula for a complete parameter:
"_fid_12=" & URLEncode("Cub")
Now that I have my parameter figured out, I need to add it to the rest of my formula. I know each parameter needs to be separated by an ampersand(&) so I'll add one in front, and add the whole thing to my formula:

URLRoot() & // At my QuickBase domain
"db/" & [_DBID_LIONS] & // At the Lions Table
"?a=API_GenAddRecordForm" & // Do the action "API_GenAddRecordForm" (open a new record page)
"&_fid_12=" & URLEncode("Cub")" & // We just added this value. "&z=" & Rurl()
My button will now open a new Lion form page, and populate my Type field with "Cub". 

If you've gotten this far, you should now be able to complete your button in the exact same way. It doesn't matter whether the value you add is a static value or not. You can also add as many parameters as you want. In fact, we can even take this a few steps further:

First, let's use some variables to make it a bit more readable. Using variables can be helpful to keep things more organized as your formulas get longer, and helps you think about your Formula in more manageable pieces.
var text URL = URLRoot() & "db/" & [_DBID_LIONS];
var text action = "?a=API_GenAddRecordForm";
var text redirect = "&z=" & Rurl();
var text lionType = "&_fid_12=URLEncode("Cub")";

$URL &
$action &
$lionType &
$redirect
This makes our formula a lot easier to read. Imagine we wanted to add a whole bunch of other pre-filled values to our form, or our values were more complicated:

var text URL = URLRoot() & "db/" & [_DBID_LIONS];
var text action = "?a=API_GenAddRecordForm";
var text redirect = "&z=" & Rurl();

var text lionTypeField = "&_fid_12="
var text lionTypeValue = If([Pride Type] = "Evil",
  URLEncode("A monkey's uncle"),
  URLEncode("Cub")
);
var text lionType = $lionTypeField & $lionTypeValue;

var text lionColor = "&_fid_13=" & URLEncode([Pride Color]);

$URL &
$action &
$lionType &
$lionColor &
$redirect
You can see that even though some of our values are starting to get a bit complex, and our formula is getting long, our final URL formula is very easy to read, and each value can be understood quickly.

This got a lot longer than I planned so I'm going to end it here...but I hope that helps!
(Edited)
Photo of Trish Wehrle

Trish Wehrle

  • 160 Points 100 badge 2x thumb
This is so helpful, thank you so much!
We accomplish these workflow kind of functions with checkboxes and automations

For example, here's a data entry form



here's the automation trigger for one of those 'Assign to' checkboxes (the 'close ticket' and 'programming complete' checks do different things)



Admittedly the form rules for those boxes are a bit of a pain

(Edited)