Forum Discussion

Qrew Cadet

4 years ago

Adding Multiple Child Records with CB in Parent and Lookups in Child

Hi all: (edit for more clarity) I would like automatically add between 1 and 9 new records to a child table when a modification has been made to the parent record using 1-9 check box fields. I tri...

MarkShnier__You

Qrew Champion

I am unclear what your question is. Are you saying you would like to have a formula URL button that would create between one and nine child records.

you say this:
I switched to a rich text formula field and developed a really pretty button that also creates child records.

What is not working?

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

Qrew Cadet

4 years ago

Hi Mark:
I edited my original post and yes a formula url could do it too, just unfamiliar with nesting all action steps to do it particularly with the child field being "lookup" type fields

------------------------------
Melissa Freel
------------------------------

MarkShnier__You
Qrew Champion
4 years ago
Ok, here goes.
We will need to combine a few Techniques and then also hope that the resulting URL is not too long. There is a length limit or # of redirect limits that's a bit fuzzy.

Firstly you need 10 steps. Nine to do the up to 9 children and then a tenth to redisplay the record.

$URLONE
& "&rdr=" & URLEncode($URLTwo)
& URLEncode("&rdr=" & URLEncode($URLThree))
& URLEncode(URLEncode("&rdr=" & URLEncode($URLFour)))
& URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFive))))
& URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSix)))))
& URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSeven))))))
& URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLEight)))))))
& URLENcode(URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLNine))))))))
& URLENcode(URLENcode(URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLTen)))))))))

OK so now all we have to do is to define what the formula variables URLONE through URLTEN do.

I will do the easy one first.

var text URLTEN = URLRoot() & "db/" & dbid() & "?a=dr&rid=[ & [Record ID#]; // redisplay the Parent record

There are nine more but they are all sort of the same. Your complicating issue is that we do need to do those first 9 URL calls even if they are not all needed. So what we can do for the un-needed ones is to edit a dummy field to be true. So make a field in the parent table called [Dummy Checkbox used in URL] and let's say it is field ID 100.

var bool Dummy = URLRoot & "db/" & dbid() & "?act=API_EditRecord&rid=" & [Record ID#]
& "&apptoken=xxxxxxxxxxxx" // needed if you have the need to app tokens enabled
& "&_fid_100=1"; // make the Dummy checkbox true

var text URLONE = IF(not [Checkbox 1], $Dummy, URLRoot() & "db/" & [_DBID_MyChildTable_Alias] & "?act=API_AddRecord"
& "&_fid_10=" & [Record ID#]
& "&_fid_11=1); // 5 is the Record ID# of the required Master task corresponding to [Checkbox 1]

In the above I assume that your first checkbox field is called [Checkbox 1]. If it's unchecked (false), then we just do the Dummy checkbox, else we do the real URL.

So in the above I have assumed that fid 10 in the child record is the field for [Related Parent] ... We needed to populate that or else the created child will be an orphan, just floating in space.

Next I'm assuming the fid 11 is the field for [Related Master Task] . ie you are saying that these children being created are really join table record between the parent (say Project) and a Master Table list of possible Tasks. In the example above I'm assuming that [Checkbox 1] means you need to have Record ID# = 5 in the Master Tasks table.

So in the table of Master Tasks, you need to expose the Record ID# on a report so you know which Master Tasks Record ID needs to be populated to match up with each of your nine checkboxes on the parent record.

Give it a try and post back if you get stuck with your formula and the syntax error message if there are any. Obviously I'm just typing while watching the super bowl and I don't have a formula editor in this box.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
- MarkShnier__You
  Qrew Champion
  4 years ago
  Correction to the above
  
  var text Dummy = URLRoot & "db/" & dbid() & "?act=API_EditRecord&rid=" & [Record ID#]
  & "&apptoken=xxxxxxxxxxxx" // needed if you have the need to app tokens enabled
  & "&_fid_100=1"; // make the Dummy checkbox true
  
  ------------------------------
  Mark Shnier (YQC)
  Quick Base Solution Provider
  Your Quick Base Coach
  http://QuickBaseCoach.com
  mark.shnier@gmail.com
  ------------------------------
  - MelissaFreel1
    Qrew Cadet
    4 years ago
    Mark:
    Thanks for the help. I got it to "slightly" work. I think the place where I am struggling is with your fid10 and fid 11. I think I am misunderstanding what you mean by "5". Here is what I have so far:
    
    //Add Utility Allowances
    
    var text LABEL= "Add Allowance";
    
    var text Dummy = URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&rid=" & [HH Record ID#]
    & "&_fid_3037=1"; // make the Dummy checkbox true
    
    var text URLONE = If([Heating Natural Gas]=false, $Dummy, URLRoot() & "db/" & [_DBID_TEST] & "?act=API_AddRecord"
    & "&_fid_3=" & [HH Record ID#]// Record Id of Parent
    & "&_fid_33=" & 5 // Related Record in Child
    & "&_fid_8=" & [Structure]// change the structure field in child to the selected information in parent [Structure]
    & "&_fid_11=" & "Heating Natural Gas"//change the utility type field in child to utility type identified with check box 1
    & "&rdr=");
    //Put 9 more in
    
    var text URLTEN = URLRoot() & "db/" & Dbid() & "?a=dr&rid=[ & [Record ID#]"; // redisplay the Parent record
    
    var text URLALL=$URLONE & URLEncode($URLTEN);
    
    "<a class='Vibrant Alert' href='" & $URLALL & "'>" & $LABEL & "</a>"
    
    ------------------------------
    Melissa Freel
    ------------------------------