Auto fill a field based on 2 other fields

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
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. 
Photo of Joshua Nurrenbern

Joshua Nurrenbern

  • 150 Points 100 badge 2x thumb

Posted 10 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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. 
 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Correction to the above as those are numeric field

List("-", ToText([Related Commercial Facility]), ToText([Related On Farm Bin]))
Photo of Joshua Nurrenbern

Joshua Nurrenbern

  • 150 Points 100 badge 2x thumb
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. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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.
(Edited)
Photo of Joshua Nurrenbern

Joshua Nurrenbern

  • 150 Points 100 badge 2x thumb
Thank you for your help!  I will work on that.