2025 Week 40 | Sigma: Do you approve?

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

Base Data Table

RETAIL / TASTY_BYTES_FOOD_TRUCKS / MENU_ITEM

Linked Input Table:  Override Table

We want to hide this table, so users are forced to use our modals for interaction.  This allows us to control their behavior more than opening the full input table to end users.

• Set to allow edits in Published mode 
• Unique Key should be Menu Item ID
• For context, include:
  • Menu Type
  • Truck Brand Name
  • Menu Item Name
  • Item Category
  • Item Subcategory
  • Cost of Goods Usd
  • Sale Price Usd
• Allow the following inputs:
  • New COGS (Number)
  • New Price (Number)
  • Override Status (Text):  Allow 3 values (Accepted, Pending, Rejected)
• Create calculated columns:
  • Updated COGS & Updated Price:  When Status = “Accepted”, should show the new value.  Otherwise, show the original value

Data Validation Hidden Sheet

I choose to hide controls that I don’t want end users to see on a separate page, but this could be done on the hidden data sheet

Put hidden controls for both modals here.  At a minimum, you will need two Menu Item ID controls (one for each modal).  You may choose to have more.  

Why do we need ID controls for each modal?  These are used so Sigma can remember the ID when it needs to update the row, as well as to filter or lookup relevant information for the modals.   I like to hide these because IDs typically do not mean anything to the end user.

I also chose to have a hidden child table that filters to the selected Update Item. This allowed me to easily get the informational data in a Text Box.  This is just one way to do this.

End Users should only have access to these elements

Controls

• A segmented control that lets users select the workbook’s Mode:  Viewer or Approver
  • Viewer displays only the Menu Items table
  • Approver displays only the Pending Updates table
• A popover containing any relevant filters you want, like Truck Name, Menu Type, Item Category, or Subcategory
  • Include a button to easily clear all filters

Visible Elements 

Menu Items table:

• Parent:  Base Data
• Hide all IDs
• Sort by when the Override Table was last updated, descending
• Include Lookups from the Override Table:
  • Updated Price
  • Updated COGS
  • Last Updated At
  • Status:  Instead of showing a status name, show an icon to denote the status.
    • I used:  = Original Values, = Updated Values, = Pending Update, = Requested Update Rejected
• Add a column that lets users “Update Item”
  • Tip:  To not be repetitive, I named this column “Update Item” and set it to display “” on every row
  • On select of this column:
    • Set the Request Menu Item ID hidden control
    • Set the visible COGS and Price controls on the Request Item Update modal
    • Open the Request Item Update modal

Pending Updates table:

• Parent:  Override Table
• Hide the Updated COGS/Price and ID columns
• Sort by Last Updated At
• Only show Pending Updates
• Add a column that lets users “Review Changes”
  • On select of this column:
    • Set Review Menu Item ID hidden control 
    • Set the Review COGS & Price Controls.  These could be hidden or visible on the Review Changes modal
    • Open the Review Changes modal

Modals

Request Item Update

• Include information about the Truck, Menu Type, Menu Item, Category, and Subcategory
• Include 2 controls for users to input their requested updates:
  • COGS (Number input)
  • Price (Number Input)
• Include 1 button to Submit Changes that performs these actions on click:
  • Updates the row for that Menu Item ID into our Override Table Input Table
    • Use the controls to input the appropriate columns
    • Set Status to Pending
• Clear controls on this modal, as well as the hidden Request Menu Item ID control
• Close the modal

Review Changes

• Include information about the Truck, Menu Type, Menu Item, Category, and Subcategory
• Compare the New vs Original COGS and Price for this item
• Include 2 buttons:
  • Approve Changes that performs these actions on click:
    • Updates the row for that Menu Item ID into our Override Table Input Table
      • Set the New COGS / Price to the New values
      • Set Status to Accepted
    • Clear any controls used to populate this modal (hidden or visible)
    • Close the modal
  • Reject Changes that performs these actions on click:
    • Updates the row for that Menu Item ID into our Override Table Input Table
      • Nullify the New COGS / Price columns
      • Set Status to Rejected
    • Clear any controls used to populate this modal (hidden or visible)
    • Close the modal

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

• This is a fairly simple approval process.  How might we add security to the process, so only designated people can respond to requests?
• Input tables never actually override your data.  How might we take this user input and incorporate it into our warehouse?

Tips

• 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.  For today’s exercise, we might use “sub-” to denote our controls impact the Submit Request modal and “rev-” to denote our controls impact the Review Requests modal.
• Group all similar controls into containers, so you can easily clear them with one action