Forum Discussion

AndrewFry's avatar
AndrewFry
Qrew Assistant Captain
6 years ago

Automation when field is blank

I have two tables:
  • Subdivision
  • Locations
Locations is a child of Subdivision (many locations to one Subdivision)

When I enter a new location I can select a previously input subdivision to associate/relate it.

Not every location has a subdivision.

If there is no subdivision selected at the time the new Location is created, in the Locations table it shows the new location with a blank Subdivision. Perfect!

However, if I go to the Subdivisions table, there is not "blank" subdivision with the locations listed which have no subdivision.

How can I remedy this?

I was thinking of creating an automation where when a new location record is added and no Subdivision is selected, then the Automation creates a subdivision of <blank> (literally: <blank>).

This would at least generate a Subdivision record of <blank> and have the related locations listed under it.

The issue is that I can't seem to figure out how to make the automation work correctly. There doesn't appear to be an option for: "when field is blank" or "when field is null".

Any thoughts on how I would correct this automation? Do I need to create the space holder of <blank> or is there a better way?

Thanks for your help and assistance!

-Andrew
Quickbase Automations
Relationships
Tables and fields
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    6 years ago
    You'll need to create a Subdivision record of "No Subdivision" or "Blank Subdivision", whatever works better for you. In your automation, you'll need to look for a record that has "Related Subdivision" as 0 and then change it to the Record ID# of the "Blank Subdivision" record. You can probably also do this with a Form Rule
  • AndrewFry's avatar
    AndrewFry
    Qrew Assistant Captain
    6 years ago
    Blake, I think what you recommended is on the right path, but I can't seem to implement it correctly.

    Here is what I have done:

    • App Home > Settings > Automation
    • New Automation > When Data Changes
    • When User [Adds A Record] > In the [Locations] table
    • Additional Criteria > Any Field Changes > And Any of the conditions are true
    • Subdivision Name - Is Equal To - The Value - 0
    • Add Action > Modify Records > Modify Records from a Different Table (Advanced)
    • Table = [Locations]
    • Filter Records (left alone)
    • Field: Related Subdivision
    • Source: Copy Value From Another Record
    • Value: Trigger [Locations] | Related Subdivisions

    I understand the concept that you have proposed:

    Basically, create a Subdivision of "No Subdivision" or similar.
    In my case I created a Subdivision of: <blank>
    The Subdivision ID# is 12.
    Then create an automation so that when a new location record is saved, with a blank subdivision, it changes the related subdivision to Subdivision # 12.

    I just can't seem to get it to work correctly because I can't seem to figure out how to tell it which Subdivision record to copy.

    Any pointers you can give?

    -Andrew
    • ArchiveUser's avatar
      ArchiveUser
      Qrew Captain
      6 years ago
      It looks like you're pretty close. I'd suggest the following changes:

      1. Where you have 'Subdivision Name = 0', you should have that as 'Related Subdivision=0'. "Subdivision Name" is going to be a text field and "Related Subdivision" is the numeric field that references the matching Subdivision record in the other table.
      2. Filter Record should be set to show Record ID# = Trigger:Record ID# as follows:
      3. For the 'Source: Copy Value' step, you should set that as 'Type a Value' and then manually enter 12 in the 'Value' field.
      I have to admit, though, that this is kind of like using a sledgehammer when a hammer will do. Probably the easiest thing to do would be to go into the properties of the 'Related Subdivision' field and change the 'Default' to 12. This would automatically add the Blank record as the connected Subdivision when a user selected the green ADD button, but wouldn't do it if they added a Location from an existing Subdivision record.