InfoPath Dev

When using InfoPath, the default DATE data type does not have a input recognition when entering only digits such as (41315) to (4/13/15). How to change the user input automatically of number/digits into a date format?

Hello Davydcondy,

This is possible using a rule and some conditions. Please follow these steps:

1. Add a rule to your textbox control with the following conditions (make sure to select "and" operator):
    a. field "does not match pattern" Custom pattern, and enter the pattern: \d\d/\d\d/\d\d\d\d
    b. the expression: string-length(translate(., "/", "")) = 10
    c. the expression: string-length(translate(., "/0123456789", "")) = 0

2. Add action to the rule:
    a. Set a field's value
    b. Select your textbox field
    c. Insert formula for the value (click the fx button): concat("20", substring(translate(., "/", ""), 5, 2), "-", substring(translate(., "/", ""), 1, 2), "-", substring(translate(., "/", ""), 3, 2))

Hope this helps.

(edited)

I have added a similar field's value for a text box; however, it will break the concepts of date picker.

The data function works for any date such as 09202015 to (09/20/2015). Here is the formula I have used: concat("", substring(translate(., "/// ", ""), 1, 2), "/", substring(translate(., "/// ", ""), 3, 2), "/", substring(translate(., "/// ", ""), 5, 4))

I have tried your method, but it does not work. The preview field contains too many forward dash and outputs an error message. See conditions below:

donstephen:

Hello Davydcondy,

This is possible using a rule and some conditions. Please follow these steps:

1. Add a rule to your textbox control with the following conditions (make sure to select "and" operator):
    a. field "does not match pattern" Custom pattern, and enter the pattern: \d\d/\d\d/\d\d\d\d
    b. the expression: string-length(translate(., "()- ", "")) = 10
    c. the expression: string-length(translate(., "()- 0123456789", "")) = 0

2. Add action to the rule:
    a. Set a field's value
    b. Select your textbox field
    c. Insert formula for the value (click the fx button): concat(substring(translate(., "()- ", ""), 1, 2), "/", substring(translate(., "()- ", ""), 3, 2), "/", substring(translate(., "()- ", ""), 5, 4))

Hope this helps.

Hi Davydcondy,

I saw some inconsistencies with the approach I provided you with and didn't notice that the textbox was using a different format. If the text box's data type is set to Date, then it will look for the format yyyy-mm-dd. I'll update the previous approach with a different formula.

Hi Davydcondy,

I have attached a form which fixes the date format for the text box.

It looks like there will be two scenarios if the date entered will have different string lengths (041315 or 41315). If you check the rules on field1, you will notice two rules that will run depending on the scenario stated.

Please check if this approach would work for you. 

Thank you so much, because it was becoming complicated. This strategy worked great!

However, I am also using the form to view data from a list while using the InfoPath form.

The Microsoft SQL only has the date and time format as (5/15/2014 8:00 PM) and output in the InfoPath form as 2014-05-15T20:00:00

What would be another expression or method to only take the 2014-05-15 and remove anything after that such as T20:00:00. Because once I delete (T20:00:00), the date will automatically configure to 5/15/2014.

I have attached an image of the issues, see below.

donstephen:

Hi Davydcondy,

I have attached a form which fixes the date format for the text box.

It looks like there will be two scenarios if the date entered will have different string lengths (041315 or 41315). If you check the rules on field1, you will notice two rules that will run depending on the scenario stated.

Please check if this approach would work for you. 

Hi,

Could you try using the formula:

substring-before(field1, "T")

where field1 is the field which holds tne date and time value.

What this formula will do is return all values before "T" and should return the date value only.

Hope this helps. 

It has been working! Thank you so much for the advice and contribution.

I have another issue with a fields using the whole number (integer) for Currency Format. Please see the link: http://www.infopathdev.com/forums/p/30509/104107.aspx#104107

The rules with action formulas work; however, I am unable to save the values.

Do you have any solution ideas for the occurring issue?

Hi Davydcondy,

I am currently unable to determine the issue. Could you provide more details and also the error you are receiving upon saving?

I am not getting an error. The issue that I am having is by connecting two fields to mask the original value from the external content type list. After publishing the form with the rules, the input data will not be save to the list.

Hi donstephen,


Since SQL databases only use date and time (data types), I am testing the same rules and conditions.


The rules and rule condition works with the date picker; however, the input recognition is not working.


The date is backwards. See image below.

donstephen:

Hi Davydcondy,

I am currently unable to determine the issue. Could you provide more details and also the error you are receiving upon saving?

Hi,

When using the InfoPath date picker, the actual value (for date time data type) would be like this:

The actual value underneath the date picker is different from what your SQL database recognizes. I believe the date and time format for SQL is 2015-09-22 00:00:00.000. Could you try using a substring to get the dat only from infopath and concatenate the value with  00:00:00.000 then try submitting it?

You formula should be similar to:

concat(substring-before(dateField,"T"), " 00:00:00.000")

I have tried the formula in the Date Picker Properties for the value, but it did not work. I am working on building a formula as well.


See attachment for the occurring error in the sample form except it is a local form and not from a external list.

Hi,

It appears that the Date field is using the formula as the default value, which is causing an issue on the concatenation.

I have attached an updated form which uses a DateValue field (this is supposed to be hidden from the display) - this field uses the formula from the previous post, you could use this to submit the date value to your database. Could you try using the approach from the form attached and let me know how it goes?

Regards,