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.

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.