Solved: Dynamic email automation with dynamic conditional ...

rcfberg · 11-25-2024 06:56 AM

Dear community,

I am developing a project tracking app and am facing challenges related to setting up dynamic email automation to users who face specific constraints. Assume I have a main project table, which is connected to a bookmarks-table. Once a logged in user selects a project as favourited, the select project is added to the bookmarks-table (this will not impact the experience of other logged in users).

Second, I have applied conditional formatting to the bookmarks-table, which marks those projects that have been updated in the last 48 hours. So far so good.

Then the issue: I want to send out a daily scheduled emails to users whose bookmarked projects have been updated (based on a last update-column in the dataset) within the last 48 hours. Assume I have a scheduled event which runs the process of sending an email. In the email's to-section, I have the following specification:

SELECT(
  users_sh[Email],
  AND(
    [ReceiveNotifications] = TRUE,
    [AutomationEnabled] = TRUE
  )
)

where users_sh is the user table (which is connected to the bookmarks table via Ref, where the bookmarks table is connected to the master project table). For some reason, this does not dynamically send an email to the specified user - even if a condition has been added:

UNIQUE(
  SELECT(
    users_sh[Email],
    COUNT(
      FILTER(
        "bookmarks_v3",
        AND(
          [Useremail] = [Email],
          (HOUR(NOW() - DATETIME([ProjectRef].[Project last updated])) / 24) <= 2
        )
      )
    ) > 0
  )
)

Secondly, my automated email does not work properly - it is if the Useremail section is broken:

Hi

You have <<COUNT(
  SELECT(
    bookmarks_v3[BookmarkID],
    [Useremail] = users_sh[Email]
  )
)>> projects as bookmarked. In the last 2 days Org X has updated the following project's <<COUNT(
  SELECT(
    bookmarks_v3[BookmarkID],
    AND(
      [Useremail] = users_sh[Email],
      (TOTALHOURS(TODAY() - DATE([ProjectRef].[Projects last updated])) / 24) <= 2
    )
  )
)>> informations. These projects are:

<<Start: SELECT(
  bookmarks_v3[BookmarkID],
  AND(
    [Useremail] = users_sh[Email],
    (TOTALHOURS(TODAY() - DATE([ProjectRef].[Projects last updated])) / 24) <= 2
  )
)>>
- Name of project: <<[ProjectRef].[Name of project]>>
- Project last updated: <<[ProjectRef].[Projects last updated]>>
<<End>>

My problems are the follow: first, automation does not send out any email. However, if I specify a specific email address, which is in the bookmarks table, then it works. However, for some reason, Appsheet's automation cannot find email inserted in the user table. If I add 'USEREMAIL()' in the to-section, then I receive an email, but for some reason, Appsheet sends this email to other accounts as well. Second, content seems not to be updating dynamically. Only if I specify a specific email (again linked to the bookmarks section), only then does it seem to work.

What am I doing wrong here? Again, I would like the automation system to dynamically send emails to those users whose bookmarked projects have been updated in the last 48 hours.

Many thanks

rcfberg

Hey @WillowMobileSys ,

Thanks for a prompt response! Apologies for not articulating myself more clearly. While your suggested did not solve the problem, it helped me on the way.

My solution was this: first, in the when event occurs, toggle "For Each Row In Table" on for the table where email addresses are being stored - in this case: bookmarks_v3. This will activate the row by row loop. Then apply the following filter:

AND(
  NOT(
    IN(
      [Useremail],
      SELECT(
        bookmarks_v3[Useremail],
        [_ROWNUMBER] < [_THISROW].[_ROWNUMBER]
      )
    )
  ),
  COUNT(
    SELECT(
      bookmarks_v3[BookmarkID],
      AND(
        [Useremail] = [_THISROW].[Useremail],
        TOTALHOURS(NOW() - DATETIME([ProjectRef].[Projects last updated])) <= 24
      )
    )
  ) > 0
)

This will apply the desired filters of identifying users via emails (while filtering email duplicates) based on a specific conditional formatting. After that, apply [Useremail] to the to-field.

Now the automatic email functions in the desired way.

Hopefully this solution helps out some other developers struggling with their email automations!

View solution in original post

WillowMobileSys

I am having a little trouble following your design....but I'll generally describe what is needed from the automation perspective.

You will want to create a Scheduled Automation that runs against your Users table. The Event criteria for the Bot will identify a LIST of users whose Bookmarked projects have changed in the last 48 hours. That expression would modify your second expression as follow:

SELECT(users_sh[user id], 
       IN([Email], 
          UNIQUE(SELECT(bookmarks_v3[Useremail],
                        TOTALHOURS(NOW() - [ProjectRef].[Project last updated]) <= 48
                 )
          )
       )
)

NOTE: The TOTALHOURS part is just a different way to select the rows based on hours.

The Bot will iterate over the list of chosen user rows one by one to send an email each user separately.

In the Email step of the Bot, I believe you can use the email template very close to what you have submitted EXCEPT, you will need to change all occurrences of this:

~~[Useremail] = users_sh[Email]~~

to

[Useremail] = [_THISROW].[Email]

I do also wonder about the middle portion of the email body but I'll leave that to you to adjust if needed.

I hope this helps!!

rcfberg