Update table using non key field

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
Is there at all possible that we could update a table using a non key field?  The contents of the table are such that I could have one key field to deal with certain number of records maintained by users online and then I have another group of records that need to be updated periodically using the unique field. I believe I cannot have two key fields.  So what is the best approach to deal with this situation? 
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb

Posted 10 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
When updated by using an API or via importing records, the only two fields which can be used to identify which record to be updated is the [Record ID#] field and the Key field. A table starts out with the Record ID field being the Key field, but the Key field can be changed to another unique field.

If users are updating records manually then they do not care or need to understand what the key field is. So set the Key field to be the one which is being updated by imports or APIs. Note that many types of fields can be set to be unique in order to prevent duplicate s during data entry by users,
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,572 Points 20k badge 2x thumb
Yes is it possible to update a record identified by a non-key field using script. Since you are hinting at the idea of having two key fields (which you can't do) I am going to assume the non-key field you want to base the update on is unique.

All you have to do is create a query for the record where the non-key field is equal to the given value and obtain the [Record ID#] for that unique record. Then edit that record specifying the now known [Record ID#] value and the values of the other fields you want to modify.

As a matter of fact if you use a Service Worker you can create your own API method and name it anything you want - perhaps something like this:

?act=API_UpdateTableUsingNonKeyField&nonkeyfield=foo&_fid_6=bar&_fid_7=baz

Within the Service Worker you detect that ?act=API_UpdateTableUsingNonKeyField is being requested and logic within the Service Worker will do the two step procedure described above and return a synthesized response indicating the composite result of the two steps.

It may sound wild but it very simple. Using a Service Worker you can create your own API methods and code them to do multistep procedures.
Photo of Keith

Keith, Champion

  • 950 Points 500 badge 2x thumb
Using our Triggers for Quick Base add on you can update multiple records without having to know the value of any key field
heres a sample app to see how it works
https://juicedtech.quickbase.com/db/bnd44x9ee

Just add a new record in the Samples table...all Item records where the Type field matches the record you entered will be updated. 

The keys in both tables were not changed, they are both Record ID#.
There is no physical relationship between the tables
https://www.juicedtech.com/juiced-cloud