Hi, in my team we are using a list to forecast estimated costs of the months in 2016. Each month have a column assigned a value in USD.
In another list we need to see if the forecasted values of one line meets the actuals. So we quite often will need to get the forecasted value of a period containing several months, for example March-June.
My thought was to create a while loop that through use of variables and math operations will go through the different columns. I was hoping to be able to use a variable when determing what column to pick the value from, but this does not seem to be possible. 2nd option would be to build a string and use the variables to identify what column to get information from but I would then need to access information from the other list which doesnt seem to be possible. Unless I go through the trouble of creating a variable for every single column (around 26). Can someone advice of a better option?
Hi Fernando, this was also my first thought, but I need to do the lookup in a new column for each loop. Ie. first loop should access USD 2016 Jan, next Loop should access USD 2016 Feb, but I seem to be bound by choosing one field.
The way you showed and screenshot, indeed will not work becaus you cannot pass the variable, but I think what Fernando Hunth is trying to lead you to is NOT to use the standard "lookup" via drop down in the list and selection the field (since you want to be able to enter text/var) but use the function "lookup" which will do the same job except that it's similar to mini-coding and therefore you can replace the lookup column name by something else (a variable)
Again, I have not tried it but only attempt to give clarity to what you may have been offered to try.
Let us know her because this is a useful case to re-use.
Hi Francois, thanks for the reply. What you are describing is exactly what I need, but I am not sure how to use the lookup() function.
Do you have some instructions on how to do this?
1) check this thread Using Nintex Form Runtime Lookup Function to populate a textbox , although for on premises function can be re-use.
2) Lookup data from another list inside a Nintex Form | Walking the stack , including video showing how to
You could also use a query list and switch to the CAML editor, then you can put in references to the column you want to query and just change the reference as the loop executes.
One possibility would be to change your data representation. With a "spreadsheet style" list with one row per year, with 12 columns (one per month), as you've discovered it can he hard to select the column to work on through code.
If instead you set up an ""Entity, Attribute, Value" (EAV) type table, with columns-
Then in Nintex you can use list filters (like the WHERE clause in a SQL SELECT statement) on both the year and month value, so looping over values becomes very easy, at the cost of a list format that's not so readable for humans.
Hi Colin, Thanks for your input. This is a really good suggestion, but the list that I am fetching information from is being used by around 50 users and is not owned by me. So unfortunately I can't "dictate" a new design.
At this point I have given up solving this with a While loop. So I have decided to make an Run if for every column that we need to go through. This will require a little more processing power. But at this point I can not see it being completed with the loop approach. (In the meantime we have also had the complication that our original list has reached its maximum capacity, so we have decided on creating a list for each year, complicating the loop event further).
Instead I have decided to go with the below approach. A Run if for every month and then I continually add additional months to the variables.
This is makes it very easy to duplicate, allowing me to only change the name of the action set and one variable every time I add a new month.
Thanks for all your help!