Error Messages in Spreadsheet Makeover #1

Ask for Help
1

In this Spreadsheet Makeover, you have red-shaded error messages appear when some data is missing.
https://youtu.be/D2tuTdUVx3k?si=mR4iY1LdXylpknj6&t=468
How do you do that?

2

Hey, thanks for watching!

Here’s the formula for that column, which is essentially a bunch of cases and outcomes:

#Lookup commission rates for the product

product_rates = Commissions.lookupRecords(Product=$Product)

## If status is discarded or potential, don't calculate commission rate
if $Status in ['Discarded', 'Potential']:
  return None

## If there is no close date, ask user to enter sale date
if not $Close_Date and $Status != "Potential":
  return "Missing close date"

## If there is no product or rates for the product, ask user to enter sale date  
if not $Product:
  return "Missing Product"

## Find all product commission rates valid before this sale date
if product_rates:
  past_rates = [r for r in product_rates if r.Start_Date <= $Close_Date]

## If there are no rates for the given sale date and product, ask for rate  
if not past_rates:
  return "Missing rate in commissions table"

## And find the most recent valid rate  
if past_rates:  
  current_rate = max(past_rates, key=lambda r: r.Start_Date)
  return current_rate.Commission_Rate

In this case, since the Commission Rate table’s type is “Numeric”, the returned error state strings like “Missing rate in commissions table” show up as red due to the mismatched type. You can do similar things in “Text” columns using conditional formatting.

3

This is excellent. I used a similar but cruder approach in my recent database. I will save this for the next time I need to make sure several error conditions don’t apply.

1 Like