Actions Across Tables

My app structure has tables: Contacts, Tasks and Service Locations all referenced by its related Company. Said another way a Company has many tasks, service locations and contacts. A service location has a repairs table. Currently the Service Location Repair form has a place to enter an email address which is currently getting its initial value from the Service Location table via the expression [Service Location].[Manifest Email].

I’d like to get this value instead from the contacts table email column when their role selected from the enum is “Manifest” I was hoping to use an action that fires to change the value of the Service Location Repair table column “manifest email” when the role “Manifest” is assigned to that contact.

Tables Service Location Repair and Contact are not related. Is it possible to do this and what would the expression look like?

Perhaps a quicker way to do this would be to have the workflow PDF that is created from the service location repair from submission get is email address from the contact that has the role “Manifest” assigned to them but I’m not sure if that is possible either.

I’ve solved a lot of questions on my own but this one has me stumped. Thanks for your help.

So when a contact is designated as manifest, all repair manifest email addresses should be updated to reflect the new designated manifest contact?

Repairs.UpdateManifestEmail (action)

Attaches a button to the ManifestEmail column in the Repairs table that allows the user to update the column value to the current designated manifest email contact for the repair’s location.

  • Action name: Repairs.UpdateManifestEmail

  • For a record of this table: Repairs

  • Do this: Data: set the value of some columns

  • Set this column: ManifestEmail

  • To this value:

    ANY(
      SELECT(
        Contacts[Email],
        AND(
          IN([ContactType], LIST("Manifest")),
          IN(
            [Company],
            SELECT(
              Locations[Company],
              ([RowKey] = [_THISROW].[Location])
            )
          )
        )
      )
    )
    
    1. ANY(...) gets only the first of the list of items (...; see (2)).

    2. SELECT(Contacts[Email], ...) gathers a list of Email column values from rows in the Contacts table that match the given criteria (...; see (3)).

    3. AND(..., ...) requires that matching rows meet both of the given criteria (...; see (4) & (5)).

    4. IN([ContactType], LIST("Manifest")) matches only when the ContactType column value of the Contacts row matches Manifest.

    5. IN([Company], ...) matches only when the Company column value of the Contacts row matches a value in the given list (...; see (6)).

    6. SELECT(Locations[Company], ...) gathers a list of Company column values from rows in the Locations table that match the given criteria (...; see (7)).

    7. ([RowKey] = [_THISROW].[Location]) matches only when key column value ([RowKey]) of the Locations row matches the current Repairs row’s Location column value ([_THISROW].[Location]).

  • Display name: "Update manifest email from contacts"

  • Prominence: Display inline

  • Attach to column: ManifestEmail

  • Only if this condition is true: TRUE

Contacts.UpdateRepairsManifestEmail (action)

Attaches a button to the Email column of the Contacts table (only if the ContactType for the row is Manifest) that allows the user to update the manifest email addresses for all repairs at locations of this contact’s company (using Repairs.UpdateManifestEmail from above).

  • Action name: Contacts.UpdateRepairsManifestEmail

  • For a record of this table: Contacts

  • Do this: Data: execute an action on a set of rows

  • References Table: Repairs

  • Referenced Rows:

    FILTER(
      "Repairs",
      ([Location].[Company] = [_THISROW].[Company])
    )
    
    1. FILTER("Repairs", ...) gathers a list of key column values from rows of the Repairs table that match the given criteria (...; see (2)).

    2. ([Location].[Company] = [_THISROW].[Company]) matches only when the Company column value of the row referenced by the Location column ([Location].[Company]) of the Repairs row matches the Company column value of the current Contacts row ([_THISROW].[Company]).

  • Referenced Action: Repairs.UpdateManifestEmail

  • Display name: "Update manifest email for repairs"

  • Prominence: Display inline

  • Attach to column: Email

  • Only if this condition is true: IN([ContactType], LIST("Manifest"))

2 Likes

Wow! Thank you. I will see if I can get this to work. Thank you for the breakdown of how the commands are designed to work. Helps in learning.

1 Like

So this updates the manifest email on an already completed service location repair form when the action is fired on the inline email column for the contact with the role “manifest”. However when completing the form the email is blank. Wondering if it would work better to just call the email associated with the role “manifest” into the “To” field in the workflow.