17,180 total views, 1 views today
Validate the date column in Infopath form – Infopath was one of the favorite form designer tools, 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).
Validate the date column in Infopath form (InfoPath data validation)
Generally, if we have column type as the date – then by default we will get a lot of validation patterns, but if we have a 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 it.
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.
However, if we have a column with the text type, the “Format” button will be in disabled 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 a text type column then if we apply the rule like
if "field2txt" does not match pattern "\d\d.\d\d.\d\d\d\d"
Show the message something like - invalid date format, please enter the format with dd.mm.yyyy format.
Date format in the column in Infopath form the custom pattern
This will work fine as long as you enter the 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 the day and month have the two digits number.
Then, how to validate the day and month from this date (text type) field?
InfoPath date validation (InfoPath data validation) – Infopath date format
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 “Extract DD from Date field” and “Extract MM from Date field” with the whole number date type and make these 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)
- In the above “field2txt” is the date field (text data type) where the user will enter the date.
In the rule of the “field2txt” date column apply the validation rules:
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.
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.
Format date InfoPath
Demo Example: Extract day from the date field.
Demo – Validate the date column in Infopath form (InfoPath data validation)
Demo Example: Extract the month from the date field.
- 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.
Rule 2: Month less than 1 or greater than 12 checkings.
Rule 3: Does not match the custom pattern.
- The way we have validated the day and month, similarly we can validate the year part also, here I have not shown that.
Summary: validate the date column in Infopath form (InfoPath date validation)
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: SharePoint Online tutorial
You may also like the below SharePoint Online tutorials:
- How to a copy list item to another list using SharePoint designer workflow
- SharePoint Framework (SPFx) development environment Setup step by step
- 3 ways add a picture library in the communication site – SharePoint Online
- SharePoint generation or version history from the year 2000 to 2020
- Office 365: Getting started with SharePoint PnP PowerShell – installation
- In 2 steps convert a classic SharePoint page to modern using PnP
- Office 365: Retrieve hub sites and associated sites using PnP Powershell
- Create a modern team site using PnP PowerShell in SharePoint
- In 4 steps access SharePoint online data using postman tool
- SharePoint admin center: Learn SharePoint online administration in an hour – step by step
- SharePoint REST API: GET vs POST vs PUT vs DELETE vs PATCH
- Office 365: Understanding the hub site in SharePoint online
- Create SharePoint online list using PnP provisioning template
- List Template IDs In SharePoint Online/SharePoint 2019/2016/2013/2010/2007
- Configure InfoPath Forms Services