Automation going both directions between tables, if field is empty

Hi! I see that this is possible, but I don’t really understand how to do it.

We have two tables:

  1. State level information.
  2. Local information.

Hopefully most of the overlapping information will be entered at the state level, and when someone enters information on the local table, an automation will push it there based on a single select field- if the value is one of the statewide options, many fields will populate.

However, sometimes folks working on the local table will collect statewide information. When they select one of those statewide options, I need to set up the automation to work the other way: push info to the state level table if the record is blank.

The additional twist here is that there may be 5 local records for one state record (ex: there is only one Delaware record, but it is linked to 5 records in the local table), so I also need to make sure only the proper information populates in those empty fields.

Thank you!

Okay, I think I’ve actually got the conditional part down here.

However, the part I can’t get right is the selecting the right value to populate.

If Record A in the Local Table is updated to include “statewide primary,” then Record 1 (the state of Record A) in State Table should have several fields updated.

However, Records B, C and D may also be from Record 1, but won’t have the condition “statewide primary.” They will, however, have values in these same fields, which will also be in lookup fields in the State Table. I only want the values from Record A to update up in Record 1 (which should only ever have one value in its native fields.)

I can’t get this to work. Screenshot attached:
State field=primary field for State table.
:inbox_tray::date: Date of election- for reminders= a lookup field from the Local table. You can see in Delaware there are multiple values. Only one of those is from Record A, which I want to populate a field in Record 1 (Delaware).

Hi,
if you need a “conditional lookup”, you should use rollup field instead of lookup.

image

Thanks- I think I’m getting closer and your screenshot is very helpful!

Now I’m on the state table and I’ve created a rollup field that is pulling data from the local table.

It pulls in the field I need- date of election for reminders- with the condition that I’m looking for- statewide primary elections. The formula I chose was CONCATENATE(values).

However, when I go back to the State table, my date format is not right:

When I try to format the rollup field, I get a message that “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date,” but the field that I’m rolling up is in fact a date field. How do I get the pretty result that you have, which I will need for the automation?

ETA: I know that I can create another field that is a DATETIME_FORMAT field to do this, but I have to create several fields like this, and that feels awkward, especially since it looks like you’ve got it without the formula field.

Okay! I’ve actually got this- it worked with MIN(values) (or MAX), but not concatenate.

I’ve also got one of the automations working. But I can’t get multiple automations working.

The automation using the rollup above works:

However, when I set up the next one, statewide general, nothing appears if the record in the state table also has other elections. Here’s the setup:
Rollup fields:


Automation:

(Edited for right screenshot)

I think these are set up the same way, but only the first works.

Thanks!

Hmm, i can’t realize what kind of field is “Type of elections”?
When you set condition for multiselect field, and need to check if some value exist, no matter what about others, you should select “has any of” (or “has none of”)

image

that will work for Plane, for Plane+Ship+Walking etc…
“Exactly” works with “Plane” alone, but not “Plane + smth else”
it’s like difference between “is” and “contains”

In your screenshots, for “Type of elections” i see condition “is exactly”, which present in multiselect, but absent in single select, and “is” - which is vice versa.

Sorry for the delay, and thanks for getting back to me, @Alexey_Gusev ! Our office was closed.

“Type of elections” is a single select field. Here’s a screenshot with statewide primary and statewide general examples. (Primary is working in the automation.)

The issue is that that all of those records might end up being linked to one record on the table where I’m trying to create the automation. So I created a rollup field to allow for conditional automations- if the record is statewide, run the automation. Here’s an example where I’ve created two rollups- one for each value that I need to separate out:

I’m not quite sure what I’m doing wrong, but I’m doing something wrong! This is how I set up the trigger for the automation:

Then, in the California record, I’ve laid out the fields next to each other. If the automation were working, the rollup field would populate the field next to it, which is native to this table.

Here’s the automation:

Thank you again!