SharePoint 2010: Calculate working days with Infopath 2010 using Excel Services
InfoPath forms has been prepared to serve our business functions. However, sometimes it is difficult to dealing or come up solution for some scenario.
First, we determine the form in the fields we use.
- Date: at calculates day that we add to our column. Set the date as the type.
- Number of Business Days: The number of working days add to date column. Upfront as a number.
- Expiration Date: date column in a day's work, with the addition of the outcome we are writing. Upfront as a type of date.
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo1.png
We can use add day or add second on Infopath,However, the addition of the number of working days is different we have 5 working days in week, some times we have holidays depend on country of origen.
In order to do this, make an excel file, this file is a document library on a SharePoint and create below columns and rows, "Tarih" means Date, "Is gunu sayisi" means working days in number format and "sure sonu tarihi" means Due date,
Format date and due date as date, make the working days in number as Integer.
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo2.png
use excel business days function. "Isgunu(c1;c2) the "Isgunu" means workday.
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo3.png
English in the Office program "WORKDAY" function in use. Our Excel "WORKDAY" function is no longer with the DATE (C1) + Number of Business Days (C2) = Expiration Date (C3).
Finally, in order to use these fields to excel in areas of services will call the web service.
C1: exceldate
C2: excelworkday
C3: I would characterize as a result. As you want to rename it.
So far, everything is OK then save the Excel file to a document library in Sharepoint.
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo4.png
So far, everything is OK then save the Excel file to a document library in Sharepoint.
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo5.png
We need to add a new data connection. With this data in Excel Services Web service, add the rest service. Here, in this connection you have connected to a particular address in the address is a standard service, edit its data
Connection link modified for your need
- hesaplamalar/workdayws.xlsx
- sonuc
- exceldate
- excelworkday
The steps to add a data connection;
InfoPath form at the bottom right-click on the links in the data.
The next screen, click the Add button the data connections.
Create a new connection options, the Import Data and select from.
REST Web Service data sources, select.
Edit according to your own web service address given above and paste it here and add that he terminate the connection.
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo6.png
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo7.png
Service has now been added as a data connection.
Now we will create a button to perform a calculation. This process of adding a button to perform in the rules. If you do not have to add the button. For example, the number of work days in the InfoPath form, the process can add to your rule.
Infopath add button controls. Then let's add the necessary rules
- Rule our service department are the digits of the date and day change fields in the InfoPath
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo8.png - rule who will calculate the rest of our service by running the query.
The result of the Expiration Date field will carry
1.Kural: a Select-Button and add a new action with the option Manage Rules.
b-On the Actions menu in the "REST URL Change" option.
c-section that you add a data connection, select the web service.
combined using the d-Address at the InfoPath FX offered by us in a REST service with the change in InfoPath will provide the date and the number of working days.
"XPATH Edit" kopyalıyorum the rule. In this way, you can use in your own space;
concat("http://Sharepointadresiniz/_vti_bin/ExcelRest.aspx/hesaplamalar/workdayws.xlsx/Model/Ranges('sonuc')?$format=atom&Ranges('exceldate')=", my:Tarih, "&Ranges('excelworkday')=", my:İşGünü)
my: History, "& Ranges ('excelworkday')=", my: Business Day)
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo9.png
Bold change according to your own system with the specified portions. "My: History," in areas such as InfoPath form areas of our scenario. Data of a Web service to use when trading is no longer here. "XPATH edit" a state of being elected will be shown as follows.
E-fields in the right places, after placing the first rule, saying okay let's be completed.
- Rule: on button adding a new action. Action to be run as a "query", and then provide a REST query our service.
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo10.png
OK, the finish by saying this rule.
3.rule: So far, with the rules, before the rest areas and service areas that have changed in InfoPath (1.rule), then ran the query, and we have built with these values account (2.rule), now our area is the calculated value of the Expiration Date will print.
This action will work, so as to add new action "Set the new field value" from the select. To print the Expiration Date as a result of our area, bringing the history of the part of the. Value is awarded to the REST..
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo11.png
Do not say this was where this is also a standard.
FV brings to the REST service as a result. We want to exceed the FV field provided we are writing.
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo12.png
Let's be okay and then he finished the last rule in the FV field.
As a result, the last appearance of our policies will be as follows. The order of rules is important.
http://www.serkankonak.com/sharepoint_en_US/Lists/Photos/121711_1134_Calculatewo13.png
Calculation was performed successfully. Holidays abroad, and the result returned was added to 5 business days of leaving.
This is an example. You never want to code, you can make different calculations with Excel Services capabilities. If we did it using InfoPath to normal on the coding would write half a page of code. And to publish the form template will be very hard and it will become administrative template. In this way, we did not coded and it codeless and much easy than using the code. InfoPath forms can be customized to list these properties are valid data.