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.