Skip to main content

Update field in a table based on changes to a field in another table

  • May 24, 2021
  • 6 replies
  • 48 views
J
Forum|alt.badge.img+17

Is this possible? I have a table called “SITES” and a table called “SERVICES” that are connected by a linked record field. A service can comprise one or more sites. In both tables there is a “status” single select field indicating whether the site/service is live or not. When a service is no longer live, then all sites linked to it also need to be marked as “ceased”. Likewise if a site ceases, and that site is the only site in a service, then by definition the service ceases as well.

I think it needs a three-step automation where the first step is finding the record matching certain conditions in sites, which then uses “find records” in step 2, to change the corresponding records in the linked table (SERVICES) in step 3.

Help very much appreciated.

6 replies

Kamille_Parks11
Forum|alt.badge.img+27

You could add do this with two Automations and Rollup fields.

[Services] table:

  • Add a Rollup-type field that pulls in all linked Sites's statuses using ARRAYUNIQUE()

Automation 1

  • Trigger: When [Services] table record matches condition {Sites Status Rollup} = Ceased AND {Status} != Ceased
  1. Update Record:
    Table: [Services]
    Record ID: trigger record's ID
    {Status} = Ceased

[Sites] table:

  • Add a Rollup-type field that pulls in all linked Service's statuses using ARRAYUNIQUE()

Automation 2

  • Trigger: When [Sites] table record matches condition {Service Status Rollup} = Ceased AND {Status} != Ceased
  1. Update Record:
    Table: [Sites]
    Record ID: trigger record's ID
    {Status} = Ceased
J
J
Forum|alt.badge.img+17
  • JonathanB
  • Author
  • Inspiring
  • May 25, 2021

You could add do this with two Automations and Rollup fields.

[Services] table:

  • Add a Rollup-type field that pulls in all linked Sites's statuses using ARRAYUNIQUE()

Automation 1

  • Trigger: When [Services] table record matches condition {Sites Status Rollup} = Ceased AND {Status} != Ceased
  1. Update Record:
    Table: [Services]
    Record ID: trigger record's ID
    {Status} = Ceased

[Sites] table:

  • Add a Rollup-type field that pulls in all linked Service's statuses using ARRAYUNIQUE()

Automation 2

  • Trigger: When [Sites] table record matches condition {Service Status Rollup} = Ceased AND {Status} != Ceased
  1. Update Record:
    Table: [Sites]
    Record ID: trigger record's ID
    {Status} = Ceased

Thank you very much for this. Automation 1 works perfectly, but for automation 2, the test fails on the last step. It needs to work so that the status of SERVICE changes to “Ceased” only when all sites linked to that service are marked as “Ceased” in the SITES table.

Kamille_Parks11
Forum|alt.badge.img+27

Thank you very much for this. Automation 1 works perfectly, but for automation 2, the test fails on the last step. It needs to work so that the status of SERVICE changes to “Ceased” only when all sites linked to that service are marked as “Ceased” in the SITES table.


Automation 1 is the one that affects [Service] records but you say 1 already works.

When you say Automation 2 fails, in what way does it fail?

J
Forum|alt.badge.img+17
  • JonathanB
  • Author
  • Inspiring
  • May 27, 2021

Automation 1 is the one that affects [Service] records but you say 1 already works.

When you say Automation 2 fails, in what way does it fail?


The second step of the automation is showing as “Test failed” - Record does not exist. See attached screenshot.

Kamille_Parks11
Forum|alt.badge.img+27

The second step of the automation is showing as “Test failed” - Record does not exist. See attached screenshot.


My bad, change the table to [Sites]. (the record doesn’t exist because the record ID is coming from the Sites table but is trying to update some record in Service.)

J
J
Forum|alt.badge.img+17
  • JonathanB
  • Author
  • Inspiring
  • May 28, 2021

My bad, change the table to [Sites]. (the record doesn’t exist because the record ID is coming from the Sites table but is trying to update some record in Service.)


OK, things seem to be joining up now, but not working exactly as I need it to.

What I need to happen is when at least one site is live, the service is also live; and when all sites are ceased, the service is also ceased. But it still needs to allow for when one site is live, and another ceased, the service is still live. i.e. a service is only changed from “live” to “ceased” when all sites linked to it are ceased; but changing one site to “ceased” and leaving another as “live” does not change the status of the service if this is already marked as “live”.

Thanks very much for your help so far! :grinning_face_with_big_eyes:

Terms & ConditionsAccessibility statement