Date and Text Field Validation


Badge +7

I'm looking for advice as to how to best implement a solution.

I need to take a written timeframe and evaluate it as a number so I can validate it. I have a task review list with the following fields/types:

Start Date/Date and Time

Due Date/Date and Time

End Date/Date and Time

Review Frequency/Lookup

Reminder Frequency/Lookup

This combination of Start Date/Due Date/Review Frequency/Reminder Frequency allows the users a great deal of flexibility in creating tasks. You can create a task that starts quarterly, is due within 30 days, and sends reminders each week. Or create a task that starts each week, is due within 3 days, and sends reminders every day. Very nice.

  • I have a calculation that determines the number of days between the Start Date and the Due Date (Total Days). The Review and Reminder frequencies are lookup columns to a list that has values assigned to each choice (once = 1, weekly = 7, monthly = 30, etc.).

  • I need a rule on the Review Frequency dropdown field so that the user can't select a Review Frequency that is less than the Total Days to complete the task.

  • I need a rule on the Reminder Frequency so that user can't select a reminder that is greater than the Review Frequency.

So:

Total Days = 14

Review Frequency = Bi-Weekly

Reminder Frequency = Weekly

Validation = True

Total Days = 14

Review Frequency = Weekly

Reminder Frequency = Daily

Validation = False

Total Days = 14

Review Frequency = Bi-Weekly

Reminder Frequency =  Monthly

Validation = False

I've tried a few things, but nothing seems to work, such as a calculated field that returns a true/false value. I think the solution is a combination of things I've tried. What happens is I end up thinking too much and my brain turns to mush and I make this harder than it has to be.

Any advice or suggestions are welcome.


7 replies

Userlevel 5
Badge +14

at one place you say review/remind frequencies are choices, but at the other they are lookups...

from your later explanation I'll assume lookups.

I would say something like this could work as validation rules

review validation

greaterThen(
lookup('ReviewRemindFrequenciesLookupList','ID',parseLookup(ReviewFrequency,false),'Days'),
TotalDays‍
)

remind validation

greaterThan(
lookup('ReviewRemindFrequenciesLookupList','ID',parseLookup(RemindFrequency,false),'Days'),
lookup('ReviewRemindFrequenciesLookupList','ID',parseLookup(ReviewFrequency,false),'Days')
)
Badge +7

Yes, Marian. They are lookup columns that provide the user a choice. Thank you for asking,

I have clarified that.

Badge +7

Hi, Marian.

I have customized your example to fit my list, but it's not working:

greaterThan(lookup('TaskIntervals','ID',parseLookup(ReviewFrequency,false),'Value'),NumberOfScheduledDays)

Where:

TaskIntervals = the list where the task intervals are stored

ID = the ID column in the TaskIntervals list

ReviewFrequency = the named control connected to the lookup field in the form that pulls from the TaskIntervals list

Value = the column containing the numeric value associated with the interval in the TaskIntervals list

NumberofScheduledDays = form variable calculating the number of days between task start date and task due date

When I publish my form and test it, I get this dialogue box:

This isn't what's supposed to happen if they make an invalid selection. It's supposed to give me the error message I typed in. What have I gotten wrong in my formula?

Also, I haven't tried the other formula yet because I haven't gotten this one working.

Userlevel 5
Badge +14

hopefully you are talking about validation rule and not custom validation under control settings.

I would check developer console for any errors.

Badge +7

Yes, Validation Rule.

Userlevel 5
Badge +14

have you checked developer console whether it gives any hint?

Badge +7

Not yet. I've been in meetings all day, but I will take a look and circle back.

Reply