Adventures with Dataverse: Trouble with Formula Columns

I had a recent ask by a customer for how to handle flipping a Yes/No field in a Dataverse table based on a couple of criteria, a Schedule column and Last Perform On Elapsed Days column.

Last Perform on Elapsed Days ended up being a Calculated column, instead of a Formula.

For some reason, the Formula kept complaining about the date types being different, even though they were the exact same. I tested in a Canvas App just to be sure.

The Schedule column was a Choice colum with the following values: None, Every, Quarterly, Once a Month and Twice a Month.

The Yes/No field that had to be set was called Schedule (Calculated) and was based on the following logic:

• If Scheduled = Every Then Yes.
• If Scheduled = Quarterly And Last Performed On Elapsed Days > 90 Then Yes.
• If Scheduled = Once a Month And Last Performed On Elapsed Days > 30 Then Yes.
• If Scheduled = Twice a Month And Last Performed On Elapsed Days > 15 Then Yes.
• Else No.

Now let’s enter some sample data.

Everything seems to check out!

While I would have loved to use Formula columns, just didn’t work out for this use case, hopefully that will change in the future!

Download the solution and test in your own environment at https://mattruma.com/wp-content/uploads/2024/04/POC4SolutionFormula_1_0_0_1.zip.