How to get the value from another list based on two columns value

Hi,

using the nintex lookup formula, you can receive multiple values by appending "true" to the formula.

For example:

lookup("MyList","MyColumn","MyVal","ID",true)

If you do this twice for you columns "Release number" and "Environment" you end up with a bunch of ids, where in theory one id should occur twice.

You can then extract the duplicate using a little javascript:

function intersectArrays(...arrays) {
  //get first element of arrays
  const arr0 = arrays.shift();
  //if the remaining arrays length is 0, there is no need to intersect. return the only element provided
  if (arrays.length === 0) {
    //Exit condition
    return arr0;
  }

  //intersect the first and second element
  const arr1 = arrays.shift();
  const intersected = arr0.filter((elt) => arr1.indexOf(elt) !== -1);

  //and intersect the intersected ones with the remaining arrays
  return intersectArrays(intersected, ...arrays);
}

And: You can use the js-function in a calculated value formula, which would then look like this

lookup(
    "Release Details",
    "ID",
    intersectArrays(
        lookup("Release Details","Release number",your_release_number,"ID",true),
        lookup("Release Details","Environment",your_environment,"ID",true))[0],
    "Date")

Please note that the JS provided can´t be executed in older Browsers, as it is using modern javascript.

I don´t know if there is another way but this is how I do it if needed.

@bansal_59 / @Tarf ...there is another way to achieve this:

1. Create a calculated column in Release Details, no need to add it in any of the views
2. Use concate formula to build "ReleaseNumber_Environment" value.
3. Now in your Nintex form add calculated control to build the same string using Named Controls of Release Number and Environment on your Nintex form. Apply the rule to always hide this calculated control.
4. Now you can either use:
   1. Calculated control and lookup function to get the date
   2. List view control on the form and filter based on the value from Step # 3

Hi @kunalpatel,

thanks for sharing!

this indeed works for column types which are supported in calculated columns.

If a lookup column in the Release details list is used for either Release Number or Environment, this does not work anymore. Since I am heavily using lookups I came up with the js solution.

Your solution should be the go-to solution if you can apply it to your environments. If you are using workflow, you could even populate a hidden single line of text each time the element is changed to achieve the same with all column types.