Infopath form

I am designing an form in which a user enters a date by selecting it on a date picker. When the user enters the date I want the form to update another field with the week number which is in the form e.g. 2013051, which represents day 1 of week 5 in 2013. I have stored the week numbers in share point list with relevant dates. I have a rule on the the date picker field as follows:

update field(weekNumber) with weekNumber where the dateFormCompleted (which is the field on the date picker that users use to select date) is equal to dateOnSharePointList.

The weekNumber is not being updated. My initial thoughts are that the date format that is shown on the date picker is NOT the same as the format that is stored in the Sharepoint List - dd/mm/yyyy.
I have a created a test field to check the date format returned when a date is selected with a date picker  - seems the visual format on the date picker is not the same as tha returned. See value on the datePicker Test field
I assume that when the rule for updating the weekNumber
is executed it gets confused because of different date formats.
Please can someone help me, I have spent days trying to resolve this. I still fairly new to using infopath
test.pdf
iCandoallAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Clay FoxDirector of Business IntegrationCommented:
Typically to trouble shoot I would create a view or put it temporarily on your current view the repeating table of your SP list and the date fuel as a calculated value. Set the formatting of the calculated value to no format so that you see the base data.

this way you can see them both. For an IP comparison the strings must match, so you may need to do some substrings and concats or translate functions to get them apples to apples.

but with this layout you can keep experimenting until you get the match you need.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tapiwabCommented:
weekNumber equals dateFormCompleted

you should be able to to this easy which infopath version 2010 or 2013?
0
iCandoallAuthor Commented:
@Clayfox thanks for your response. Firstly I am not quite sure how I would get the calculate value of the date in the sharepoint list. Do I need to create another calculated column, if so what sort of formula do I need to retrieve the date value.
Initial the user wanted the week number to match with the name of day, and I got this to work (see attachment), now the user wants to be able to select the date from the date picker and get the week number   populated automatically with relevant week.
As you correctly point out, its making sure that the string match between the date picker date format and sharepoint list date that I am struggling with.
@tapiwab, unfortunately I only have access to 2007
test2.pdf
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

tapiwabCommented:
an example of calculated column
http://office.microsoft.com/en-za/windows-sharepoint-services-help/examples-of-common-formulas-HA001160947.aspx

you have to remember the date format that shows in SHAREPOINT LIST IS SET ON SITE SETTINGS > REGION SETTINGS
0
iCandoallAuthor Commented:
@tapiwa thanks. I have created a calucated column on the sharepoint list and used this formula

TEXT(Date_Form_Completed, "dd/mm/yyyy") - courtesy of Stanfford @ http://social.technet.microsoft.com/Forums/sharepoint/en-US/ade1b4a2-7eac-479a-9dde-baa66eb18e36/how-to-change-date-format-of-sharepoint-list.

However when I tried to update my data connection in infopath the calculated column is not showing in the data connection wizard. Any ideas. BY having the calculated column formated this way I am trying to make sure that the date I am comparing to in my rule infopath form is the same as the one on the infopath field. Any ideas?
0
tapiwabCommented:
do you want the users to view the column in infopath form,

if so i suggest in infopath create a function
0
iCandoallAuthor Commented:
@tapiwa sorry I do not quite understand what you mean in your last post. Users will complete form using web browser enabled form to submit to a diffent sharepoint list. The list that stores the dates and corresponding week numbers which I want users to retrieve by selecting a date using a date picker in an infopath form, is a different list.
What is happening is that the week number is not being updated when users select a date on the form when I preview the form. I have narrowed this down to being due to different date formats between the date picker date field and the date on the the sharepoint list. The week Number is being updated when I  use a rule without a filter.
0
iCandoallAuthor Commented:
@Clayfox, how do I set the calculated field to no format so I see the base data?
0
iCandoallAuthor Commented:
Guys anyone figured out how I can do this filter to compare the string in the form and list suggested by Clayfox above. At the moment I can set the week number field by selecting a date from the date picker without a filter applied. So whatever date I select it the first week is selected because there is no filter,
0
Clay FoxDirector of Business IntegrationCommented:
Can you provide an example of the SharePoint list? If you were going to make it easy you would have 2 columns, date, and specialdate
the first would be todays date and the specialdate would be what you wanted in the form for today. You would have to make 365 entries but it would be done. I would recommend Excel and then create the list from the Spreadsheet.

