Controls

Re: Date format in date control field type

davydcondy — Mon, 12 Oct 2015 13:27:23 GMT

The date field does not work in an InfoPath, SQL external list.

The date and time field does not matter about the time, but it would be great to not output the time. Only the date input for the date and time data type is important; therefore, 081515 to 8/15/2015 ... 81515 to 8/15/2015.. etc.

donstephen:
I am unable to guarantee if what you need will work, but we can give it a shot - however, chaging the data type to date and time would also require entering the time as well. The entry 41315 to get 4/13/2015 would now need to have a specific time included, which would mean chaging the validation rule's conditions (pattern and string length logic), as well as the formula to extract the date.

Are you encountering issues if the Date field is not set to Date and Time? What would be the entry to the date and time field if we are to add time?

Re: Date format in date control field type

donstephen — Mon, 12 Oct 2015 05:04:06 GMT

I am unable to guarantee if what you need will work, but we can give it a shot - however, chaging the data type to date and time would also require entering the time as well. The entry 41315 to get 4/13/2015 would now need to have a specific time included, which would mean chaging the validation rule's conditions (pattern and string length logic), as well as the formula to extract the date.

Are you encountering issues if the Date field is not set to Date and Time? What would be the entry to the date and time field if we are to add time?

Re: Date format in date control field type

davydcondy — Sat, 10 Oct 2015 16:30:43 GMT

Everything works! However, I want to used the " Date and Time (dateTime) " data type not the date data type.

Can you create an example with using the Date and Time (dateTime) " data type? possibly using the same rules.

Re: Date format in date control field type

donstephen — Thu, 08 Oct 2015 23:56:44 GMT

To hide the DateValue field from display, all you need to do is to delete it from the display. The field will still hold the value since it is still in the schema.

One thing I noticed is that on the second action rule, the formula was not inserted as a function. You will need to click the fx button and enter the formula there.

Please try this updated test form.

Re: Date format in date control field type

davydcondy — Tue, 06 Oct 2015 14:01:17 GMT

When I enter (51615) the dates does not output (5/16/2015), the output is actually the following: concat("20", substring(translate(., "/", ""), 4, 2), "-0", substring(translate(., "/", ""), 1, 1), "-", substring(translate(., "/", ""), 2, 2)).

The second fields also does not hide. I have been trying to created a form, so an action reverts the error (2015-16-5).

Let me know for other ideas.

Re: Date format in date control field type

donstephen — Tue, 06 Oct 2015 02:09:10 GMT

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,

Re: Date format in date control field type

davydcondy — Mon, 05 Oct 2015 20:14:36 GMT

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.

Re: Date format in date control field type

donstephen — Sat, 03 Oct 2015 06:33:09 GMT

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")

Re: Date format in date control field type

davydcondy — Wed, 30 Sep 2015 21:30:48 GMT

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?

Re: Date format in date control field type

davydcondy — Wed, 30 Sep 2015 14:45:51 GMT

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.

Re: Date format in date control field type

donstephen — Fri, 25 Sep 2015 06:44:18 GMT

Hi Davydcondy,

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

Re: Date format in date control field type

davydcondy — Thu, 24 Sep 2015 20:35:14 GMT

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

Do you have any solution ideas for the occurring issue?

Re: Date format in date control field type

davydcondy — Wed, 23 Sep 2015 15:40:52 GMT

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

Re: Date format in date control field type

donstephen — Tue, 22 Sep 2015 17:16:20 GMT

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.

Re: Date format in date control field type

davydcondy — Tue, 22 Sep 2015 16:58:47 GMT

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.