General

Re: Need some help with infopath 2010

tiatia — Thu, 09 Jan 2014 05:15:01 GMT

I hope you can help me. I have an info path web part form that is connected to an excel web part. I have 3 fields in the IP form that routes to excel for a calculation - # of employees reported, empSTHours, empOTHours. Upon button click on the form, the data populates in an excel spreadsheet with calculated combined straight time hours and combined over time hours. For example: 13 employees worked 8:00 straight time hours and 4:23 overtime. The total equals 104 straight time hours, 56:59 overtime hours and a total of 160:59. I want the user to enter the totals back in the info path form after calculations have been done and I want to require it to be in the same format as the fellow person in this post, i.e. 00:00. So, I followed all of the steps attached here and my form requires that the user enter the data as time up to 23:59. I need to format the cells, however, to allow for any time over 24 hours. So, for example, I need the user to be able to enter 104:00 in the combinedSTHours field, and 56:59 in combinedOTHours and 160:59 in totalHours. Can you help me?

Re: Need some help with infopath 2007

umjorge2 — Tue, 05 Feb 2013 22:05:30 GMT

Either way I'm satisfied with that portion now. The other thing I'm trying to get it to do now is to run some sorta search query on the data that is being received from my access database. In the picture below is what my form looks like with the entry fields in the table at the top (the main connection submit query) then the table at the bottom is all the entered records ordered by most recent date which is a second query data connection receiving the info that is stored in my access database from running the first submit query (all info is gibberish for testing purposes). Is there a way, by the users command using date pickers and a search box, to narrow down the received data list to display a particular date range or word by running some sorta search query? For example like booking a flight using a date range using 2 different date pickers, or searching for a particular word entered into one of the text boxes?

Re: Need some help with infopath 2007

Hilary Stoupa — Tue, 05 Feb 2013 15:19:59 GMT

Yes, the data type can't be changed if you are using a database bound form. That is expected behavior.

Re: Need some help with infopath 2007

umjorge2 — Thu, 31 Jan 2013 16:40:18 GMT

I think its pretty safe to say that it does have something to do with the binding to the Access database fields of why its returning 0:00 when I type in an invalid time. I created a separate access database with just one field (called Time) and created one field in infopath also called time. I linked the access database to the form and binded the time field in infopath to the time field in Access. I applied the rules with the conditions to add the colon and reset the value when an invalid time is entered. Same thing happens the value defaults to 0:00 when an invalid time is entered. I did what you said before by disabling the rules and entering a time with the colon manually and then looking up the node after saving as an xml for the time field I created and it says <d:Table1 ID="" Time="1899-12-30T23:59:00"></d:Table1>. In the sample form you gave me doing the same thing disabling the rules and saving an xml and opening it in notepad the node there says <my:field1>1899-12-30T22:33:00</my:field1>. So the format of the date and time look the same but why does it return 0:00 in my form? Again data type under the properties says optional_dateTime.notz and is greyed out whereas in the sample it says Date and Time for data type and isn't greyed out.

As I mentioned before if there's an easy way around this, I'd like it to do what it does in your sample form but if not, the 0:00 looks better than before. I really appreciate all the help so far and couldn't have gotten to this point without your help Hilary :)

Re: Need some help with infopath 2007

umjorge2 — Wed, 30 Jan 2013 20:15:45 GMT

Thanks Hilary almost there. That's exactly what I want it to do. The only difference when I apply the condition in my form is now when something greater than 2359 is entered, 00:00 is returned with the validation error. I'm still wondering if its because the field in my form is binded to a data field in Access with the node for my field having that 1899-12-30T in it with the data type being optional_dateTime.notz under the binding whereas the field in the sample form you gave isn't binded to something from Access (data type is Date and Time (datetime) . I've double checked everything and I have my rules with conditions and values entered exactly the same as what is shown in the sample so that's the only difference I can think of, just like last time. I'd prefer it to do what its doing in the sample but if nothing can be done after this point, it looks much better than before with 00:00.

Re: Need some help with infopath 2007

Hilary Stoupa — Tue, 29 Jan 2013 23:16:44 GMT

See if this does what you want as far as resetting the value.

Re: Need some help with infopath 2007

umjorge2 — Tue, 29 Jan 2013 21:03:21 GMT

