Expand all | Collapse all

Update table using non key field

  • 1.  Update table using non key field

    Posted 01-14-2018 09:38
    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? 

  • 2.  RE: Update table using non key field

    Posted 01-14-2018 13:42
    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,

  • 3.  RE: Update table using non key field

    Posted 01-14-2018 17:54
    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:


    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.

  • 4.  RE: Update table using non key field

    Posted 01-14-2018 18:04
    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

    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