Infopath codeless programming (walkthrough) 2
Other parts: 1, 3.
Bookmark that post
Tested for SP2007 web forms, SP2010 list and library forms (note: the form has to be redesigned significiantly for list form because of different data fields structure).
Updated: 5 more forms added at the bottom: “work days calculation (excluding weekends and holidays)”, “work days calculation in the list form”, “day of the week calculation”, “Days since form was created” and “Hours Calculations”.
Special thanks: Karl D. Swartzendruber, Karin Edmondson
So what about so called “codeless programming” in InfoPath? As we all probably know, InfoPath can performed only 16 calulations without user interaction. But what if your business rules require you to iterate through 17 repeating elements? You will get a message box from InfoPath with phrase “The rules or code may be causing an infinite loop”. Yes, in InfoPath 16 is a very big number and 17 is considered infinite. To you and I this might seem stupid and arbitrarily restrictive, but Microsoft’s wisdom is greater than ours. Perhaps they know that 17th iteration would give us developers too much power. It would break the fine balance of Microsoft applications, make other development tools obsolete, and become unbearable for non InfoPath developers. But let’s drop the light irony and just accept that InfoPath already has more codeless programming power than mere mortals should ever need.
Today’s challenge is to find the difference in days between 2 datepicker controls disregarding the absence of datediff() function in InfoPath. We are going to show that the 16 iterations limit just can’t stop the true codeless programmer.
Disclaimer: During developing cycle author has been having in mind “finite automates” principles as well as primitive binary search.
Anyway, lets get to the business. Date Difference Calculation. Form to download.
Our recipy is the following: We will need 3 fields. 2 of them are date type fields with datepicker controls (startDate, endDate) and one (dayDifference) is a text type field represented by a simple textbox. All rules have exactly 1 action.
startDate rule:
condition expession: ../my:endDate != “”
field: dayDifference
string value: 0;4096
endDate rule:
condition expession: ../my:startDate != “”
field: dayDifference
string value: 0;4096
dayDifference rules:
MainRule:
condition expession:
xdDate:AddDays(../my:startDate, substring-before(., “;”)) != ../my:endDate and contains(., “;”)
field: dayDifference
xPath expression: concat(substring-before(., “;”) + substring-after(., “;”) * ((translate(xdDate:AddDays(../my:startDate, substring-before(., “;”)), “-“, “”) < translate(../my:endDate, “-“, “”)) * 2 – 1), “;”, substring-after(., “;”) div 2)
finalize rule:
condition expession: contains(., “;”)
field: dayDifference
xPath expression: substring-before(., “;”)
Your form is ready!
What else can be done by applying same principles:
Here is form
that shows amount of days since it was created
Form that shows current day of the week and day of the week from Date Picker control.
Same but with Hours: Hours calculation
The tricky one: Work days calculation (business days)
Work days implemented in the list form.
This form was kindly provided by blog reader Karin Edmondson. The form won’t open in preview mode because of the obvious reason – the connections are pointed to Karin’s server. So your only option is to examine his formulas and try to reproduce them in your own form. Also another nice thing about this form is it takes into account half days.
That’s it. Happy codeless programming!
Posted under tags: Infopath, Date Picker Control
Hello,
i like your steps and it is exact what i need. However when an user is filling out my form in our intranet the dayDifference field shows: NaN;NaN
I ma using infopath 2007.
Do you have an idea what i am doing wrong?
M.
markus lacher - January 7, 2009 at 4:02 pm |
When you do copy/paste XPath expressions (not values like 0;4096) into action vindow, make sure you’ve pressed fx button and then checked “Edt XPath(advanced)” checkbox.
alecpojidaev - January 7, 2009 at 7:16 pm |
Hi very thanks for this cool sharing. I follow the step but was encountered with the error: Some rules were not applied
AFter i click on the OK button.
day diff = 0.5;0.25
for Start Date=14-Jan-09, End Date=15-Jan-09
I though this should return me 1 day in different? Any thing wrong?
linjy - January 14, 2009 at 2:55 pm |
There is link in my post where you can download working form.
Looking at result it seems like your condition on MainRule is not working.
alecpojidaev - January 14, 2009 at 6:33 pm |
Issue is your finalize rule. Recreate it manually, and it will work correctly.
Yongil - November 7, 2011 at 5:03 pm |
Hi,
Is there anyway we can calulate months between two dates?
Please help if you know.
Thanks
IA
Imran - January 15, 2009 at 8:45 pm |
>Please help if you know.
Knowing is overrated. But I can help you even if I don’t initially know.
It’s somthing like (substring(date1,1,4) -substring(date2,1,4))*12 +substring(date1,6,2)-substring(date2,6,2)
alecpojidaev - January 15, 2009 at 10:05 pm |
Alecpojidaev thanks a lot!
linjy - January 16, 2009 at 2:40 am |
Thanks for your reply alecpojidaev.
Can you point me where should I have to put this code. I tried in rules, condition. I might doing somethign wrong if you can point me will be realy helpfull.
Thanks again
Imran
Imran - January 16, 2009 at 3:52 pm |
To: Imran
Actions inside rules are the right places.
You will need to replace date1 and date2 with the actual field names on your form as well.
alecpojidaev - January 16, 2009 at 4:11 pm |
Hi alecpojidaev,
I tried in it but no luck. If its possible can you send me the file if you test on your end.
Thanks again for you help
Imran
Imran - January 16, 2009 at 8:14 pm |
To: Imran
http://cid-e5a123fca40349ad.skydrive.live.com/self.aspx/.Public/monthCheck.xsn
alecpojidaev - January 16, 2009 at 8:46 pm |
Hi alecpojidaev,
is it possible to exclude weekends? I only want calculate workdays.
Thanks for help.
plimp
Plimp - January 21, 2009 at 1:39 pm |
To: Plimp
I’m not sure about that. It would be posible if we have know information about days of the weeks of both date picker controls.
alecpojidaev - January 21, 2009 at 2:14 pm |
Hi,
is it possible to get informations from datepickers?
When i have to calculate the date from picker 1 with 01/21/09 and date from picker 2 with 01/28/09 then i have 7 day´s between the picker. In this case it should strip 2 day´s. When i have 14 day´s it should strip 4 day´s.
Did this work with rules?
Plimp - January 21, 2009 at 2:26 pm |
To:Plimp
Ya thats how it should be calculated. But without knowing at least one day of the week at either one date picker you cant be sure how many weekends between 2 dates. the formula (date1-date2)*5/7 gives you the result with precision +/-2 working days.
alecpojidaev - January 21, 2009 at 2:56 pm |
Hi Alec,
I do have the same problem, when using your form it works 100% without the weekends but when I use mine it adds the weekends. Any thoughts, thanks
Hannu - December 20, 2010 at 1:39 pm
If you copied everything correctly the fact the form doesn’t work means someone did cursed your form and you need help of powerful magician to remove the curse. Sorry, can’t help you here.
alecpojidaev - December 20, 2010 at 2:52 pm
Thanks alecpojidaev.
It works like a charm. Only issue getting is when I entered 01/01/2009 to 31/12/2009 it calculates 11 months but if you see these are 12 months.
Do you have any solution?
Thanks
Imran
Imran - January 21, 2009 at 2:40 pm |
To:Imran
As for me 11 is correct. But if you want to feel free to add +1 to each formula.
alecpojidaev - January 21, 2009 at 2:45 pm |
Hi,
could it work when the format of the date pickers are “wed 01/21/09”.
If this would work, how?
Plimp - January 21, 2009 at 4:18 pm |
To:Plimp
I believe the internal representation will always be yyyy-mm-dd. The output format is a different story.
alecpojidaev - January 21, 2009 at 5:48 pm |
I am in the same boat as Imran, the weird thing is I can see how yours works, but I can’t get mine to work. Something to do with the set field to in the second rule on the date2 has a set fields value: y = concat(substring(., 1, 4), substring(../my:date2, 1, 4), substring(., 5, 2), substring(../my:date2, 5, 2))
I’m not sure how you are pointing to the field called ../my:y in your rule. how is that accomplished.
murphy tha dog - January 26, 2009 at 8:45 pm |
To:murphy tha dog
You can remove action where ..my:y is involved. It’s not used anymore (Sorry, hanging wires of fast developing).
alecpojidaev - January 26, 2009 at 9:34 pm |
But you do need both rules in both date fields right?
murphy tha dog - January 26, 2009 at 10:18 pm |
In that design yes. But you can reduse amount of actions to just 1 by transferreng action to a button and removing all actions from controls. In that case you will need to press button to perform calculation. The paths to the fields has to be updated respectfully.
alecpojidaev - January 26, 2009 at 10:27 pm |
I like how yours is automatic so I will keep it that way, but my result is NAN in the Nmonths field I can’t seem to reproduce the correct calc.
murphy tha dog - January 26, 2009 at 10:31 pm |
i did however get it to work in a new clean xsn file. so i will try again tomorrow. thanks
murphy tha dog - January 26, 2009 at 10:54 pm |
Can you post the latest XSN file to review how you accomplished removing the weekends. I have a form very similar to this and have yet to get this to work. Thanks
mrdesignit - February 3, 2009 at 3:54 pm |
I hadn’t accopmplished it. You can’t do it without knowing the day of the week on at least one datepicker. I don’t know how to get that info without using external data sources (i.e. sharepoint).
Update: Now I know how to do that. Still need to fight my laziness to finish the thing.
alecpojidaev - February 3, 2009 at 6:22 pm |
Is there a way to make is so that when you click on Monday and then on Friday and the difference will show 5 days. Right now I get 4 days.
mrdesignit - February 3, 2009 at 6:30 pm |
modify finalize rule as following:
substring-after(., “;”)+1
alecpojidaev - February 3, 2009 at 7:46 pm |
These are pretty good. :) Hope there are more coming.
Richard Harbridge - February 26, 2009 at 4:35 pm |
very very good arcticle, exactly matching for my application. But this is not supported with Infopath 2003. Is it possible to open in Infopath form 2003.
venkatesh - March 11, 2009 at 1:33 pm |
Sorry, IP 2007 only.
alecpojidaev - March 11, 2009 at 2:49 pm |
Is there an updated place to download the xsn files? They show that they are unavailable. thanks
Dennis - April 9, 2009 at 9:47 pm |
Sorry, looks like Microsoft constantly changing links to the files in SkyDrive. I replaced direct file downloads with links to the folders.
alecpojidaev - April 10, 2009 at 1:44 pm |
Hello. For some reason day difference returns 0;4096, any idea?
Hector Cuevas - April 15, 2009 at 4:38 am |
To: Hector Cuevas
Nope. No idea. I even don’t know have you created the form from scratch or you talking about form available for download.
alecpojidaev - April 15, 2009 at 5:09 am |
can you please publish the code for the daysOfTheWeek xml file used in the demonstration for showing day of the week in the data connection in the demo form?
I have an immediate need for this application. Thanks!
Jared
Jared Gollnitz - May 6, 2009 at 3:23 pm |
Sorry… I finally see where you used the xml data source file… still need the code for the xml file, if you can, please.
Thank you!
Jared
Jared Gollnitz - May 6, 2009 at 4:48 pm |
Whats the problem to extract secondary data source XML file from the template? Are you getting any errors when you doing
Manage Data connections -> Modify daysOfTheWeek -> Resource Files -> Export?
Or just save as Source Files.
alecpojidaev - May 6, 2009 at 5:44 pm |
Ah… thanks for the tip… I’ve never noticed the export feature before.
Thanks!
Jared Gollnitz - May 7, 2009 at 2:41 pm |
I’ve just downloaded the form you’ve created to calculate the day of the week and have been trying to put the formula’s from the downloaded form into my other one, i think i’ve got all the little bits and pieces required for it to work yet when i preview the page it only comes up with 0;4096. Is there anything commonly left out, or have you created a step by step in setting this up?
Thanks heaps for all your codeless solutions, they really help with taming Infopath 2007
TomMc - May 11, 2009 at 1:55 am |
Issue fixed, on my conditions i had is equal to “;” instead of contains.
Thanks Again.
TomMc - May 11, 2009 at 2:26 am |
Dear Alec,
Thank you for this nice tool…
Im new to the world of InfoPath and I have tried following your “step by step” above. However, I run into a small problem. No matter which dates i select in my date picker, the resault always end at 0;4096
What am I doing wrong?
Your help will be appriciated :-)
Hoff - May 19, 2009 at 12:01 pm |
Try to find difference between what you trying to do and the form available for download.
alecpojidaev - May 19, 2009 at 1:06 pm |
I did that… I copy/pasted every thing…
Hoff - May 19, 2009 at 1:17 pm |
Well… if you copy/pasted everything then we are witnesses of a miracle.
alecpojidaev - May 19, 2009 at 1:26 pm |
If I add a simple calculation field to your form, dayDifference * 3.33, I get an error message that reads ” A calculation in the form has not been completed. The number of calculations used for a single action exceeded the maximum limit.”
Any ideas
Rob - May 19, 2009 at 5:18 pm |
Thats how it suppoused to be. dayDifference is already almost at the limit of allowed amount of calculations. Adding one more field with calculation involved dayDifference stops things working right away. Same error because of the same reason you will see if you’ll try to mesure amount of days between dates with day difference more then 23 years on unchenged form.
alecpojidaev - May 19, 2009 at 5:42 pm |
Thanks for the response. Is there any way to include the diffDays field in a caluculation? Or, is the diffDays calc the only one I can have on the form?
Thanks again for your help.
Rob
Rob - May 19, 2009 at 5:59 pm |
If you need to do some after calculations use “finalize” rule action.
You can have as many calculating controls as you want on the form. The only limitations is the amount of automatic calculations after user action (16).
alecpojidaev - May 19, 2009 at 6:28 pm |
Thanks for the info. It works like a champ.
Rob
Rob - May 19, 2009 at 8:56 pm |
Great post this has been really useful. I’ve used your technique to work out the working days in a holiday request form but I have an issue if the begin date is a Sunday. The calculation comes back with decimal number eg 6.7142857. I’ve downloaded your example form and it does the same in there, everything else is fine. Any ideas what’s causing this.
Kev - June 1, 2009 at 12:22 pm |
Fixed and uploaded new form.
alecpojidaev - June 1, 2009 at 1:36 pm |
Thanks for uploading your form, there are helpful. I’ve got a problem when i try to do the workdayscalculation form. When i have to make the last rule for the workday, i can’t do
“compte(hday[traduire(BeginDate; “-“; “”) = traduire(@hdayn; “-“; “”)]) “,
and error occured there is the error :
“xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/my:BeginDate » ne pointe pas vers le chemin d’accès valide d’un champ ou d’un groupe.”
It’s in french but it’s saying that doesn’t point to a validated path of a field or a group, i don’t know what i do wrong, everything else seems ok, thanks in advance
Ced
Ced - June 8, 2009 at 12:30 pm |
Have you included XML file with French holydays in your form? You can extract copy of it using instructions in my previous responses.
alecpojidaev - June 8, 2009 at 1:09 pm |
I included the same XML file you used to my form, i extract it to you form …. something strange, if in your form i change compte(hday[traduire(BeginDate; “-“; “”) to compte(hday[traduire(@name; “-“; “”)
it’s working to, but in my form it still calculte the number of days but it doesn’t take care about workday or not
thanks in advance
Ced
Ced - June 8, 2009 at 1:51 pm |
Well… at that point it’s getting kind of hard to say what is wrong. Looks like all your troubles are related to secondary data source. So I would sugest maybe better solution in your situation would be using InfopPath UI for manualy picking up fields from secondary datasource.
alecpojidaev - June 8, 2009 at 2:03 pm |
I’m doing this aswell, i always use InfoPath UI for avoiding synthax error, thanks anyways i’ll try to find out where is the problem.
Ced - June 8, 2009 at 2:10 pm |
Also, why i can’t remake your form ? it’s the same error as me when i try to do the
“compte(hday[traduire(BeginDate; “-“; “”) = traduire(@hdayn; “-“; “”)]) “,
i pick field from infopath ui etc … but it’s still getting me the same error, i think it’s because in Holidays/hday/ there is no BeginDate, so it can’t work, i really don’t know how you do this.
Ced - June 8, 2009 at 2:14 pm |
At that point you have to use absolute path not relative. Thats why it’s always better to copy when ” edit avanced” check box is checked.
In my advanced view BeginDate looks like:
xdXDocument:get-DOM()/my:myFields/my:BeginDate
and because of error yours path structure is different probably.
alecpojidaev - June 8, 2009 at 2:50 pm |
Thanks a lot, i can compile my form now, but there is another problem. Now the weekend are determinate by the startdate,
example : if start date is a monday -> weekend = sunday and monday
example2 : if startdate is a sunday -> weekend = saturday and sunday
Any thought about this please ?
Ced
Ced - June 8, 2009 at 3:11 pm |
Sorry I can’t understand what your problem is.
alecpojidaev - June 8, 2009 at 3:26 pm |
well i have 2 datepicker, one for the begindate and one for the enddate, in workdaycalculation you don’t count weekends (saturday and sunday) into date calculation.
My problem is the weekends aren’t saturday and sunday, the 2 days which are considered as weekend are determinated by the datepicker of begindate. this means if i choose today ( 8 june ) as begindate, the calcul will be like this :
enddate 9 june -> workday = 2
enddate 10 june -> workday = 3
enddate 11 june -> workday = 4
enddate 12 june -> workday = 5
enddate 13 june -> workday = 6 saturday it should be 5 here
enddate 14 june -> workday = 6 sunday it should be 5 here
enddate 15 june – > workday = 6
i wish you understand my problem,
thanks in advance
Ced
Ced - June 8, 2009 at 3:53 pm |
I can’t reproduce that. My form shows
enddate 13 june -> workday = 5 as it should be.
alecpojidaev - June 8, 2009 at 4:05 pm |
I really don’t know why my form does this error, i’ve exactly the same codeless programming of your form but it’s doing the thing i described in my previous post, anyways if you have any ideas of what can be wrong for doing this error, thanks to share me.
Ced
Ced - June 8, 2009 at 4:48 pm |
Ok i resolve this problem, now the form calculate workdays like your. But now i’ve a new problem … my form doesn’t count the holidays.
Basically, if in your form i extract the xml file and i rename this. when i create a new connection to the new xml file and change BegDateWN condition to rule 1 xdXDocument:GetDOM(“daysOfTheWeek”) to xdXDocument:GetDOM(“newnameofXML”), and i change the path of the basepoint of this rule to the new path basepoint. Then i also change in rule 2 of workdays the path to hday and @hdayn to the new path.
The result is it don’t count the holidays of the XML file, what I am doing wrong ?
thanks in advance
Ced - June 9, 2009 at 3:23 pm |
Try to troubleshoot it by extacting the holyday part of the formula into an expressionbox and see what result you are getting
alecpojidaev - June 9, 2009 at 4:19 pm
hello
i ve jus copy pasted this from ur form.
when i add a rule for the workday textbox i get the error as
Node-test expected.
(substring-before(/my:myFields/my:txtHiddendayscount, “;”) + /my:myFields/my:FromDateValue) mod 7 – ((substring-before(/my:myFields/my:txtHiddendayscount, “;”) + /my:myFields/my:FromDateValue) mod 7 = 6) + 1 – /my:myFields/–>my:FromDateValue- <–(/my:myFields/my:FromDateValue= 0) + (substring-before(/my:myFields/my:txtHiddendayscount, ";") – ((substring-before(/my:myFields/my:txtHiddendayscount, ";") + /my:myFields/my:FromDateValue) mod 7 – /my:myFields/my:FromDateValue)) * 5 div 7 – count(/somestuff/holydays/hday[translate(/my:myFields/my:datLeaveFrom, "-", "") = translate(/somestuff/holydays/hday/@hdayn, “-“, “”)])
Prasanna - June 10, 2009 at 5:46 am |
I found my error, it’s when i’ve to do
compte(hday[traduire(BeginDate; “-“; “”) = traduire(@hdayn; “-“; “”)])
for doing this i enter
compte(xdXDocument:getDOM(“daysOfTheWeek”)/somestuff/holydays/hday[traduire(/my:myFields/BeginDate; “-“; “”) = traduire(xdXDocument:getDOM(“daysOfTheWeek”)/somestuff/holydays/hday/@hdayn; “-“; “”)])
there is no error for the form but when i enter this it doesn’t count the holidays, what is your method for enter this please ?
Ced - June 10, 2009 at 11:04 am |
Just in the case you don’t understand my problem, delete compte(hday[traduire(BeginDate; “-“; “”) = traduire(@hdayn; “-“; “”)])
and explain how do you do for rewrite this,
thanks
Ced
Ced - June 10, 2009 at 11:58 am |
To: Ced and Prasanna
For some reason the formula in my form in that fragment looks a bit different:
count(hday[translate(BeginDate, “-“, “”) <= translate(@hdayn, "-", "") and translate(EndDate, “-“, “”) >= translate(@hdayn, “-“, “”)])
Same with XPath:
count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, “-“, “”) <= translate(@hdayn, "-", "") and translate(xdXDocument:get-DOM()/my:myFields/my:EndDate, “-“, “”) >= translate(@hdayn, “-“, “”)])
alecpojidaev - June 10, 2009 at 7:05 pm |
Ok Alec it’s working now, thanks a lot
Ced
Ced - June 11, 2009 at 7:03 am
Hello its working.thanks a lot and i ve one more doubt.the form is all about excluding the weekends.but dont want to exclude first and last saturday of the month.can u help me out with this.
thanks in advance
Prasanna - June 15, 2009 at 4:41 am |
It’s definatly possible. However I’m not skilled enouth to help you in any reasonable amount of time.
alecpojidaev - June 15, 2009 at 1:11 pm |
Hey–can you adapt this so that it returns the number of specific days in the date range? (i.e., the number on Mondays between the start time and end time?) Or even can you choose Monday as a options and only populate a drop down that shows dates in the specified range that are Mondays?
I tried, but since I’m not familiar enough with xpath functions, I couldn’t figure it out.
Thanks!
Cassie - June 15, 2009 at 7:41 pm |
It’s definatly possible. However I’m not skilled enouth to help you in any reasonable amount of time.
alecpojidaev - June 15, 2009 at 7:55 pm |
I was wondering what changes I need to make to use this in a repeating table?
David - July 15, 2009 at 7:18 pm |
Edit: Check my new DateCheck form with repeating table primer.
In repeating table it shouldnt be too different. Just use
preceding-sibling
.
following-sibling
functions instead of hardcoded field names. You also have to avoid linked calculations (i.e recalculating all fields in repeating table on some event) because you can easily get over the limit of allowed calculations.
alecpojidaev - July 15, 2009 at 8:37 pm |
Hey there!
The “What Day is Today/Anyday” code works but I have the fields on a repeating table and when I add a new row, the “day of the week” field is left blank. Is there a way to make the code operational on the added rows?
Thanks!!!
newbiekelidelic - July 21, 2009 at 2:48 am
The main example DateCheck.xsn has XPath adopted to the repeating table. You will have to combine both XPaths from 2 forms to get what you need.
alecpojidaev - July 21, 2009 at 1:06 pm
Hi – thank you for the code – the concept is great, especially doing it without code. When I followed your instructions and previewed the form it looks/works great. However, when I publish the form to a form library in Sharepoint and launch the form via browser – I get the following error. Any ideas?
“An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit. The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute. “
Zach - August 7, 2009 at 3:37 pm |
It would be nice to know what form are you talking about. That post has 4 of them.
alecpojidaev - August 7, 2009 at 6:37 pm |
Hi sorry – the question was in respective to counting the number of days between two date pickers (inclusive of weekends). Specifically the instructions abover about startDate rule, endDate rule, and dayDifference rules. Like i mentioned in my original question – the form works perfectly until I publish the form to a browser-enabled forms library in Sharepoint. When I launch the form from that location, I get the error mentioned above.
Zach - August 10, 2009 at 3:35 pm
Edit: Unsupported function problem fixed.
alecpojidaev - August 10, 2009 at 3:52 pm
Great and extremely valuable post, i’m currently working on a Sharepoint implementation of a Leave Management System and we are thinking about using InfoPath forms as forms for leave application, the business days calculation form looks very useful in my case. My problem is that it works well in the infopath client and preview mode but when i publish it to a sharepoint forms library, i get the error:
“An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit. The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute. ”
Any ideas why? I have by the way changed the data source to xml file stored on disk by the same name.
uzalish - August 11, 2010 at 5:07 pm
Try to retrieve the data of external xml file on clean without any functionality web enabled form. If you won’t be successful learn how and where to store you xml data.
alecpojidaev - August 11, 2010 at 5:42 pm
I’m interested in the “TodayIs” field and funciton alone where it auto sets today as it’s value then it formats itself as the day of the week. I downloaded the “WhatDayIsToday” template and see how with one resource file, and then 2 rules with conditions this deal is done. I’ve created the very same field named “TodayIs”, I uploaded the resource file on my form and added it as a dataconnection, I then using the fx button have copied the formula (while having the edit xpath pressed) to simply recreate via copy paste the same funtion but mine never works.
The Rule 1 if left alone on mine gives me no value, and if I enter into the field “;” I get “nan,nan”. The second rule after copy/paste always is upset about the formula, the very same one working right in my face… AhHH..lol…. What step might I be missing?
I make a field called “TodayIs”, I extract then upload the resource file, add it as datasource, I copy/paste the conditions and rules using the xpath advanced while copy and while paste and no dice.
Thanks for you help on this.
Gil - August 10, 2009 at 3:01 pm |
It’s hard to tell whats wrong with your form. Probably data types are mismatched. Make sure all field types are the same as in my form.
Also may be you could try to modify the form from the other end? Use my form as starting point then add the logic you need. I’ve tested that form in browser enabled forms it works just perfect.
alecpojidaev - August 10, 2009 at 3:28 pm |
Hello again.. :)
We’ll I’m trouble shooting and narrow it down and left my text field “TodayIs” with just the first rules copied expression “cut/paste” and the if “TodayIs” contains “;”, then in testing I had the aciton set my date field to “1”. This did not work making me see that the expression when copied and pasted does not work for me. I do have the resource file as a secondary data connection to recieve also.
So, regarding the query:
xdDate:AddDays(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/basepoint, substring-before(., “;”)) != xdDate:Today()
I’m just choosing for my rule’s condition the option of “The Expression” and pasting in the expression, the creating the simple if the field includes the “;”, and it does not work for me. What can I do with this expression to duplicate it on my form please?
Gil - August 10, 2009 at 5:44 pm
Make sure your secondary datasource has the same name (case sensitive).
alecpojidaev - August 10, 2009 at 6:29 pm
When i enter start date as Friday and End date as Monday then my field only calculates 2 days of my working day. Dont calculates the sat and sun. how can i perform this.. thnX Advance
Saran - October 23, 2009 at 11:41 am |
Thats how working days are calculated. Monday and Friday are 2 working days so they have to be counted.
alecpojidaev - October 23, 2009 at 2:17 pm |
Dear Alec,
just wanted to know from where did u get this info regarding 16 iterations,i mean that the maximum iterations possible in an INfopath form is 16 and 17 is infinity for infopath form as u’ve said.How relevant is this info.I’m checkng that because except your blog i never get a chance to see any such comment made by anyone or accepted by microsoft themselves.Any link or help would be of a great help,i’m caught in a issue that involves help from Microsoft and i cant recah them without checking the relevancy of this message.
Sorry to bother you
Thanks in Advance
Vishal - November 26, 2009 at 7:37 am |
Sorry, I don’t remmeber where I found that info. In Infopath 2010 you will be able to change that limit (also don’t remember where I’ve read that)
alecpojidaev - November 26, 2009 at 6:45 pm |
Hi Alec,
just wanted to know,from where u get this information that there are only 16 iterations possible in an infopath form,and can u be specific in terms of telling me,what exactly this iteration means.Does that mean if i have a repeating control and i’m iterating it’s nodes or something else.It is kind of urgent.As it involves me raising an issue with Microsoft for Infopath forms.So please if u can provide me some links that can prove that this statement is genuine,it will be very helpful for me.
Thanks in Advance
Sorry to bother you..
Vishal - November 26, 2009 at 9:36 am |
More presisely 16 is not a iteration limit. The limit is a number of assignements (not just any assignements but assignements actually changing the value of a field) to the infopath fields after an user action.
alecpojidaev - November 26, 2009 at 6:56 pm |
i have downloaded the form on this website which shows calculation of workdays between two dates selected. i tried to copy the same rules on my form and preview but it didn’t work. The mome nt i select the start date i am getting the error pasted below:
A call to XDocument.GetDOM failed. The DataObject “daysOfTheWeek” does not exist.
Error occurred during a call to property or method ‘GetDOM’.
Kindly tellme what am i doing wrong here or if i have missed any step to be followed.
Thanks in Advance….!
Arti - December 4, 2009 at 5:38 am |
There is secondary datasource in my form. You need to create same datastructure as in my form.
alecpojidaev - December 4, 2009 at 2:41 pm |
This is all very useful, but I’m using the reply on this discussion to ask a bit of a different question. I’m a non-code writing developer (I use that term losely). Anyway, I am trying to develop a number of InfoPath forms to be used on SharePoint. I would like managers to be able to complete a form for an employee and have Profile Data autofil by using the employee’s account name not the “user”. The employee would be selected from a list or search at the top of the form. Hope this makes sense. I am a bit frustrated on how to obtain this kind of help from Microsoft. Everything is so wizard driven with no explanation as to what you are really doing.
Scott - December 17, 2009 at 7:23 pm |
Thats a pretty standard task. In this post:
https://alecpojidaev.wordpress.com/2008/12/16/infopath-codeless-programming-walkthrough/
you can find all elements you need to achieve your goal. For more info just do search “Contact Selector” + userprofilebyname. You will find plenty of quite useful resources.
alecpojidaev - December 17, 2009 at 7:43 pm |
I am trying to get the workdaysform to work and I have the same kind of problem as dev. But when I aplly the 2nd rule to my BegDateWN I get this message (in danish) “xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/my:BeginDate” refererer ikke til en gyldig sti til placeringen af et felt eller en gruppe
But it says that the path to BeginDate is not a valid reference. I have tried to pick out the BeginDate, so I can see that it is actually there but why does it then say that it is unvalide?
peito - January 8, 2010 at 4:01 pm |
What else you were expecting from Infopath? It’s NOT a valid reference.
alecpojidaev - January 8, 2010 at 4:10 pm |
Can You explain to me, why it is not or even better what I should do?
peito - January 8, 2010 at 5:22 pm
First of all I don’t understand why are you trying to make work days form work? It’s working already.
alecpojidaev - January 8, 2010 at 6:50 pm
I am trying to integrate it into a form that I already have, so I wanted to copy it rule by rule to my own form.
peito - January 11, 2010 at 1:48 pm
BeginDate field is in main datasource. But your path :
(“daysOfTheWeek”)/somestuff/holydays/my:BeginDate
is trying to access BeginDate in secondary datasource (in XML file)
alecpojidaev - January 11, 2010 at 2:23 pm
I am trying to calculate time in InfoPath. I would like to be able to total the amount of hours/minutes as well as average the hours/minutes reported for each day. Is this possible? I would really appreciate your help.
Christi - January 26, 2010 at 7:39 pm |
It’s definitly possible. Don’t hesitate and use search when you really need to :) http://www.bizsupportonline.net/infopath2003/time-calculations-infopath-using-rules.htm
alecpojidaev - January 26, 2010 at 7:45 pm |
Hai..
I downloaded ur form its working.
But when am creating new form its not working it giving error.
“Function ‘AddDays’ did not return a value, or it returned a value that cannot be converted to an XSL data type.”
I am using infopath 2010
Please help me.
Thanks in advance
iqbalkmk
iqbalkmk - May 2, 2010 at 9:46 am |
IP 2010 is not an official release yet. So i’m not going to investigate what is going on here. I would recommend to seek some help on prerelease Microsoft Office forums.
alecpojidaev - May 3, 2010 at 1:26 pm |
I downloaded your form, and its working fine.
However, when I’m creating a new form its not working. I get the error. “Function ‘AddDays’ did not return a value, or it returned a value that cannot be converted to an XSL data type.”
I am using infopath 2007
Help!! Thanks in advance
Andy
andynicholls - May 10, 2010 at 4:10 pm |
From my expirience it’s rare Infopath glitch related to AddDays function. Try to recreate formula or ad/remove spaces.
alecpojidaev - May 10, 2010 at 6:07 pm |
Hi Alec.
I’ve finally managed to get my form working in IP but now that I’m publishing it to my forms library I’m getting an error…
“There was a filure loading WorkDayCalc.dll. Details: The specified user does not have a valid profile. Unable to load ‘WorkDaysCals, Version=1.0.3793.17538, Culture=neutral, PublicKeyToken=f8a230bbef5ac874’..
Can you help with this?
Have you come across it before?
N03L - May 21, 2010 at 10:03 am |
I don’t think that message is specific to my form. Have you been able to publish just empty form without any modifications?
alecpojidaev - May 21, 2010 at 1:14 pm |
Hi Alec,
Thanks so much for this solution it’s been a huge help. I do have one question although it’s not a major issue. I have been able to get all the rules to work and the work days calculates correctly however, if I change the “BeginDate” field to a date in the past I get an error message that “A calculation in the form has not completed. Number of calculations used exceeded the maximum limit” – any idea what might be causing this? The Work Days still calculate correctly but I’m worried this error popping up will cause confusion with users of the form.
Ben - June 2, 2010 at 3:00 pm |
Sorry, I’m not able to reproduce that error on the form available for download. Probably you missed to put some rules in your form.
alecpojidaev - June 2, 2010 at 5:11 pm |
i was doing the tutorial for the difference date and i got the following error message when I try to add formula for the Main rule in Add difference … please let me know if you have a clue thx
Unexpected character.
concat(substring-before(., “;”) + substring-after(., “;”) * ((translate(xdDate:AddDays(../my:startDate, substring-before(., “;”)), “-“, “”) –<– 1), ";", substring-after(., ";") div 2)
Clueless - July 23, 2010 at 2:45 pm |
Try to copy original formula more carefully. If you compare your formula and original the difference is obvious.
alecpojidaev - July 23, 2010 at 3:19 pm |
I did couple times but it’s returning this error modifying the code do you have another idea what could be wrong
clueless - July 23, 2010 at 5:40 pm |
Try to copy formula not in one piece but in 2 or more manageble parts.
alecpojidaev - July 23, 2010 at 5:59 pm |
Hi Alec
Great information thank-you – I am new to InfoPath and when I try to download your sample I cannot get it to open in design view – is this an option and if so how do I do it. I am using the 2010 version?
Thanks RJ
RJD22 - July 27, 2010 at 2:23 pm |
You got to save it first then right click on it and choose “Design”
alecpojidaev - July 27, 2010 at 2:31 pm |
Hi Alec – no design option probably something new in 2010 and/or Windows 7. Lots of other “Open with” options.
Thanks RJ
RJD22 - July 27, 2010 at 5:02 pm |
when i want to use this form, with data from a sharepoint list not with the xml.
can the date format from sharepoint give an issiue?
kimvlas - August 17, 2010 at 1:33 pm |
No. As long as you will be able to understand how to modify the formula.
alecpojidaev - August 17, 2010 at 2:24 pm |
i think i understand very good.
i have changed the second rule in the beginDateWN en endDateWN the count function with my own date.
and my holidays that stands in a list wont be counted down from the total.
this is the ownly rule i must change i think i’m i wright?
kimvlas - August 17, 2010 at 2:55 pm |
Make sure that date format in your list is yyyy-mm-dd. And if it’s not convert to the expected format.
alecpojidaev - August 17, 2010 at 5:54 pm |
when i check my listdate the date that stands there is yyy-mm-dd uu-mm-ss, in the sharepoint list it stand like yyyy-mm-dd.
this was my first mistake i thouht.
do within my data connection i have placed a rule on the date like this: concat(substring(date,1,4),”-“,substring(6,2),”-“,substring(9,2))
wo now i think that my date will be proced properly.
or i’m i wrong?
kimvlas - August 17, 2010 at 8:54 pm |
I would use translate(substring-before(../my:endDate,” “), “-“, “”) that formula removes “uu-mm-ss” part
alecpojidaev - August 18, 2010 at 1:19 pm |
it has been a while since i continued working on it.
when i recieve my list of data from sharepoint.
how can i iterate over every item in this list?
must i write this in code or can this be done with rules?
and how?
kimvlas - August 26, 2010 at 7:33 am
hi, i’m trying to use the workdaysCalculation
when i use the action:
concat(substring-before(., “;”) + substring-after(., “;”) * ((translate(addDays(basepoint, substring-before(., “;”)), “-“, “”) < translate(@VM_DATUM_VAN, "-", "")) * 2 – 1), ";", substring-after(., ";") / 2)
the BegDateWN i got this error:
Node-test expected.
concat(substring-before(., ";") + substring-after(., ";") * ((translate(addDays(basepoint, substring-before(., ";")), "-", "") 2<–)
can someone help me with this?
jenn - October 7, 2010 at 7:55 pm |
replace
/ 2
with
div 2
alecpojidaev - October 7, 2010 at 8:43 pm |
ok, thnx for your reply.
but now it gives me an error at the addays
jenn - October 8, 2010 at 12:58 pm
What error?
alecpojidaev - October 8, 2010 at 1:56 pm
this is the formula i’m using
concat(substring-before(., “;”) + substring-after(., “:”) * ((translate(addDays(basepoint, substring-before(., “;”)), “-“, “”) < translate(@VM_DATUM_VAN, "-", "")) * 2 – 1), ";", substring-after(., ";") div 2)
jenn - October 8, 2010 at 1:34 pm
this is the error:
Function ‘AddDays’ did not return a value, or it returned a value that cannot be converted to an XSL data type.
jenn - October 8, 2010 at 2:17 pm
It’s AddDays function bug. I found no solution to it exept retype the whole formula (possibly not just one time).
alecpojidaev - October 8, 2010 at 2:22 pm
ok thnx
jenn - October 8, 2010 at 2:25 pm
Hi thanks for the post
I am getting the error below when I publish the form or if I use it in customize form in SharePoint 2010. It would be great if you could tell me the reason?
An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit.
The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute.
nthrdy - October 20, 2010 at 5:25 pm |
The unchanged forms should work just perfect in IP2010. The “exceeding maximum limit” problem appears whenever conditions on rules are missed or incorrect.
alecpojidaev - October 20, 2010 at 6:17 pm |
Thanks for the quick reply. But my current scenario is if I create a custom list in SharePoint 2010, and click on Customize form under the List, and use the same fields and rules correctly and publish, then it won’t work.
When I try to add a new item in the list it throws the following error
Default values, rules or code may be running in an infinite loop. To prevent this, ensure that the default value, rule action or event handler does not update the data which causes the same rule action or event handler to execute.
But if I create an InfoPath form based on SharePoint form library template and publish to a form library then it works fine.
Why is it not working for Custom List?
nthrdy - October 20, 2010 at 7:29 pm |
There is no difference in behaivior regular forms and customizable list item for. Even if there is some difference you have to contact MS support and report it as bug. But most probably you just missed some condition/formula.
alecpojidaev - October 20, 2010 at 7:37 pm |
I appreciate your help, I tried creating the form again in the Custom List, that error repeats. There must be some bug with it.
I have one question about the formula that we are using here. Could you clarify my doubt
for the first iteration,
StartDate = 2010-10-19
EndDate = 2010-10-22
concat(0 + 4096 * ((20101019 < 20101022) * 2 – 1), ";", 4096 / 2)
gives -4096;2048
My understanding is, it calls recursively and comes to the result 3 at some point. My question here is
1. How can the Concat() function gives a result with out ';'
2. In the above Concat expression, what is the value of (20101019 < 20101022). It should be a boolean value right? Thanks in advance.
nthrdy - October 20, 2010 at 8:35 pm |
1. It’s done by the second rule is called “finalize”
2. If boolean expression enters in arithmetical one it converts on a fly to the 0 or 1 depending on true/false
alecpojidaev - October 20, 2010 at 8:45 pm |
I understand that the Main Rule condition will become false eventually and second rule gets triggered. How will the Main rule condition becomes false? Could you explain the algorithm behind? I tried to calculate the formula in the rule it seems like it would never violate the condition on Main rule. Please correct me if I am wrong.
nthrdy - October 21, 2010 at 8:17 pm
Look at the condition:
xdDate:AddDays(../my:startDate, substring-before(., “;”)) != ../my:endDate and contains(., “;”)
If both start date and enddate are the same the rule wont be triggered at all – AddDays function will add 0 to the begin date so the output of the AddDays function will be equal to the endDate and because of that the condition will fail. If the dates are different the formula ensures that changing the dayDifference field will eventually yelds the figure adding which to the statrtDate will produce result exactly equal to the endDate and this will make condition to fail.
alecpojidaev - October 21, 2010 at 8:36 pm
First of all, sorry for bothering you much. You said, if the dates are different the formula ensures that changing the dayDifference field will eventually yields the figure adding which to the statrtDate will produce result exactly.
My question was how will it eventually yields to the result?
If I calculate the formula in the rule the sequence goes as below
-4096;2048
-6144;1024
-7168;512
-7680;256
-7936;128
-8064;64
-8128;32
-8160;16
-8176;8
-8184;4
-8188;2
-8190;1
-8191;0.5
-8191.5;0.25
-8191.75;0.125
-8191.875;0.0625
-8191.9375;0.03125
-8191.96875;0.015625
-8191.984375;0.0078125
-8191.9921875;0.00390625
-8191.99609375;0.001953125……..
This sequence does not seem to give the result.
Thank you so much for all your replies.
nthrdy - October 21, 2010 at 11:49 pm |
Your very first iteration is incorrect.
Copy this expression to the Expression box:
4096 * ((20101019 < 20101022) * 2 -1)
it will produce 4096 not -4096 as in your sequence.
The second iteration would be
2048 * ((20220105 < 20101022) * 2 -1)
it will result to the -2048
alecpojidaev - October 22, 2010 at 2:18 pm |
Is there a codeless way to add only workdays and exclude holidays from the total. I need to enter a date and add 29 business days to that date to produce a date in the future. The 29 business days would exclude holidays and weekends. Thank you for any assistance you can provide.
Ernest - October 25, 2010 at 2:06 pm |
Yes it’s possible. Although I never done it codeless in InfoPath form. The algorithm is described here:
alecpojidaev - October 25, 2010 at 2:14 pm |
I adjusted this to work with DateTime fields by adding substring-before(datetimefield, “T”) in all references to date fields. Also dayDifference can be an Integer field and it still works.
Kevin - October 25, 2010 at 9:44 pm |
Kevin, can you post the code? Can’t quite seem to figure out where the references go.
80mm - May 2, 2011 at 2:49 am |
Hi, thnx for this good example!
I have only one question, if I want to add 1 day to the daydifference, what formula can I use in the Finalize rule, do you have an example?
Thnx in advance!
kind regards,
Sunil
Sunil - November 4, 2010 at 3:24 pm |
+1 at the end
alecpojidaev - November 4, 2010 at 3:34 pm |
Hi Alec,
i have been trying to use your workdays calculation.
but in my case i want to use it in a repeating section.
so when the user load the form his dates he once have added will load. and he can add a new date. but whenever he add a new date, it gives me this error:
An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit.
The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute.
if i use the workdays calculation not in a repeating section it works fine.
how can i fix this so it works in a repeating section?
thnx
jenn - November 9, 2010 at 1:55 pm |
In the example form there is a repeating section that calculates date difference. Replace date difference calculation with work days calculation.
alecpojidaev - November 9, 2010 at 2:12 pm |
Sorry, but i dont understand what you are saying.
i am using the work days calculation (WorkDay’sCalc)example form
jenn - November 9, 2010 at 2:24 pm
ok, i think i know what you mean.
but can you explain to me. how i replace the date difference calculation with workdays cal?
jenn - November 9, 2010 at 2:31 pm
You have to analyse in what way day difference XPath in non repeating section is different from the same XPath in repeating part. Then apply this pattern in your form. I will add that code to the form in 2-3 days (when I’ll have time for that) in case if this task is too complex for you.
alecpojidaev - November 9, 2010 at 2:55 pm
ok, many thnx for your reply
jenn - November 9, 2010 at 3:01 pm
Hi ASlec, first of all great blog ypu have.Second i have a issue too it is posible to have this days calculation on Infopath 2003 ? nd how i had search on www but still no luck so far for asolution for 2003 Infopath.Thanks
Paul - November 21, 2010 at 1:42 am |
Sorry IP 2007 only.
alecpojidaev - November 22, 2010 at 2:22 pm |
Здравствуйте!
Подскажите пожалуйста, а русскоязычного описания данного вопроса у Вас нет? :)
Для меня это очень важно. Буду очень признателен! спс
HardHardy - November 23, 2010 at 10:56 am |
А как же! Вот: http://translate.google.com/translate?js=n&prev=_t&hl=en&ie=UTF-8&layout=2&eotf=1&sl=auto&tl=ru&u=http%3A%2F%2Falecpojidaev.wordpress.com%2F2008%2F12%2F30%2Finfopath-codeless-programming-walkthrough-2%2F
alecpojidaev - November 23, 2010 at 2:13 pm |
Hello!
Tell me how to do sorting in ascending order in a repeating table on a particular field?
Thank you:)
Artem - December 7, 2010 at 1:56 pm |
As of my knowlidge this is impossible in InfoPath 2007 without writing some C# code. Not that sure about 2010 version.
alecpojidaev - December 7, 2010 at 2:04 pm |
Hello!
Prompt Please, I form a query that displays data in a repeating table, I need to sort ascending line at the specified column, how to do this?
Artem - December 8, 2010 at 6:21 am |
It’s impossible without writing some C# code in IP2007. I’m not sure but it might be one of IP2010 features.
alecpojidaev - December 8, 2010 at 1:24 pm |
Another question. It is possible to build queries on a single date from the calendar. Is there any possibility to query from the database by month, or set a date range?
Artem - December 8, 2010 at 8:36 am |
Сдраствуйте увожаемий,
помогите пожалуста вставит филтр в “Work days calculation” для не полных рабочих дней, таких как новий Год например. Надо чтобы шеталосъ как половина виходного.
За ранее благодарю.
Niru - December 15, 2010 at 3:09 pm |
Добавте дополнительное поле halfday в XML файл к holydays:
hday name=”Holyday1″ hdayn=”2009-05-25″ halfday=”1″/
модифицируйте эту часть формулы:
count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, “-“, “”) = translate(@hdayn, “-“, “”)])
чтобы выглядела так:
count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, “-“, “”) = translate(@hdayn, “-“, “”) and (@halfday=”1″)]) – 0.5*count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, “-“, “”) = translate(@hdayn, “-“, “”) and (@halfday=”0.5″)])
alecpojidaev - December 15, 2010 at 3:53 pm |
Добрий день,
спасибо за помошь, но чегото я делаю не правилно. Если ето возножно поправте пожалуста.
Так я изменил XML
hday name=”Holyday1″ hdayn=”2009-05-25″ /
hday name=”Holiday2″ hdayn=”2010-12-24″ halfday=”1″/
но как нужно изменить формулу и какую из них, я незнаю.
Я так думаю что нужно в “beginDayWE” и “endDayWE” во второй формуле менят, но у меня невыходит.
Вот формула из “BeginDay”:
(substring-before(../my:bisWE, “;”) – substring-before(., “;”) + substring-before(., “;”) mod 7 – substring-before(../my:bisWE, “;”) mod 7) * 5 div 7 + substring-before(../my:bisWE, “;”) mod 7 – substring-before(., “;”) mod 7 – (substring-before(../my:bisWE, “;”) mod 7 = 6) – (substring-before(., “;”) mod 7 = 0) + 1 – count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:meineFelder/my:von, “-“, “”) = translate(@hdayn, “-“, “”)])
что здесь нужно поменять?
Прошу извенить меня за мою тупость, так как я в етом деле полний чайник. Но формуларчик очень нужен.
Спасибо.
Niru - December 16, 2010 at 7:53 am |
Извините, потерялось часть XML при копировании:
hday name=”Holyday1″ hdayn=”2009-05-25″ halfday=”0.5″/ (половинный день)
hday name=”Holiday2″ hdayn=”2010-12-24″ halfday=”1″/ (полный день)
Менять нужно вот эту часть:
count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:meineFelder/my:von, “-“, “”) = translate(@hdayn, “-“, “”)])
а как – посмотрите в предыдущем посте.
alecpojidaev - December 16, 2010 at 2:20 pm |
Добрий день,
еше раз спасибо за помошь я всё сделал так как вы сказали, но чтото до сих пор работает неправилно. Если я выбираю с 24.12.2010 по 24.12.2010 тоесть всего один день то формула вышитывает мне правилно 0,5 дней но если например с 20.12.2010 по 27.12.2010 то я получаю 5 полних дней в ответ хотя должно быть 4,5 дней. Что мне нужно потправить.
Спасибо
Niru - December 17, 2010 at 7:42 am
Это потому что WordPress убирает символы в формуле :( , но попробую исхитриться:
count(xdXDocument:GetDOM("daysOfTheWeek")/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, "-", "") <= translate(@hdayn, "-", "") and translate(xdXDocument:get-DOM()/my:myFields/my:EndDate, "-", "") >= translate(@hdayn, "-", "") and (@halfday="1")])-
0.5*count(xdXDocument:GetDOM("daysOfTheWeek")/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, "-", "") <= translate(@hdayn, "-", "") and translate(xdXDocument:get-DOM()/my:myFields/my:EndDate, "-", "") >= translate(@hdayn, "-", "") and (@halfday="0.5")])
alecpojidaev - December 17, 2010 at 6:58 pm
ок спасиба,
попробую в понеделник и отпешусь.
Niru - December 16, 2010 at 2:25 pm |
hi
i am using this concept in my infopath i am getting below warning.
There has been an error while processing the form.
Narayana - December 16, 2010 at 3:04 pm |
Thats weird. The example forms are using this concept and showing no warnings at all. That makes me think that the concept itself is unrelated to your problem.
alecpojidaev - December 16, 2010 at 3:09 pm |
спасибо болшое!
вы гений!!! всё работает так как я себе предстовлял.
Niru - December 20, 2010 at 11:02 am |
I am using your calculations for a timecard and your example is great, my only trouble is recreating your “hours” repeating table. When I add yours to a form it repeats to 23, mine just stays at 00
Debi - December 27, 2010 at 10:06 pm |
Have you added default values to the repeating field “hours”? I’m using this field as a source in my dropdowns. Here is search string that might be useful:
infopath default value repeating
alecpojidaev - December 27, 2010 at 10:15 pm |
The default value in your “hours” fext field is just set to 00
Debi - December 27, 2010 at 10:17 pm
I know it’s tricky to add multiple default values to just one field. Thats why I recommend to do some search with the string from my previous post.
alecpojidaev - December 27, 2010 at 10:24 pm
My Time fields are set as dropdowns to “get choices from fields in this form” and point to the “hours” Xpath like yours
Debi - December 27, 2010 at 10:18 pm |
So you do not know how to recreate the “hours” repeating table from your form?
Debi - December 27, 2010 at 10:26 pm |
I do. But it depends on what version of InfoPath designer you are using. Also the questions like “how to use InfoPath designer?” is kind of out of the scope of this blog. Yet another reason to encourage you to do some homework ;)
alecpojidaev - December 27, 2010 at 10:33 pm |
I am actually pretty good with designer and am using 2010. I have changes all the fields rules in your form to include the Xpaths in my new form so I have a fiar idea of how to use the tool. I think the issue is that you were using an older version of Infopath.
Debi - December 27, 2010 at 10:43 pm
>I think the issue is that you were using an older version of Infopath
Nope. The form was designed in IP2010
alecpojidaev - December 28, 2010 at 1:02 am
I found the information by going into the advances settings of the form. Seems like it woudl have been easy to direct me where to add the 00, 01, 02. But I found it. Thanks for all your calculations though – that stuff is very tricky.
Debi - December 27, 2010 at 11:03 pm
I did a search on your string and am following some instructions now- thanks
Debi - December 27, 2010 at 10:32 pm |
On the hours calculations sheet it says that the minutes would not be hard to add if needed. Could I possible get some instructions on how to do that. I need to calculate the minutes as well.
Debi - December 30, 2010 at 3:40 pm |
On the hour form, like Debi is saying, do you have a form made up that delivers in minutes rather hours?
Peter - February 9, 2011 at 7:35 pm |
Nope.
alecpojidaev - February 9, 2011 at 7:41 pm
Alec — As these guys are saying in the HourDifference form, I need to include minutes in the time calculation, can you provide direction on how to make this happen?
Peter - June 6, 2013 at 4:47 pm
Hi Alec:
How can I convert this Excel formula into SharePoint 2010 formula for a vacation request form I am creating?
Hannu
Hannu - January 3, 2011 at 9:39 am |
Sorry I dont understand your request. There are no Excel formulas in this blog.
alecpojidaev - January 3, 2011 at 2:24 pm |
I do understand there are no Excel formulas on this blog but what I am asking of you is what the formula would be in SharePoint to do what I have done in Excel. That formula is what i need to use in my vacation list to calculate remaining status on leave?
Hannu - January 4, 2011 at 7:11 am
Do the search. I think I saw column formula for work days calculation somewhere.
alecpojidaev - January 4, 2011 at 2:12 pm
Hi Alec,
I want to use your WorkdaysCalc and you are talking about the XML, where can I find this file and is there any information to be changed?
Hannu
Hannu - January 18, 2011 at 11:56 am |
Please read my replies to others. The question is already answered
alecpojidaev - January 19, 2011 at 2:35 pm |
Thanks Alec I have figured it out, my next question is on the dates in the XML file, i see you are using the year how can we have this as concurrent and not specified?
Hannu - January 20, 2011 at 4:21 am
In your XML file under Holidays you specify the dates in full like Hol1 = 2009-05-25 and Hol2 = 2009-05-26, what happens now when it is 2010, do you need to change the year in the file every new year? Hope this makes more sence
Hannu - January 24, 2011 at 5:32 am
No, you don’t need to change dates every new year. Usually people for whom common sense is not just empty sound prefer to fill XML with all holidays for let say 10 years and then document this. In enterprise enviroment it shouldn’t be XML file at all. It should be a web service connection to the HR system because holidays are different in different countries and also there is such thing as firm holidays.
alecpojidaev - January 24, 2011 at 2:22 pm
To Hannu: Sorry, I don’t understand your question. Could you please rephrase?
alecpojidaev - January 22, 2011 at 6:38 pm |
A lot of good comments. I am trying to figure out how to count a specific date. I have a SharePoint list with class dates and normally these dates are on a Friday. On my InfoPath Web enabled form, I have a text field called class_date. I want to count all the dates on the SharePoint list that match the class_date on my form. Is this possible. I can do it using code but it does not work when the form is Web enabled.
Michael Vasquez - January 28, 2011 at 11:58 pm |
It should be just a simple formula like count(DateFromList[.=class_date]).
alecpojidaev - January 29, 2011 at 6:46 pm |
This is just great for a non code person as me :-D
Frank - February 9, 2011 at 1:19 pm |
Thanks for the great post. Question: I need to calulate the number days it takes to complete a task while the task is in progress (i.e. today-startDate) and only once there is a value in endDate do I want to calculate days to complete (i.e. endDate-startDate). Both results need to appear in the same field dayDifference. My fields are:
startDate
endDate
today
dayDifference
Pls help!
Much thanks!
FL Newbie
FL Newbie - February 25, 2011 at 4:14 pm |
Why are you limiting yourself with just one dayDiffererence field? What I would do is calculate endDate-startDate on submit event then use different result field to show days to complete that is calculated on open event.
alecpojidaev - February 25, 2011 at 7:38 pm |
Alec, Thank you very much for your code and examples! I’m having one issue though. I have to show the number of hours instead of days, Example 1 day would = 8 hours. I tried create a new field and just enter a default value of (daysWorked *8) but as you commented to another post adding any other cauculation causes an Error. In that post you said to use the “Finalize” action to stop this from happening. But I can not figure out how.
New To InfoPath - March 8, 2011 at 8:27 pm |
Finalize in this case is just the last action in a sequence.
alecpojidaev - March 8, 2011 at 9:52 pm |
Yes I see that now. It’s just an added action that stops the rule. I apologize beforehand that I have very little experience with rules. I’m just starting to pick it up through blogs like yours.
Can you explain to me how, when using the WorkDays calculation I can multiply the sum of the days worked by 8 and not get the error?
New To InfoPath - March 8, 2011 at 10:19 pm
Alec please respond. I really need help with this, please. I just need to know how I can use the sum of daysWorked and multiply it by 8 without getting an error.
New To InfoPath - March 10, 2011 at 4:43 pm
Just download the form that calculates hours difference and multiply by 8 in the similar spot of WorkDaysCalc form.
alecpojidaev - March 10, 2011 at 8:07 pm
Alic, I asume you mean to add a new rule to WorkDays that says.. If workDays contains “;” then substring-before(., “;”) * 24 + substring(EndDate, 12, 2) – substring(StartDate, 12, 2) * 8. But this throws the same error.
P.S. I’m sorry to keep bugging you about this. I promise that once I get this fixed I will never post again! :)
New To InfoPath - March 10, 2011 at 8:54 pm
Nope. I mean you should edit the existing rule not to edit a new one. If you’ll replace my formula with yours everything should work.
alecpojidaev - March 10, 2011 at 10:56 pm
here is a copy of the file it that at all helps. Thanks.
New To InfoPath - March 10, 2011 at 9:11 pm
http://blindluckprod.com/Leave%20Request.Test.xsn
New To InfoPath - March 10, 2011 at 9:13 pm
But what rule do i modify?
New To InfoPath - March 11, 2011 at 12:24 am
The last one that is on the field that calculates business days.
alecpojidaev - March 11, 2011 at 2:35 pm
I appologies, I’m not a programer, from your discription I have no idea what field or expression I need to use. All I want to do is take the sum of daysWorked and mutilpy it by 8.
New To InfoPath - March 11, 2011 at 7:07 pm
The stuff here is quite advanced. May be you should ask programmers in your organization to help you. As for myself I have troubles to make myself doing something that is too simple but requires quite noticable amount of time.
alecpojidaev - March 18, 2011 at 3:14 pm
I have mirrored your settings in my web-based form and still get ‘0;4096’ displayed in my DateDiff field. Changes were to field names to be usable by my form. Date Pickers take input correctly, but the DateDiff field displays the input from the endDate field rule rather than the calculation from the rules withinthe control.
First question would be, what does the ‘0;4096’ represent? Second, why is it being sent to the DateDiff control with no designation of origin? Maybe I am just not getting it. I really appreciate your help with this stuff. I am in an environment that does not allow ‘custom code’ without a great deal of review.
All help is greatly appreciated!
Dale
Dale - March 10, 2011 at 7:41 pm |
You havent copied everything. Couple rules at DateDiff control are missed.
alecpojidaev - March 10, 2011 at 8:09 pm |
Also if you’ll try to read comments above here there is discussion with explanation of what ‘0;4096’ does mean.
alecpojidaev - March 10, 2011 at 8:13 pm |
Alec,
Did not see the explanation of the ‘0;4096’, just a reference to it in the calculation and an iterative table of the person’s calculation steps.
I have built the form from scratch and still get the same ‘0;4096’ result in the dateDiff field.
I copied and pasted your inputs from the tutorial. My rules look like yours, the fields are set up like yours (dateDiff field differs slightly) I am obviously missing a crucial part, but do not see it.
Tried this alternative;
http://www.bizsupportonline.net/infopath2007/calculate-date-difference-infopath-rules-formulas.htm
It works, but seems very cumbersome and I cannot follow it to do any edits.
Thanks in advance for your help,
Dale
Dale - March 10, 2011 at 10:01 pm
If you’ve copied 2 rules at dateDiff field and you still can’t make it work that means the condition on rule is failing. Fix the error in condition formula.
alecpojidaev - March 10, 2011 at 11:00 pm
>Did not see the explanation of the ’0;4096′, just a reference to it in the calculation and an iterative table of the person’s calculation steps.
Exactly. He is demonstrating whats happening in form after dateDIff field got assigned with 0;4096 value
alecpojidaev - March 10, 2011 at 11:05 pm
Hey,
Can i use the same principal to calculate the week number, if yes how?
thank you in advanced guys
Yarie Poerqe - March 18, 2011 at 9:46 am |
http://www.infopathdev.com/forums/p/11553/40957.aspx
alecpojidaev - March 18, 2011 at 2:00 pm |
Alec,
Thank you very much for your reply
”The week number will be amount of days since the beginning of the first week of the year divided by 7 and rounded to the largest integer.”
Sorry for being such a pain, but I dont get this though. I have downloaded the dateCheck.xsn form.
But i dont get the formula i need to use to get it to display the week numbers
Yarie Poerqe - March 18, 2011 at 2:18 pm
The stuff here is quite advanced. May be you should ask programmers in your organization to help you. As for myself I have troubles to make myself doing something that is too simple but requires quite noticable amount of time..
alecpojidaev - March 18, 2011 at 3:22 pm
This is asking a lot Alec, but could you please put it in a form and upload the form please.
I would really appreciate it
Yarie Poerqe - March 18, 2011 at 3:20 pm |
Did you ever get your week number solution?
Mike - June 30, 2011 at 6:05 pm |
No. Should I?
alecpojidaev - June 30, 2011 at 6:25 pm
I would like to have a solution
Mike - July 1, 2011 at 3:24 am
This is probably the best post for working with text type fields in infopath .Even the templates are available for download .
Please keep the good work coming …:)
Anamika Mukherjee - April 15, 2011 at 8:33 am |
Alec,
After copying your exact formulas to calculate the day difference between datepickers in my own template, all runs O.K but sometimes, after changing the startdate or enddate values I get the message error of : ”A calculation in the form has not been completed. The number of calculations used for a single action exceeded the maximum limit.”
I have checked all the rules, formats, expressions but I don’t find the error. It seems to be in the finalizerule.
Please, help!
Thanks in advance,
Jesus
Jesus - May 13, 2011 at 8:23 am |
Alec,
Continuing with my last post:
There is no error when selecting in both date pickers odd or even date values. But if you mix odd and even values, you get the error.
I hope it helps.
Thank you.
Jesus - May 13, 2011 at 9:42 am |
It’s hard to tell what the problem is. Try to disable finalize rule and see what is the difference the problem case and the good one.
alecpojidaev - May 13, 2011 at 2:28 pm |
This has saved my hide, is it possible to work backwards and set the end date based on the a specific number of workdays? i can find examples of code for this, but I can’t find any real specific instructions on how to make that code work (and since I’m not a great coder…)
Thanks for the great solutions!
natalied - May 16, 2011 at 9:18 pm |
Ok. I’ll try to create example as soon as I have time for that.
alecpojidaev - May 17, 2011 at 1:09 pm |
Hi Alec – thanks for this posting! I downloaded your Working days/business days sample file and added my own holidays to the daysofTheWeek resource file, but it doesn’t calculate correctly and I’m not sure why. Some holidays work and others do not – the only thing I can pick out is that holidays crossing a Friday or weekend seem to have an issue. Any ideas why that sample file isn’t calculating correctly? Thanks!
AmyMae - May 25, 2011 at 3:09 am |
Ok. I’ll take a look. I also have to note that xml file is not suppoused to include holidays crossing weekend by design.
alecpojidaev - May 25, 2011 at 1:22 pm |
Hi, I have been looking at the posts and are indeed right on target. I seem to have a problem.
I have two dates in my IP 2007 xsn both showing up with Date Time format. I need to autodistribute the form and wish to use the date from the above field as Due Date.
Earlier assistance will be much appreciated.
Jigar - May 31, 2011 at 12:22 pm |
What part of this solution is preventing you from doing what you want?
alecpojidaev - May 31, 2011 at 1:39 pm |
Hi,
Ur steps r too good for what i expected..
But i got a value o;4096 in both date difference control named starting and ending date……
i didnt get a correct value for these control…
im expecting the value for these controls like 45,56…….
so plz do reply as soon as possible
Kasi - June 2, 2011 at 10:13 am |
It won’t work if you entering letter o instead of figure 0 in o;4096
alecpojidaev - June 2, 2011 at 1:41 pm |
I saw a few comments about this but didn’t see a clear response. I’m getting an error on a browser based form using the workdaycalc template. It works fine if the form is opened in the client but not the browser. I have verified the code for all the fields, and am at a loss. I have this solution in use in a few places in my environment, but am only having problems on this one form:
An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit. The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute.
natalied - June 17, 2011 at 12:03 am |
Hello there!
your work is truly amazing and it helped me a lot, though, I have a very small problem, which is just I need to specify that the weekend is “Friday and Saturday” instead of “Saturday and Sunday”, I have searched a lot but didn’t get a way in till now.
Thank you in advance.
Ramy Besar - June 22, 2011 at 9:07 am |
Btw, I even tried changing the value of each week day in the secondary data source, replacing Saturday by Friday, and Sunday by Saturday, and keeping the same values for the rest of the week days but it’s still the same.
Ramy Besar - June 22, 2011 at 9:14 am |
The figures responsible for weekdays in the formula are 6 for Saturday and 0 for Sunday. Fridays number is 5.
alecpojidaev - June 22, 2011 at 3:01 pm |
Yes I do notice that dude :)), you didn’t get my point as in my first post, I want to specify that the weekend is Friday and Saturday instead of Saturday and Sunday. how to do this?
Ramy Besar - June 22, 2011 at 8:54 pm |
I’ve followed the instructions exactly. The result that I get in the dayDifference field says “true” rather than a number. The data type of the field is “Text” as per the instructions. I’m using IP2010 but forced to save in IP2007 format due to company restrictions.
Andrew - July 14, 2011 at 11:12 pm |
>I’ve followed the instructions exactly.
No you are not. The formula output is numerical not boolean. If you’ll copy my formula and yours side by side the difference will be obvious.
alecpojidaev - July 15, 2011 at 1:35 pm |
dayDiff example
Alec, Like the others I thank you for your contributions. However, after following your steps and verifying all values and formulas match your example, I receive the “infinite loop” error. The data in my dayDiff field after cycling through the error is -8191.5;0.25.
Do you have any suggestions how I can work through this?
Thanks
Bill Burke - August 28, 2011 at 7:43 pm |
There is a link in this article where yu can download working form.
alecpojidaev - August 29, 2011 at 1:21 pm |
Hi Alex,
Would you know how to sort a list box using just xpath? I can’t use the owssvr method.
Thanks,
Sara
Sara Tenenbaum - September 6, 2011 at 10:29 pm |
I don’t think it’s possible
alecpojidaev - September 7, 2011 at 2:53 pm |
Fantastic help, and brilliant files! Thank you!
Just one thing: I am using the “workdaysCalc.xsn” for a holiday requisition form at work, but every time anyone visits to the form, the BeginDate defaults back to “today’s” date, irrespective of what the person who submitted the form set it to (sometimes as far as months in advance). Is there any way of stopping this (i.e. not have today’s date as the default value)?
gurgling_mrd - September 7, 2011 at 4:05 pm |
Sure. Just remove this default value. On the open event just check if the field is empty and if it is then set it to the today’s date.
alecpojidaev - September 7, 2011 at 5:20 pm |
Ah, I had thought it was something about the field itself that I couldn’t identify – didn’t notice the open event! Thank you.
gurgling_mrd - September 8, 2011 at 7:35 am
Awesome stuff. Many Thanks!
tmay245 - September 10, 2011 at 10:02 pm |
I could not get the following to work in my form, any suggestions?
field: dayDifference
xPath expression: concat(substring-before(., “;”) + substring-after(., “;”) * ((translate(xdDate:AddDays(../my:startDate, substring-before(., “;”)), “-“, “”) < translate(../my:endDate, "-", "")) * 2 – 1), ";", substring-after(., ";") div 2)
Melinda - September 21, 2011 at 3:21 pm |
Down loaded the working version of this but I still get: 0;4096 as the day difference, what now?
Melinda - September 22, 2011 at 12:05 am |
The downloaded form is working. You shouldnt be geting any error.
alecpojidaev - September 22, 2011 at 2:37 pm |
Very nice solutions for date difference calculations in InfoPath.
But I get an “possible infinite loop” error in the repeating section example of the WorkDaysCalc.xsn. The normal calculation works fine.
Olaf - September 23, 2011 at 9:42 am |
Thanks for letting me know. This part of the form never been tested in SP2010. It should work in SP2007 though. I’ll look into this issue as soon as I will be able to.
alecpojidaev - September 23, 2011 at 1:20 pm |
Have you been able to investigate for SP2010? This seems like it woudl be exactly what i need if not for the infinite loop error.
Thanks
Dale - November 23, 2011 at 12:18 am
It’s working in SP 2010. You can download any form from this page and they all will work.
alecpojidaev - November 23, 2011 at 2:52 pm
I was able to replicate the recipie but I had to explicitly match fields using the Rules Inspector. Once I adjusted everything, it worked as displayed in the example.
Mark Carter - September 23, 2011 at 6:14 pm |
Hi Alec
Thanks a lot for sharing you knowledge and for patience answering all these question. That is very helpful.
The problem I have is with holidays.
For example I have
If I choose
Start 10-5 End 10-05 –> 1 requested day
Start 10-5 End 10-06 –> 0 requested day
Start 10-5 End 10-07 –> 1 requested day and so on…
On the other hand with 2011-10-12
If I choose
Start 10-11 End 10-11 –> 1 requested day
Start 10-11 End 10-12 –> 2 requested days
Start 10-11 End 10-13 –> 3 requested days and so on…
All things except holidays (weekends )work perfect
Any advice
Thanks
nimonas - October 5, 2011 at 12:27 pm |
Sorry forgot to mention about chosen holidays in a file
hday name=”Holyday1″ hdayn=”2011-10-06″
hday name=”Holiday2″ hdayn=”2011-10-12″
nimonas - October 5, 2011 at 12:29 pm |
Sorry, I’m not able to reproduce this. if I assign the holidays as it is in your example the sequence is the following:
Start 10-5 End 10-05 –> 1 requested day
Start 10-5 End 10-06 –> 1 requested day
Start 10-5 End 10-07 –> 2 requested day and so on…
same with second date:
Start 10-11 End 10-11 –> 1 requested day
Start 10-11 End 10-12 –> 1 requested days
Start 10-11 End 10-13 –> 2 requested days and so on…
alecpojidaev - October 5, 2011 at 1:06 pm |
hello every one.
please explain how to does this in infopath 2010.
anandmarriagecharles michel - October 29, 2011 at 8:14 am |
There is no difference. The form will work in both 2007 and 2010 Sharepoint.
alecpojidaev - October 29, 2011 at 11:54 pm |
Hi Alec,
Sorry, I know you mentioned if someone is receiving the following error message in the date difference field as 0;4096. How can I get the form not to assign the dateDiff field with the value 0;4096? Do I need to turn on Microsoft Visual Studio Tools for Applications?
Respectfully
Jimmy - October 30, 2011 at 11:35 pm |
You don’t want to not assign the dateDiff field with 0;4096 value. Without this step the form wont work.
alecpojidaev - November 1, 2011 at 1:05 pm |
it worked greatly, thanks a bunch sir
naveed - October 31, 2011 at 7:07 am |
HI Is there a way I can use this to get the return date(next working day)
great post by the way
Rego - November 3, 2011 at 10:02 am |
You can modify the form showing day of week. Add to current date depending on output of the form.
alecpojidaev - November 3, 2011 at 4:49 pm |
i am always impressed with you work. I have a similar requirement. The difference is i want to compare my enddate, in the repeating table to my start date , not in the repeating table. somewhat of a combo between your example form. When I tried to implement this IP errors with max number of calculations exceeded. Althought once the error is acknowledged, the day difference is correct. Is there another way to compare the dates if one is in a repeating table and the other is not?
Larry - November 4, 2011 at 5:34 pm |
In addition, it also errors with max calc exceeded with the end date is before the start date. I am trying to figure out a way to circumvent this.
Larry - November 4, 2011 at 5:37 pm |
Hi Alecpojidaev,
Thank you very much for sharing your work with us. I need calculate Task due date by adding Business days to task start date, some tasks has 30 minut so I used AddSecound, for the working day part , I converted your example by deleting End date( Date picker )and EndDateWN add text field called Task due date, in total I have two text field one date picker.
for example: Start Date is 24/11/2011 and we have 3 working day to finish the task, it should be display the 28/11/2011 for Task due date.
I copied the EndDateWn rules to taskDueDateWN :
the condition expression : xdDate:AddDays(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/basepoint, substring-before(., “;”)) = ../my:workDays
Set value to:
(substring-before(., “;”) – substring-before(BegDateWN, “;”) + substring-before(BegDateWN, “;”) mod 7 – substring-before(., “;”) mod 7) * 5 / 7 + substring-before(., “;”) mod 7 – substring-before(BegDateWN, “;”) mod 7 – (substring-before(., “;”) mod 7 = 6) – (substring-before(BegDateWN, “;”) mod 7 = 0) + 1 – count(hday[translate(BeginDate, “-“, “”) = translate(@hdayn, “-“, “”)])
But I am getting errors
Could you please help.
erkindunya - November 24, 2011 at 11:53 pm |
It’s not practical to use increments less than 1 day in this method. Also it’s not clear from your post what are the actual changes you’ve made.
alecpojidaev - November 28, 2011 at 7:48 pm |
Hi Alec,
What I am goining to do is add working days to A date fild and find Due date in Infopath, upside down to your example, I manage to do it on Calculated column and workflow but could handel bank holidays, want to do it Infopath.
Thanks
erkindunya - November 29, 2011 at 12:10 am
Hi, I test this rules and formulas and works greate but when I put them on a web-browser Infopath 2010 form i get the loop error, con you tell me if I need to modify something
Jose - December 7, 2011 at 10:14 pm |
All these forms have been tested and they work just fine in SharePoint 2010.
alecpojidaev - December 8, 2011 at 3:56 pm |
I am getting the same thing. I have spent all morning on this. I am now using the original form and trying to add the other items I need for my application. Let me know if you have any luck.
Karin - December 8, 2011 at 4:44 pm |
Hi Alecpojidaev,
I have downloaded your file and it does work perfectly.
I am using IP 2010 and when I want to add other fields to your form and do a lookup to an existing list with a filter, it tells me what I am trying to do is not valid in 2007 and I have to remove it. Is there a way to convert the form to a 2010 version?
I am trying to copy all of your formulas over to a 2010 form, but I am running into an issue on the BegDateWN rule 2. It is telling me that I am referencing a field that is not valid. I will try again.
Please let me know. I appreciate all your hard work.
Karin
Karin - December 8, 2011 at 2:14 pm |
I’m not aware of the process of converting forms to 2010 version. But when I had have to redo everthing in IP 2010 I hit absolutly no problems (implemented same datadifference in list form)
alecpojidaev - December 8, 2011 at 4:01 pm |
I tried to move everything over, but I am getting the same error that others have gotten about a loop. If you have done the same in a 2010 version, is it possible for you to post that form along with the others?
Karin - December 8, 2011 at 4:41 pm
This error is urelated to the version of InfoPath. It’s just matter of carefully following instructions.
alecpojidaev - December 8, 2011 at 4:56 pm
Would you be willing to look at my form if I sent it to you?
Karin - December 8, 2011 at 5:05 pm |
Sure, just upload it somewhere and send me the link.
alecpojidaev - December 8, 2011 at 5:08 pm |
can you send me an email to [removed] and I will reply back to you.
Karin - December 8, 2011 at 5:16 pm |
[removed] is the link to the file on our website
Karin - December 8, 2011 at 5:20 pm |
Ok. Here is the problem. In sharepoint field there is no just date field as it is in my example. It’s always date and time field. And this means that internal representation of date field always will be yyyy-mm-ddT00:00:00. So to adjust your form you will need to remove the time part in every formula. For example condition at Rule 1 at BegDateWN field should be looking like this :
xdDate:AddDays(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/basepoint, substring-before(., “;”)) != substring-before(../my:beginDate,”T”). There are like 4 or more places in form that needs to be adjusted.
alecpojidaev - December 8, 2011 at 6:45 pm
Thank you so much for looking at that. I will make the changes and try it again. I was pretty sure I had followed all those instructions and I read every comment in this post twice to see if someone else had the same issue. I will let you know what happens. Again thank you soooooo much!!!
Karin - December 8, 2011 at 6:52 pm
No problem. Don’t forget to add test on empty BeginDate at EndDate field. If you not prepopulating it as it is in my form you will need it.
alecpojidaev - December 8, 2011 at 7:09 pm
Hello alecpojidaev:
I have success — no more loop error message.
It is calculating without any errors the correct # of days, but it is not subtracting the holidays in the xml file. I used the same xml file with your form and it does calculate without the holidays. I believe it has to do with the date field in the second rule on the EndDateWN and BegDateWN fields. I am not sure how to structure the filter used in the -count portion of the formula.
translate(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “-“, “”) substring-before(beginDate,”T”) and the same for the endDate. I then changed the formula to use these fields.
translate(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:BegDateOnly, “-“, “”) <= translate(@hdayn, "-", "")
I'm sorry to be such a pain, but I am so close now. Can you help me one more time. Let me know if you want me to post the form so you can review again.
Thanks again for all your help,
Karin
Karin - December 9, 2011 at 5:39 pm |
Part of my post is missing:
second paragraph should be:
translate(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “-”, “”)
I created 2 new fields that calculated to jus the date —> substring-before(beginDate,”T”) and the same for the endDate. I then changed the formula to use these fields.
Karin - December 9, 2011 at 5:42 pm |
Creating new fields might cause an error. The prefferable way is in my previous post. If you still want to use additional fields use 0;2048 value instead 0;4096. It will buy you additional step in calculations without excesing calculation limit.
alecpojidaev - December 9, 2011 at 6:01 pm
Instead of
translate(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “-”, “”) substring-before(beginDate,”T”)
you should use
substring-before(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “-”,””), “T”)
alecpojidaev - December 9, 2011 at 5:56 pm |
It did not like that:
[translate(–>substring-before(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “-“, “”)<–, "T") <= translate(@hdayn, "-", "")
I took out the new date fields I added. Back to only your code.
Karin - December 9, 2011 at 6:15 pm
The quotes are not right ones.
also tails of functions are incorrect
here is how it shoud be:
[translate(substring-before(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “T”), “-”, “”) <= translate(@hdayn, "-", "")
alecpojidaev - December 9, 2011 at 6:19 pm
That did it, it is working perfectly now.
Just want to say “Thank You”. You have been wonderful in walking me through this. If anyone else is having an issue with IP2010, I would be happy to share my form with them so they can see the changes that needed to happen.
Thanks again for everything, have a GREAT weekend and holiday season!!
Karin - December 9, 2011 at 6:45 pm |
Sure, I can upload your form as well as whatever text and your contact info you would like to be viewable for others.
alecpojidaev - December 9, 2011 at 6:53 pm |
This is an awesome resource for InfoPath codeless programming! Google keeps taking me here. However, I have requirement for vacation time to display the business days requested between two date fields. As an example, if the start date for vacation is 12/27/2011-12/30/2011, HR needs to see that what is requested are 12/27, 12/28, 12/29, 12/30. Anyway to tweak your date calculation to display the days in the middle? Any assistance is greatly appreciated!
Rhett Saunders - December 22, 2011 at 2:26 pm |
I really don’t know
alecpojidaev - December 22, 2011 at 4:22 pm |
I will attempt this, but my initial thought is to start with the quasi-pseudo/real code to create the codeless programming in InfoPath:
while (endDate < startDate)
addDays to startDate
display new +1 Day
increment 1
Rhett Saunders - December 23, 2011 at 1:50 pm
It can done. But the info you want have to be displayed in precreated expression boxes with rules that hides/unhides them depending on AddDays condition.
alecpojidaev - January 4, 2012 at 7:26 pm
Any idea on how this may look in codeless programming. I cannot figure this out.
Rhett Saunders - January 9, 2012 at 7:05 pm
Create as many expression boxes as maximum you expect ever have. The expressions shoud be AddDays(BeginDate,expressionBoxnumber). The visibility shoud be based on (begDateWn + ,expressionBoxnumber) mod 7 expression
alecpojidaev - January 9, 2012 at 9:49 pm
If there a way to calc half day if adding one more fields AM/PM?
William - December 30, 2011 at 7:35 am |
Yes there is. Although please don’t expect me to provide a solution to you. This blog purpose is to describe and discuss general concepts.
alecpojidaev - January 4, 2012 at 5:55 pm |
Alec – I did this on the days off application I did. If you provide me with an email, I can send you the form to upload. It also has all the changes required for 2010.
Thanks, Karin
Karin - January 4, 2012 at 5:57 pm |
Thanks Alec and Karin. I am a newbie to InfoPath. Karin could you send me the form by email? My email address is []. Thanks very much.
Edited by Alec Pojidaev (email address is removed)
William - January 5, 2012 at 12:49 am
Karin – I get your form in this post, thanks. It seems that your form cannot calc the following format:
Start Date AM/PM – End Date AM/PM.
Alec’s algorithm is amazing. I will try to improve my form with his rules. Once done, I can send the form to upload.
William - January 5, 2012 at 2:54 am
William – we either have half day (AM or PM) or full days. The form works for what we need. The calculations for the full days is totally based on Alec’s code and works for SP2010. Can’t wait to see what you do with it!
Karin - January 5, 2012 at 12:32 pm |
Karin – Is your half day (AM or PM) for end date, or for Start day, or for both start day and end day?
William - January 6, 2012 at 12:39 am |
dateCheck.xsn is great! I used the rules to calculate 3 sets of start date and end date differences. I need to now get the sum of the 3 sets of differences. When I try my:dateDiff1 + my:dateDiff2 + my:dateDiff3 in a separate calculated field I get “A calculation in the form has not been completed. The number of calculations used for a single action exceeded the maximum limit.” Any help is appreciated.
Milly - January 17, 2012 at 9:29 pm |
Use 2048 value instead of 4096. Another posibility is to present this sum in an expression box and if you need also submit the sum, not just present do the sum operation at submit event.
alecpojidaev - January 17, 2012 at 10:07 pm |
hi, thanks for the info. But, I am having similar problem and getting the ‘an error occurred in the form’s rules or code.”. I tried disabling each rule in my form and the one that created this error message was the mainrule. I tried typing the mainrule and finalizerule manually vs. copy/paste but I cant get it work. I’m customizing a form from an existing SharePoint list. I dont have any other rules just these 4. I also downloaded the datecheck.xsn which works perfectly, but the same rules does not work on my customized form. Any help is greatly appreciated. Thanks.
sharon - January 18, 2012 at 5:27 pm |
>but the same rules does not work on my customized form
Sorry, but the only possible explanation here is they are not the same.
The problem usally is in the condition of the rule. Double check conditions.
alecpojidaev - January 18, 2012 at 7:14 pm |
Hi Alex,
I was wondering if you have successfully managed to do this the other way round? As opposed to finding out how many working days (not weekend/holidays) between two dates, start with a given date and add a number of working days to it.
I keep butting up against the number of rules limit in trying to achieve this. It seems that, without an obscenely large formula (I’m not sure what the limit are on the length of infopath formulas is) this needs to be done in stages;
1) calculate number of actual days to account for weekends
2) find out if there are holidays in between the two dates
3) use method in 1 but instead of original work days, use work days + result of 2) but this might push the end date on to another bank holiday (e.g. easter weekend)
4) similar to 2)
5) similar to 1)… ad infinitum (or at least enough that you can hit the rule execution limit.)
Have you had a go at this? Do you think this is possible/achievable? I was able to reduce the amount of rule executions by shifting the basedate based on the start date so that the number of rule executions to determine the offset can be reduced but I’m still using too many rule executions (also, the formulas are beginning to get silly in length…).
Any suggestions would be greatly appreciated.
Rich - January 23, 2012 at 10:00 am |
The way how to do it is described here:
alecpojidaev - January 23, 2012 at 1:52 pm |
If anyone needs to check the difference between two dates in order to hide/disable a section of a form after a specific deadline, I found the following approach to work:
Specify the expression below inside a condition:
translate(“2012-02-21”, “:-T”, “”) <= translate(xdDate:today(), ":-T", "")
In regard to the method outlined in this article does anyone know the significance of setting dayDifference to 0;4096 . I gather that it somehow tricks infopath's "16 calculations" limitation but I don't really understand how it accomplishes that.
Fred - January 30, 2012 at 3:33 pm |
This approach is not tricking “16 calculations” limit. It just makes this limit bearable :). Try to google “binary search”. The algorithms utilizing binary search are very savvy in terms of number of calculations compearing to any other type of algorithm.
alecpojidaev - January 30, 2012 at 6:01 pm |
I am trying to download the CheckDate Form for your link but when i try to edit it it gives me an error for invalid URL.
Any ideas?!?
Berns - February 1, 2012 at 3:22 pm |
Sorry, this blog is not covering educational material about how to download files on local drives.
alecpojidaev - February 1, 2012 at 5:17 pm |
This is a great post! I am not a developer and it helped me with the date diff but now I need to do the same for hours diff using date&time picker and not as in the example you added where the time is in a seperate control. is there a way to do the same using date&time and the result will give me the hours diff?
Shiri Rabinovitch - February 7, 2012 at 7:56 am |
Yes, there is way how to do it. Unfortunatly, in the nearest future I don’t have plans to create additional forms as a demo for this article.
alecpojidaev - February 7, 2012 at 3:21 pm |
Your demo form works perfectly! Unfortunately, when I try to use it in mym own form I keep getting the errors below whenever I click on the StartDate field…any idea what I’m doing wrong?
“An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit.
The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute.”
Daren - February 7, 2012 at 6:43 pm |
Try to read posts above about the same problem.
alecpojidaev - February 7, 2012 at 11:13 pm |
you are brilliant. I have successfully implemented your formulas with great success. I am using them in a time off request form. I want to increment and decrement hours used with each request. So I added another field, and when workdays changes I multiply that value by 8, for 8 hours in a day. when workdays = 1 i get an rule error. When I change the enddate do so workdays is = to 2, it calculates perfectly (16). Changing it back to 1 I get error. I tried to just set the hours to 8 when workdays = 1, still get error. I am using 2010, but dont think that should impact it. I also changed workdays field type to number.
Any suggestion would be greatly appreciated.
Larry - February 13, 2012 at 4:14 pm |
Upon further investigation, I can’t replicate this error on your form. I’m guessing there is something on my form causing the issue
Larry - February 13, 2012 at 5:05 pm |
Dear Alec..am getting this error when i enter the value in the action rule
Node-test expected.
concat(substring-before(.,”;”)+substring-after(., “;”)*((translate(addDays(xdXDocument:GetDOM(“test1”)/somestuff/basepoint,substring-before(., “;”)), “-“, “”)2<–)
what should i do..please tell me where am wrong..
Gita - February 23, 2012 at 7:19 am |
Looks like you lost multiply sign in front of number 2.
alecpojidaev - February 23, 2012 at 2:20 pm |
Dear Alec,
well..i did a demo by creating a form for calculateing workdays and it worked.now when i try to put it in my original form am getting the following eroor.please guide me on what is wrong and what i have to do.i saved the xml file under the same name and also named the fields in the same way.
“xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday/@hdayn” does not point to a valid location path of a field or group.
Gita - February 24, 2012 at 6:29 am
The path looks correct. Try to paste the same path as calculated value in the expression box. If it does’t work something wrong with your xml file.
alecpojidaev - February 24, 2012 at 2:44 pm
Hello Alec,
I am using your WorkDaysCalc form as a template to create my company’s form in InfoPath 2010. I am using it against a database form and I am getting this error when trying to do Rule 2 for BegDateWN:
“xdXDocument:get-DOM()/my:myFields/my:BeginDate” does not point to a valid location path of a field or group.
The folder structure is as follows:
myFields/my:Requestor/BegDateWN
I have already spent an entire day on this with no results. I would appreciate any light that you can shed on this issue. Thanks.
Stephen - March 8, 2012 at 2:49 pm |
I’m not able to add more to the error you getting – the path to BeginDate field is incorrect.
alecpojidaev - March 8, 2012 at 3:54 pm |
Hello Alec,
To add more to the error I mentioned earlier. I created a form using the database form from InfoPath 2010. Any folder that is created under the myFields folder is given a prefix of (my:)
I created a folder under myFields called Requestor. The folder is now my:Requestor. I placed all of the files located in the WorkDaysCalc in that folder. So the files are as follows:
myFields/my:Requestor/BeginDate
myFields/my:Requestor/BegDateWN
…..and so on.
I have also tried putting the files directly under myFields, but I get the same error.
I hope this sheds some more light on my problem
Stephen - March 8, 2012 at 7:52 pm
very Nice post. I have used this same logic in my leave application form.now I have a requirement that if a person takes leave from fri to mon or anydates which consist of sat and sun in between the leave days .Eg: Begin date :20/4/2012 enddate:24/4/2012 no of workdays is:3 ,but i need to get that as 5 including sat and sun.I know we have to add +2 to the formula.but the problem is this action should run only when leave dates
consist of sat and sun in between the leave days ,otherwise the formula which u gave should run..could you suggest me pls
…
raja - April 20, 2012 at 1:01 pm |
Another form in this blog (the main one) is working this way
alecpojidaev - April 20, 2012 at 2:27 pm |
could you pls tell me the name of that form..so that i can download..
Thanks
raja - April 23, 2012 at 5:36 am |
The name is datecheck.xsn
alecpojidaev - April 25, 2012 at 2:27 pm |
Thanks for u r reply.I will use it.
raja - April 26, 2012 at 7:00 am |
[…] I originally found this form on the following site when I faced this issue: https://alecpojidaev.wordpress.com/2008/12/30/infopath-codeless-programming-walkthrough-2/ […]
InfoPath – Difference between days | Big Eric's Place - November 12, 2015 at 1:57 am |