Forum Discussion

RyanStanford1's avatar
RyanStanford1
Qrew Captain
6 years ago

Automation to create record if does not exist

I have a Request table, and when a new request is entered, certain values are then taken to create a new record in Table B... issue is that I'm hoping to only have the new record in Table B to be created if there isn't already a match of three values, like a foreign key.

For Example: Contract, Union, Location have to be unique... I have a formula field with these 3 values concatenated together... my question is, would this block the record being made if say Union was a RecID from the Union table?

Thanks in advance.
Quickbase Automations
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    I suggest making the Key field of table B be the concatenation of the three values separated by a hyphen.  You can use a form rule to copy the formula value into the Key field which will be  a regular text field.

    Then create a  formula checkbox field called [Contract-Union-Location exists?] and set the formula to be

    true

    Then look that up down to the Request table .

    Then fire an Automation to create the Contract-Union-Location record if the lookup is false.

  • RyanStanford1's avatar
    RyanStanford1
    Qrew Captain
    6 years ago
    The difficulty is that there's no direct relationship between these two tables... We purposefully did that to avoid the "one-to-many vs many-to-one" relationship conundrum... as multiple locations can be made from the same request.
    • LauraThacker's avatar
      LauraThacker
      Qrew Captain
      6 years ago
      It is not ideal, but if a webhook fails because it cannot create a record, due to duplication; it will simply fire out an email.  I suffer these for the convenience of having a custom key field in a parent table created from (in my case) a set of child records which auto-create the parent record the 1st (and only time).  Building a connection simply to prove the parent already exists but not for any other purpose is still worth building a relationship; and to prevent webhooks from firing multiple times when you know they will fail.  You may get one or two emails if records are added close together; but once the parent-connection proves itself you will avoid being inundated with failure emails.

      Your alternative is to use Google Scripts to create these "parents"; which will still only create records when the Key is unique and does not already exist.
  • RyanStanford1's avatar
    RyanStanford1
    Qrew Captain
    6 years ago
    I found a work around for my system... More bringing to the front of "new requst" vs "update request"... As my automation only fires on the "new request".