Forum Discussion

samhari's avatar
samhari
Qrew Trainee
6 years ago

Autofill Multiple Child records from sibling table

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
Forms
Formulas and functions
Tables and fields

6 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    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.
    • samhari's avatar
      samhari
      Qrew Trainee
      6 years ago
      Almost, because of the way my client is using the application they would like the button to appear on the Traveler Record not the Job record. Other than it being on a different form i'd like for it to function exactly as you mention.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      Call just before I get into my explanation I want to be sure of the ask.

      How can the button be on the traveler form?. If we do that then there�s no way to identify which job we need to create the travel details for.

      I have a feeling that what they mean is that the button would be on a report of jobs which is embedded on the traveler record.
    • samhari's avatar
      samhari
      Qrew Trainee
      6 years ago
      I double checked, it is the Traveler form i want the button on Not the Job form.I can know the identity of the job through the Traveler, it has a field 'Job #' that contains the ID of the Job this traveler belongs to.

      Currently i have a button 'Add traveler Detail' on the traveler form. This is the formula for the button

      URLRoot() & "db/" & [_DBID_TRAVELER_DETAILS] & "?a=API_GenAddRecordForm&_fid_7=" & URLEncode ([Record ID#])& "&z=" & Rurl()
      Normally when the user is on the Traveler Detail form one of the fields is a select box that populates based off of the 'Job Details' table, this select box only shows Job Detail records that dont have a corresponding Traveler Detail Record. (This was setup before the client started using my company for IT, I'm not sure how that works, still reading quickbase docs). When the user makes a selection it auto fill's the rest of the form and saves it. This works well for single job details but some jobs have 50+ details and manually doing this for each one has been a pain for them.

      All i really want is a button that will automate the task of
      - click add traveler detail - this opens the traveler detail form
      - select any entry from the select box
      - save
      - repeat if the select box is empty(All details have been added) stop

      Thank you so much for trying to help
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    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))