InfoPath Dev

Hi, I have  created a sharepoint list linked to an embedded InfoPath form foro ur drivers to submit monthly mileage returns.

When the form loads, I would like it to load the drivers end mileage from the previous submission and to load it for the start mileage of the new submission. I know how to perform a simple "vlookup" type action when loading a form however this will obviously be a little more complex. Does anyone have a suggestion as to how it can be achieved? I am a novice user so programatical answers may be lost on me.

Thanks a lot.

Dave  

Hi -

Let's see. I think what I would try is:

1. Create a data connection in the form to the same list the form submits to.
2. Make sure the form submits the userName() to a field - like, I'd add a field called "Submitter" and then on form load, if submitter is blank, set it to userName().
3. Select whatever you use to identify the driver (maybe name if that is unique) as one of the columns - it would be great if you had their user name in the list, because then you could easily get back just the items for "this" driver.
4. Select "Created" or  "Modified" (whichever date you think will let you know if the submission is "most recent" - probably "Created"?) and be sure to sort by that field, so the data is returned date ascending
5. Set the data connection to NOT query on load (this is a checkbox on the final screen of the dialog).
6. When the form loads, set the Submitter query field in the DC created in step 1, and execute the query - this should return all the rows for this submitter from oldest to newest.
7. To set your field's value, you'd select the field in the data source that has the mileage, then add a [last()] XPath filter to it

It sounds harder than it is - post back if you need help. :)

Hi, thanks for this, I will give it a go. Just so I know, would it be an applicable method for 2 step data validation. As some drivers use more than one vehicle, it will be necessary to match not only the username but also the registration number.

Thanks

Dave 

 Hi, I am all set up until the [last()] function, It keeps evaluating to an error. Can you give me an example of how to insert it please?

Dave 

What is the error you are getting? Can you select the edit XPath checkbox in the formula editor and paste it in a reply?

Here you go

xdXDocument:GetDOM("Mileage return data")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:o7i7[(../d:submitter = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Driver0/pc:Person/pc:DisplayName) and (../d:kxwq = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:kxwq)] 

It has 2 step verification using Driver name and Registration number.

Dave  

Try this:

xdXDocument:GetDOM("Mileage return data")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[(d:submitter = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Driver0/pc:Person/pc:DisplayName) and (d:kxwq = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:kxwq)][last()] /d:o7i7

And if you get a syntax error when you verify, let me know what it says (you can attach a screenshot under the Options tab in a reply)

Oh euw. I forgot a browser form won't support last. Let's try with a count of the rows.... Are you using query fields to only return the rows you need? If so, we may not need the filter on submitter and kxwq.

xdXDocument:GetDOM("Mileage return data")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[(d:submitter = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Driver0/pc:Person/pc:DisplayName) and (d:kxwq = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:kxwq)][count(xdXDocument:GetDOM("Mileage return data")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[(d:submitter = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Driver0/pc:Person/pc:DisplayName) and (d:kxwq = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:kxwq)])] /d:o7i7

Hi, all working!!

Many thanks for your support and great help.

Kind Regards

Dave 

 Hi, we have a change to make!! sorry. I want to remove the matching of the driver name and only validate on registration number if possible. I tried to edit your xpath code but it messed up some rules on other fields.

Could you explain how I can edit this xpath to remove the driver matching? 

I removed the submitter manually from this one:

xdXDocument:GetDOM("Mileage return data")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:kxwq = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:kxwq][count(xdXDocument:GetDOM("Mileage return data")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:kxwq = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:kxwq])]/d:o7i7

 Hi, I did that too, turns out there was a hidden rule sitting in the form load section that was confusing a field change rule. My bad sorry.

Thanks a lot.

Dave