InfoPath Dev

I am new to IP and have been developing a form to calculate mileage claims. I have a date pick field for the date of travel. A total traveled miles filed, and a rate field that was static with a prepopulated value to calculate how much to claim. This worked fine until the rates needed to be changed on a certain date. Changing the prepopulated value on the form is not feasible when the claims need to be filed with different rates. I need to be able to change the mileage rate in a field based on a date. i.e 12/1/2016 the rate will be .55 on 1/1/2017 the rate will be .52 etc. I am thought I could do this with a SharePoint list and rules on the form, but I can only get the rate from the first line on the list. Is a list the best way to achieve what I am trying to do?

Yeah, I'd use a list. You may be missing adding a filter - when you select the item from the list (secondary data source) there is a "filter data" button that lets you specify how to determine which row to get the data from.

Hmm... Not sure what to filter. I have a rule condition if the date picked for the travel date is less than or eq to the date and time field on the rate list then set a value (I picked rate) but not sure what to filter on. I have filtered on the ID I have filtered on rate. nothing gets populated in the field. on the list I have 1/1/2016 (data and time) rate 0.55 ID is 1 and 1/1/2017 0.535 ID is 2

I think instead of the condition you added to the rule, you need the filter on the data when you set the value. I'll try to set up a simple sample later today, or attach a copy of your template under the options tab in a reply if you'd like a hand.

I have attached the test form that I am using. Thanks

 Okay, a few things - you have a rule on "Mrate" to set a query field and run a query based on the date? That rule should probably go on the date itself. Second - how many rows do you think you'll end up having in your list? Probably not a lot, right? I'd just query all the data on load, and then set the rate when the date changes. Here's a copy of your form back - I disabled your rules and set a rule on the Date field. I kept a rule there to query the data connection - but you could just set that connection to query on load. You need to change the data connection to sort by date desc, so that the nearest date is returned first - and then I think what I have put in place will work. :) Save the form locally, right click, select design to open in InfoPath Designer.

Thanks! That helped a lot. It gives the correct rate based on the date chosen. The only issue I have now is that if I have the field in a repeating section after the first selection the rate remains the same as the first row regardless of the date picked on subsequent rows. Do I need to add another rule or can I do an if then statement? I have not seen that being available on the available functions.

InfoPath doesn't have an if / then - but if this is on a field in a repeating table, you may need to use the current function in your formula for the value. In your rule, click on the formula editor for the value. Select the edit XPath checkbox and copy the resulting XPath - paste in a reply & I can help you out. Let me know the name of the repeating group too. :)

when I put a check in xpath it does not give me anything. I have attached the form to the post. the repeating section is claimdata

 Modify the Rate data connection and select the checkbox to run the query when the form load. Remove the rule from the Date of Travel field that runs the query. The way the form is now, the query will run every time the date changes - that is unnecessary. When I click the Edit XPath checkbox, I get this:

xdXDocument:GetDOM("Rate")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:zu7p[msxsl:string-compare(../d:p9yg, xdXDocument:get-DOM()/my:myFields/my:group2/my:Claimdata/my:Date) <= 0]

Which needs to change to this:

xdXDocument:GetDOM("Rate")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:zu7p[msxsl:string-compare(../d:p9yg, current()) <= 0] 

I've attached the form w/ that change. 

Thank you. I would still like to know how you got the xpath to come up. is the attached image where you clicked?

Figured it out. I was looking in the wrong place. Thanks for all your help.