DateFormula Validation

Business Central/NAV Developers Forum
1

I’m in the process of creating a data import using NAV’s XML web services. My upload contains a number of DateFormula fields and I need an efficient way to validate the fields before I update the system. I’d like to use a RegEx but I can’t find a DateFormula RegEx. Does anyone have a RegEx or other idea?

2

There is a DateFormula data type, just like an Integer or Text. Import into that and it will error out if it is not formatted correctly.

3

The problem is the DateFormula data type is defined as a string data type via the webservice. So I wouldn’t be able to validate it until I actually tried to update the record. I need to validate all the data in the spreadsheet before attempting to update the system. This way I can force the user to fix all errors before updating the system.

4

From the NAV Client help, the following rules describe the valid syntax of date expressions. Maybe it can help you write a reg-ex (I would just for the fun of it, sad, but I don’t have the time)

The following rules describe the valid syntax of date expressions:

DateExpression = [][][]
= []
= + | -
= | |
= Positive integer
= D | WD | W | M | Q | Y (D=day, WD=weekday, W=week, M=month, Q=quarter, Y=year)
= C (C=current)

5

Ugh. I finally did it. I hate these things. But I think I have one that works. I can’t be the only person who’s had a need to validate a date expression in an efficient way. This SHOULD work. Hopefully this will save someone else the pain. Please let me know if you find an error.

DateFormula RegEx = (?i)[-+]?([1-9]{1,2}(WD|[DWMQY])|(WD|[DWMQY])[1-9]{1,2}|C(WD|[DWMQY]))

= [-+]?([1-9]{1,2}(WD|[DWMQY])|(WD|[DWMQY])[1-9]{1,2}|C(WD|[DWMQY]))
= [-+]?
= [1-9]{1,2}
= ([1-9]{1,2}(WD|[DWMQY])|(WD|[DWMQY])[1-9]{1,2}|C(WD|[DWMQY]))
= (WD|[DWMQY])
= C
= [1-9]{1,2}(WD|[DWMQY])
= (WD|[DWMQY])[1-9]{1,2}
= C(WD|[DWMQY])
Case insensitive = (?i)

6

Actually that one has bug that prevents the use of 0 as a number, therefore 10, 20, 30, etc wouldn’t validate.

Here is a corrected version:

DateFormula RegEx = (?i)[-+]?(([1-9][0-9]?)(WD|[DWMQY])|(WD|[DWMQY])([1-9][0-9]?)|C(WD|[DWMQY]))

= [-+]?([1-9][0-9]?(WD|[DWMQY])|(WD|[DWMQY])[1-9][0-9]?|C(WD|[DWMQY]))
= [-+]?
= [1-9][0-9]?
= ([1-9][0-9]?(WD|[DWMQY])|(WD|[DWMQY])[1-9][0-9]?|C(WD|[DWMQY]))
= (WD|[DWMQY])
= C
= [1-9][0-9]?(WD|[DWMQY])
= (WD|[DWMQY])[1-9][0-9]?
= C(WD|[DWMQY])
Case insensitive = (?i)