InfoPath Dev

Hi All, Can someone explain a possible way I can do a Vlookup similar to excel with the "true" range lookup. We have a national Fuel % sharepoint table that I would like to be queried when a user enters in a date. The table is update every Monday so an example looks like this

Date Fuel %

10/6/2014 25%

10/13/2014 26%

10/20/2014 25%

10/27/2014 24%

11/3/2014 26%

11/10/2014 25%

So if the user enters in the date of 10/15/2014 it would go the the fuel table and determine that the most effective fuel date is 10/13/2014 with a fuel percentage of 26%. Thanks, Thomas

Hi tplee,

I have here a sample form with a formula which determines the day of the week. You may find the formula in the date picker where 0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday.. etc. Combining this logic with some rules could help you determine or select the Monday designated for that week. You may find those rules on the button I have added. Kindly download the form and open in design mode (right click > design). Hope this helps.

Thanks for the reply donstephen. Unfortunately I cant open the file because i dont have infopath 2013, but i think i figured out a way to get the information i need. I have a new problem though because the table I have has multiple rows all with different dates. Is there a way I can have the query move row by row. Example when I click a button I have it run the formula and query the date, but its for only one line. I would want it to move row by row and query the date, then if there is a second row, set that dat as the query filed and bring back the data, then go to the 3rd row and bring back the data based off the date in that row. I hope that makes sense .

Hi tplee, here is the form in IP 2010 in case you still need it. As for your new problem, are you attempting to update a column using a formula upon button click? for this one, you will need to use a hidden field and set an action to setting the target field's value based on the formula. You can trigger this formula by adding a condition that when that field is equal to a certain value - triggered by the button click. Example: set that column to the value of "go", therefore the action should only run if that column's value is = to go, then add a logic to reset the fields after. I hope that made sense.

Thanks for the reply. That's what I ended up figuring out. For my new problem what I meant is that each row has a specific date now, based off that date I want to query a sharepoint list that's is updated every monday with the national fuel rate. So I need to figure out a way to loop through however many rows there are in the table and then query each specific date and set the fuel rate to whatever it equals. I attached an example of my table and highlighted the cell im trying to populate with the query. Basically I'm looking for the vlookup true function for multiple rows with different dates.

Thanks again in advance

Hi, the logic still holds true even for querying. So you would need to have a trigger button which sets a hidden field to query a database based on a specific column from that row. Since we are dealing with repeating tables, the set value action from the hidden field will be able to identify which target field for the specific column and row it needs to update. I believe that there is no need for a any loop or decrement logic.

OK i think i get what you're saying but what confuses me is how to set the value of the second row for example. Since the fuel rate field is the same field for every row when I set the value won't it change the entire column to whatever it queries? Thanks

I guess I'm just really confused on how it differentiate between rows and won't set the same fuel rate for each row.

mmmm. I think I know what you're saying now, but wouldn't this require me to have a trigger button for every row that the user would have to press row by row?

Hi, if you place the button outside of the repeating table and then set a value for a field within that table will set the whole column to that value. When this column is triggered, it will update each specific row's target field.

donstephen I love you. Thank you this is totally working now. I really appreciate all the help