How to validate the date column in Infopath form

No comments

 3,388 total views,  2 views today

Infopath was my one of the favorite form designer tool, but unfortunately, Microsoft will stop support for Infopath form from 2022 onwards. In this article, I will share some techniques of how we can validate the date column in the InfoPath form (SharePoint). 

Generally, if we have column type as the date – then by default we will get a lot of validation pattern, but if we have text type field then we have to write our validation patterns or rules. For example, we will validate the data as dd.mm.yyyy(example: 14.03.2020) let’s see how we can do. 

If we create a text field with the data type as Date (date) – then if we click on the properties of that column – click on the “Format” button, we will see the all available format from all countries. So, there won’t be an issue with the “Date (date)” type column.

Date format in column in Infopath form
Date format in the column in Infopath form

However, if we have a column with the text type, the “Format” button will be in disable mode. 

Then, how we can format this as desired format (dd.mm.yyyy) using the custom pattern and with some validation rules.

Let’s say we have “field2txt” as text type column then if we apply the rule like 

if "field2txt" does not match pattern "\d\d.\d\d.\d\d\d\d"

Then

Show the message something like - invalid date format, please enter the format with dd.mm.yyyy format.

Date format in column in Infopath form custom pattern

Date format in the column in Infopath form the custom pattern

This will work fine as long as you enter date value as dd.mm.yyyy – but if you enter the month as 20 and day as 40 – still this will consider this as a valid value because it just checks whether day and month have the two digits number.  

Then, how to validate the day and month from this date (text type) field?

Maybe it could be handled in many better ways – but I thought of fixing this as quickly using the below approach.

Create two columns, name as “Extract DD from Date field” and “Extract MM from Date field” with the whole number date type and make these as hidden fields. These will only be used for calculation purposes.

Let’s take the example of 20.15.2019 (this is an invalid date considering the dd.mm.yyyy format because the month cannot be 15).

Now we will make use of the above fields and the string substring() function.

Get the first two digits for the day (DD) from the above original date field (text type) by using the substring () function – like below and set this value to “Extract DD from Date field

substring(field2txt; 0; 3)

Get the first two digits for the day (MM) from the above original date field (text type) by using the substring () function – like below and set this value to “Extract MM from Date field

substring(field2txt; 4; 2)

Notes:

  • In the above “field2txt” is the date field (text data type) where the user will enter the date.

In the rule of “field2txt” date column apply the validation rules:

Rule 1:

If "Extract DD from Date field" greater than 31 or less than 1

Validation message: Invalid day in the date field, please enter the day between 1 to 31. 

Rule 2:

If "Extract MM from Date field" greater than 12 or less than 1

Validation message: Invalid month in the date field, please enter the month between 1 to 12.


Demo Example: Extract day from the date field.

Extract day from date field in Infopath form
Extract day from a date field in Infopath form

Demo Example: Extract month from the date field.

Extract month from date field in Infopath form
Extract month from a date field in Infopath form

Note:

  • We can set the default value in the field by clicking on the fx button.

Finally, there will be three rules in the “field2txt” date column:

Rule 1: Day less than 1 or greater than 31 checkings.

Date format validation in infopath form - day part
Date format validation in InfoPath form – daypart

Rule 2: Month less than 1 or greater than 12 checkings.

Date format validation in infopath form - month part
Date format validation in InfoPath form – month part

Rule 3: Does not match the custom pattern.

Date format validation in infopath form - does not match custom pattern
Date format validation in InfoPath form – does not match the custom pattern

 Notes:

  • The way we have validated the day and month, similarly we can validate the year part also, here I have not shown that.

Summary:

Hence, in this article we have learned the below:

  • How to do the date validation in Infopath forms.
  • How to create an InfoPath form to auto-populate data in Sharepoint 2013.
  • How to use the substring in InfoPath form.
  • How to validate the data before submitting the form in Infopath form.

See Also:

Free download SharePoint Online & Office 365 Administration eBook(238 Pages)

Get the PDF eBook from here:


[purchase_link id="0" style="button" color="blue" text="Free Download SharePoint Online & Office 365 Administration"]

 

 
FREE DOWNLOAD

Send download link to:

Subscribe to get exclusive content and recommendations every month. You can unsubscribe anytime.