2025 Week 48 | Sigma: Can you announce it?

When I build Data Apps, I find planning is most important part of the process.  As you build your workflow, you may find yourself returning to created objects, more often than usual, to create actions involving newly created objects.  To help illustrate what we’re doing, I have included a flowchart at the bottom of this challenge.

Hidden Data Sheet

Announcements

• Announcement (Text)
• Date to Release (Date)
• Made By (Text)
• Expiration Period (Single-Select)
  • Use whatever periods you like.  I used 3 Days, 1 Week, 2 Weeks, and 1 Month

There should also be the following calculated columns:

• Is Not Expired (Logical):  Determined by the difference of [Date to Release] and Now() and whether or not that period is greater than the specified [Expiration Period]
• Is Released (Logical):  If [Date to Release] <= Now()
• Show Announcement (Logical):  If the record is both Released and Not Expired

Finally, show the Last Updated System columns.

Empty Input Table – Emails

In a real application, this would come from a dimensional table containing user information.  For our purposes, we’re going to use an empty input table that is Editable in draft with the following inputs:

• Email (Text)

This table should have 1 calculated column:

• Is Current User (Logical):  Determines if CurrentUserEmail() matches [Email]

At a minimum, you should a row with your email.

Joined Announcements by User

Use a left outer join to the Announcements to the Emails table.  You should have every announcement represented with Email data on the right.  This should fan your data so every email has each announcement.  You can use a custom formula (True) as your Join Keys.  Filter this table to only show the Current User.

Linked Input Table:  Announcements Viewed

This will be a child of the joined Announcements by User table.  This should be Editable in published version (all users).  

These columns should be used as a primary key:

• Announcement
• Email

These are necessary context columns:

• Show Announcement (Hidden)
  • Filter to only show True records
• Made By
• Date to Release

Add one input column:

• Email has Viewed (Logical)

Add one Summary Column:

• Unviewed Announcements:  Count of rows where Email Has Viewed does not equal True

Visible Page

For our purposes today, we do not care what is on the visible page.  Make whatever you like here.  I am embedding my previous Sigma challenge workbook. 

The only thing that you must show is a button that opens an Announcements Modal.  This button should show an indicator that shows with the Current User has Unviewed Announcements. 

Modal – Announcements

Display a table of all announcements for the current user, with unread announcements highlighted.  This table should be sorted with new announcements on top.

Also, include a button for users to Make an Announcement, which opens a new modal.

Modal – Make New Announcement

This modal should include 3 controls:

• Announcement (Text Input)
• When to Release (Date)
• Expire After (List):  This should be the same options as the [Expiration Period] column

If a user clicks the Make Announcement button, insert a row in the Announcements table with the 3 controls and the CurrentUserFullName().  Also include an easy Clear Form button.  Don’t forget to clear all controls when submitting an announcement and closing the modal, not just on click of Clear Form.

Finishing Touches

• Give the dashboard a descriptive title
• Arrange your elements into a cohesive dashboard
• Update your workbook’s settings to give it a more personalized flair

Food for thought

• How might you give your announcements indicator a more polished look? Instead of a native button element, you could use a publicly-hosted set of images and dynamically switch between a read and unread image based on if the user has Unviewed Announcements.
• This is one workflow option that utilizes an empty input table for announcements and a linked input table to track if they’ve been read.  Could you think of one that uses a single empty input table to submit and track announcements?

Tips

• With this workflow, [Email Has Viewed] could have a True, False, or null input.  Do not forget to account for this when referencing this column in formulas.
• Prefix your controls with a meaningful label.  As your Data Apps get more complex, developers will appreciate knowing which “ID” control is which when selecting a field in the formula bar.  
• Group all similar controls into containers, so you can easily clear them with one action