Thanks Hilary. Forgive me but I'm still stooped on this (I'm very novice when it comes to any programming whatsoever). Yes I want them to enter 2359 and have the colon added with the condition that if they enter anything greater than that (ie 2360 would be an error), it just stays on that value they entered with the validation error rather then defaulting to todays date or the 1899 date format. I noticed that for example if I manually add in the colon with an invalid time (I.E. 55:55) then 55:55 is what is displayed when I tab away with the validation red box error, which is what I want it to show (just what is typed in). The colon doesn't have to be there (ie automatically added), so if they typed in 5555 then a validation is shown with just "5555" in the box. I'm unsure where I would set that condition exactly. Do I add it on to the existing conditions (set condition) for both rules? I tried doing stuff there that wasn't working, like saying only apply rule (along with the other conditions) if the time is less than 23:59 (they make me type in a colon since the only options for a greater than condition are typing in a date and time which they want a year, month, and day with the time, select a field or group or use a formula) but that did nothing.

Another way to put all this is can I display something else as an error in the box, if an invalid date is put in there?

I really appreciate all the help so far!

Re: Need some help with infopath 2007

Hilary Stoupa — Fri, 25 Jan 2013 22:50:34 GMT

I realize that using today will return today's date. I thought that may be less confusing for your users.

You want them to enter 2359, right? And have the colon added? So your condition would be if the entry is greater that 2359, without the colon. You are going to have challenges with this because you may also want to verify that they don't enter anything greater than "59" for the last to chars in the string - you may need to add a condition to check for that as well.

Re: Need some help with infopath 2007

umjorge2 — Fri, 25 Jan 2013 22:26:39 GMT

When I put in today() instead of 1899 its returning todays date with the validation error in the text box now when I type in an invalid time. I'm trying to get it so that it just displays whatever invalid time was put in, if its greater than 23:59 or less than 00:00. I tried some conditional formatting with the rules playing around with the order of less than or greater then with 23:59 or 00:00 without success.

Re: Need some help with infopath 2007

Hilary Stoupa — Fri, 25 Jan 2013 22:00:32 GMT

You could also use today() instead of 1899:

Rule 1: concat(today(), "T", "0", substring(., 1, 1), ":", substring(., 2, 2), ":00")

I think you could check to see if something was invalid in your rule conditions - if the number entered is greater than 2400, perhaps?

Re: Need some help with infopath 2007

umjorge2 — Fri, 25 Jan 2013 21:10:41 GMT

Thanks Hilary I almost got my form working like a charm! I get no validation errors now when the user just types a valid time and the colon gets added in automatically. The only thing now is when they enter an invalid time using 4 numbers (for example 9999) then 1899-12-30T99:99:00 is what shows up in the text box with the validation error. Is there a way to have it not display it like that but just 9999 with the validation error? Maybe I'm just being picky with the way it should look but it just looks messy when the 1899 thing is returned. Heres the modified coding, concatting in 1899-12-30T that you said to put in that I'm running for both the rules for adding in the colon automatically

Rule 1: concat("1899-12-30T", "0", substring(., 1, 1), ":", substring(., 2, 2), ":00")

Rule 2: concat("1899-12-30T", substring(., 1, 2), ":", substring(., 3, 2), ":00")

Re: Need some help with infopath 2007

Hilary Stoupa — Fri, 25 Jan 2013 01:57:39 GMT

Euw. Well, that't the trick, then - when you modify what the user is entering via rules, you need the data to look like what the form expects for validation. Looks like it needs to be a date time - so you'd preface the time value sample I gave you with 1899-12-30T, you could just concat that in, I suppose - and you could probably use today() instead - check and see what your data in Access looks like.

Re: Need some help with infopath 2007

umjorge2 — Thu, 24 Jan 2013 22:17:51 GMT

One line of data shows Report_time="" and then a line of data below it shows Report_time="1899-12-30T15:25:00"

Re: Need some help with infopath 2007

Hilary Stoupa — Thu, 24 Jan 2013 22:06:00 GMT

Disable the rules. Enter time in the report time field, with the colon, so you don't get the validation error. Use File > Save As to save the XML. Open the XML in a text editor like Notepad. Search for Report_time to find the node. What's the data look like?

Re: Need some help with infopath 2007

umjorge2 — Thu, 24 Jan 2013 21:19:05 GMT

Thanks Hilary thats exactly what I want to do, and it works the way I want it to in my form with the colon automatically appearing however I'm getting an invalid data red box appearing around my text box when I just type in a time without a colon and when I manually type in a colon there is no red box. This may have something to do with my form being linked to an access database thats putting in a different time format thats binded to whatever that format is in access. Under the text box properties under binding, my data type says optional_dateTime.notz and is greyed out whereas in your form the data type under the binding says Time (time) and is not greyed out. I'm not sure if this is the problem but its the only difference in terms of the properties go. Heres a picture of the 2 text box properties that show what I'm talking about, the one on the left is from mine and the one on the right is the sample you posted.