Skip to main content
Question

Autofill Multiple Child records from sibling table

  • April 6, 2019
  • 0 replies
  • 59 views
Hello i am brand new to quickbase so please forgive me if some of my terms are incorrect or misleading, I'm trying to be as clear as possible. So say i have 4 tables Jobs, Job Details, Travelers, and Traveler Details. They are related very simply. A single Job Record can have Multiple Job Details Records, as well as Multiple Traveler Records. A single Traveler Record can have multiple Traveler Details Records.

The 'Traveler Details' and 'Job Details' tables are very dependent. A 'Traveler Detail' always relates to a specific 'Job Detail'.

What i would like to do is add a URL Formula button on my 'Travelers' form that finds all the related 'Job Details' Records for the 'Job' this Traveler belongs to and creates a new 'Traveler Detail' record for each one with some default values. I've found documentation for API_AddRecord witch i think would allow me to do what i want. But i cant seem to find a way to iterate through all the 'Job Details' associated to the Parent Table (Jobs). Is something like a for loop or forEach possible inside a form URL formula?

Like i said i'm new to quickbase, so a quick explanation and links to the appropriate docs would be best. I do have javascript experience i would not shy away from an answer that required a bit of custom scripting
    This topic has been closed for replies.

    Can we clarify the heart of your question


    You say this.


    What i would like to do is add a URL Formula button on my 'Travelers' form that finds all the related 'Job Details' Records for the 'Job' this Traveler belongs to and creates a new 'Traveler Detail' record for each one with some default values


    It sounds instead that you want a button on the Job Record to identify its Job Detail child records and copy them into equivalent Travellor details records. Is that your real question?


    If so yes that can easyily be done with native Quick Base, no code.
    This can be done with a URL formula button. But it also be done with an Automation. The initial setup is the same though.

    Set up a new table called Focus Job. Add a single numeric field called Record ID of Focus Job.

    Add a record to this table and then set permissions so that no one, not even admin can add or delete this record. It will be Record ID# of 1.

    Create a relationship to the Job table by making a field called Link to Focus Job as a formula numeric field of 1. Look up the field for Record ID of Focus job.

    Lookup that field down to Job Details.

    Now, next step is to create a saved table to table import from Job details into traveler details subject to the filter that the arelated Job is equal to the value of Record ID of Focus Job. If there are default values to be populated make fields on the Job Details record To Copy across in the saved table to table import.

    Ok all the ingredients are in place.

    You should be able to set up an Automation that triggers off a checkbox being checked on the Traveler Record to update the Focus Job Record with the job number and run the saved table to table import.

    This can also be executed by a formula URL button. If you want that code, get the rest working and I can help you with the URL formula to set the focus, run the import and refresh the Traveller recird.
    Here is some code for a URL formula button

    var text SetFocus =
    URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_EditRecord"
    & "&apptoken=xxxxxxx"
    & "&rid=1"
    & "&_fid_7=" & [Record ID# of Job];

    var text RunImport = 
    URLRoot() & "db/" & [_DBID_table name from its advanced properties]
    & "?act=API_RunImport&ID=10"
    & "&apptoken=xxxxxxx";

    var text DisplayRecord=
    URLRoot() & "db/" & Dbid()  &"?a=dr&rid=" & [Record ID#];


    $SetFocus
    & "&rdr=" & URLEncode($RunImport)
    & URLEncode("&rdr=" & URLEncode($DisplayRecord))

    Terms & ConditionsAccessibility statement