otherwise you would have to use code in the form to determine week of year and the days of the week and then concat those together.
0
iCandoallAuthor Commented:
@Clayfox thanks for getting back. Here is attachment of the list I use to select the week number. I want to compare the Date_Form_Completed column to the date that is selected on the Infopath date picker. List of week numbers column (Year_week_Day) is a single line of test and Date_Form_Completed is a date column which I have set to date only format on the list settings.
Users select a date on an infopath form using a date picker which sets  the field Week No on the infopath form to relevant week number stored on the Year_Week_Day SharePoint list (note difference in spelling of "Week" as compared to the column in this list)  attached here as Fig 1. The Week No depends on the date that is selected on the date picker.
I have tried to set the field on infopath by using a rule as shown in the attachement in Fig 3 but the weekNumber field is not being updated - when a filter on the date is applied.
When I use a rule without a filter applied on the date as in Fig 2, the weekNumber fields gets updated with the first instance of the week number on the
Year_Week_Day . As you pointed out above, this is a date comparison issue. To behonest I cannot figure a way out and need to get this project out like yesterday, I really appreciate you taking your time to help me.
I have used concat and substring functions to format the date to the format dd/mm/yyyy on a test field so that it is the same as the date format on the Sharepoint list but I still cant compare the two dates. I hope this explains what I am trying to achieve.
Please note - the Share Point list has got 365 weeks on it I have just given you an extract of the list.
y-w-dList.pdf
0
Clay FoxDirector of Business IntegrationCommented:
Ok I think this might be simple, you need to use a filter.

so when you select the set field, when you go to the list you need to use the filter button in the lower left of the window. Then you will say give me the week value where date enter is less than the date field in your list and addDays(dateenteredfield, 7 is greater than date field in list.

why you are getting the first list value is because you need a filter so that it finds a value lower in the list based on parameters.
0
iCandoallAuthor Commented:
@Clayfox thank you for you help. Quick question though, In which field do I add the addDays function? I have set the field as suggested above as follows, but I am not sure how to go about adding the days. Once again thanks for you invaluable input
0
iCandoallAuthor Commented:
@Clayfox sorry to be a pain. Here is a filter I have put together, wonder if makes any sense, and by the way its not working

@Year_week_Day[msxsl:string-compare(datePickerTest, @Date_Form_Completed) < 0 and msxsl:string-compare(@Date_Form_Completed, addDays(datePickerTest, 7)) < 0]

Year_week_Day is  the week value in the Sharepoint list
datePickerTest is date entered on infopath form
Date_Form_Completed is date on the Share point list

Guys anyone to help me with this?
0
Clay FoxDirector of Business IntegrationCommented:
Yah you are not getting it.
It is when you select the Year-week-day field from the list, you do not want the first value remember.
click on the Year-Week-Day field once, then the create filter button in the lower left of the window.  Then select add pick calculated-YWD in the first drop down is greater than date entered in form and them add a second one. Select Calculated-YWD less than or equal to and then select formula from thebdropdown. That is where you will put the addDays(date entered, 7)
0
iCandoallAuthor Commented:
Thanks, man you are awesome. I get it now. Although I still have not got it sorted, after following you detailed instructions above, you have pointed me in the right direction.  I will post once I have figured out something.
0
iCandoallAuthor Commented:
@Clayfox, man you are awesome. Now sorted, didnt need to do anything else besides what you advised. Thank you very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.