Changing date based on multiple column inputs

ericlmiller86

Hello everyone, I am 🔰 programmer building an app for food redistribution and leftover donations, currently building a form for adding a row in my Donations table, this form needs an app formula to calculate the Best Buy Date that takes info from other columns to compute the number of days to add to the column "Donation Creation Date" [which has a NOW() app formula). The columns to check for data are "Donation Type" which is an Enum type with "Refrigerated" "Frozen" & "Pantry" and two checkbox columns "Contains Dairy" and "Contains Protein" there are multiple conditions to generate the best by date. The conditions are:

"Pantry" hides Contains Dairy and Contains Protein columns and adds 365 days
"Refrigerated" by itself adds 14 days
"Frozen" by itself adds 180 days
Refrigerated + Dairy with or without Protein adds 5 days
Refrigerated + Protein, no Dairy adds 7 days
Frozen + Dairy with or without Protein adds 30 days
Frozen + Protein, no Dairy adds 90 days

I feel like I have a grasp on what I need to do but what I've tried doing hasn't been working.

Thank you to whomever helps me

TeeSee1

You can use IFS().

Place a condition - value pair with higher matching complexity before ones with lower complexity, I.e. "Refrigerated + Protein, no Dairy" before "Refrigerated + Dairy with or without Protein".

You can control show/hide behavior of a column by Show-If setting.

Aurelien

Hi @ericlmiller86

I would suggest to use an IFS() expression in the Formula of the [Best Buy Date] column.

You would have something like:

IFS(
  AND(
   CONTAINS([Donation Type],"Refrigerated"),
   [Contains Dairy]
  ),Donation Creation Date]+5,

  [.........other mixed condition.....],

  CONTAINS([Donation Type],"Pantry"),[Donation Creation Date]+365
  CONTAINS([Donation Type],"Refrigerated"),[Donation Creation Date]+14,
  CONTAINS([Donation Type],"Frozen"),[Donation Creation Date]+180,
  [.........and so on.....]
)

Please note that conditions in the IFS() statement are evaluated sequentially. You may need to adjust conditions to make sure every case is handled properly.

Also, about this:

@ericlmiller86 wrote:

to the column "Donation Creation Date" [which has a NOW() app formula)

you may want to set the type to Date (not DateTime) and change the initial value expression to TODAY() instead of NOW().

For reference:

CONTAINS() - AppSheet Help

IFS() - AppSheet Help

OR() - AppSheet Help

AND() - AppSheet Help