Forum Discussion

JoshuaNurrenber's avatar
JoshuaNurrenber
Qrew Trainee
8 years ago

Auto fill a field based on 2 other fields

I have 4 tables:  "Loads Hauled", "Commercial Facilities", "On Farm Bins", and "Distance".  The "Commercial Facilities" table is a list of facilities where loads can be hauled to.  The "On Farm Bins" table is a list of where loads can come from.  The "Distance" table is a child table of both the "On Farm Bins" table and the "Commercial Facilities" table.  Each record on the "Distance" table is a distance from the on farm bin to the commercial facility to which the load is being hauled.  
The loads hauled table is a child table of the "Commercial Facilities", "On Farm Bins", and "Distance" tables.  Whenever the form on the "Loads Hauled" table is filled out, a "Commercial Facility" and an "On Farm Bin" is chosen separately.  I would like the form to automatically choose the correct "Distance" record that goes with the "On Farm Bin" and "Commercial Facility" that were chosen. 
Does anybody have any suggestions as to how I could achieve this?

Thank you for your help. 
App builders
Ask about Quickbase functionality
Forms
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Yes this can be done.

    The trick is the Key field of the Distance table needs to be changed to the same value in a formula field called [Related CF-Related Bin (formula)] set up such as



    List("-", [Related Commercial Facility], [Related On Farm Bin])

    But Quick Base won't let you make that a Key field unless you use a bit of an advanced technique using an API, so let me describe the low tech version.

    Make a text Text field called [Related CF-Related Bin (key)]

    So, one way to do this is to create that formula field and then using grid edit use copy and paste to copy  the value in the formula field to the [Related CF-Related Bin (key)] field.

    Then set the Key field of the Distance table to the [Related CF-Related Bin (key)] field.

    Now, on your Loads table, make the same formula field 
    List("-", [Related Commercial Facility], [Related On Farm Bin])

    ... and using that field make a relationship to the Distances table to look up the value.  You will find that the distance will populate automatically.

    The last step is that if you ever need to add new Cfs or Bins, then you will need to have a form rule so that says

    When [Related CF-Related Bin (formula)] is  ot equal to [Related CF-Related Bin (key)]
    Change the value of [Related CF-Related Bin (key)]  to the value in the field [Related CF-Related Bin (formula)]  and set that to fire all the time, so uncheck the checkbox at the very bottom of the form rule setup. 

    If you have a lot of distances and they get added to all the time with new CFs or new Bins, then theres in fact a way to change the Key field of the Distances table to be that formula field, so you would not need to have a form rule.  That in many ways is simpler than this setup, but does require typing in a URL with an API to change the Key field. 
     
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      Correction to the above as those are numeric field

      List("-", ToText([Related Commercial Facility]), ToText([Related On Farm Bin]))
  • JoshuaNurrenber's avatar
    JoshuaNurrenber
    Qrew Trainee
    8 years ago
    Thank you for the reply!  This is exactly what I am wanting! I believe making the key field be the formula field would be the best option as there are always new bins and Facilities being added. 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Ok, so to change the Key field to be a formula field

    1. Turn off Application tokens (at least temporarily).  That checkbox is on Application Properties.

    2. The API call is here https://help.quickbase.com/api-guide/index.html#setfieldproperties.html

    so you would basically type https://mycompany.quickbase.com/db/xxxxx?a=API_SetFieldProperties&fid=7

    where the xxxxx is your table ID and the 7 if the field to be set as the key field.  The easiest way is to just go to that table and edit the URL.