Forum Discussion

ArletaMaæka_a's avatar
ArletaMaæka_a
Qrew Member
2 years ago

Formula to test if values provided in two data fields satisfy certain criteria

Hello,

I built an app that uses QuickBase form to allow users to provide different information about expiring licenses. In this form I'm also introducing various error messages that should explain which field values are wrong and why and also prevent the form from being saved.

Form contains two date fields named Expiration Date and Reminder Date and I want to introduce field named Reminder Date Error Message, that will work as follows

If Reminder Date is an earlier date ten Expiration Date it should be empty.

If Reminder Date is empty and Expiration Date is filled in it should be empty.

If Expiration Date is empty and Reminder Date is filled in it should be empty.

If Reminder Date is date later then Expiration Date it should return message "Reminder Date is later then Expiration Date".

Currently my formula looks like this

If(IsNull([Reminder Date]) and IsNull([Expiration Date]),"",
If([Reminder Date]<[Expiration Date],"",
If([Reminder Date]>[Expiration Date],"Error- Reminder date is later than Expiration Date",
If(not IsNull([Expiration Date]) and IsNull([Reminder Date]), "",
If(IsNull([Reminder Date]) and not IsNull([Expiration Date]),"")))))

but it doesn't work as expected for one case - when Reminder Date is empty and Expiration Date is filled in. In this case formula returns ???.

Can you please help me to fix it?

Thank you for your help!

Arleta



------------------------------
Arleta Maæka³a
------------------------------
No RepliesBe the first to reply