Need to get multiple correspondent value using lookup function

Help Studio
, ,
1

I have 2 datatable, variable1 = lookup value , variable2 = raw data.
1 lookup value can have multiple lines in raw data, and I only want get the values in column “ERP Invoice Number”

What I want to achieve is, lookup the 1st value in variable1 , get all the corresponded value in variable2.

Then write it in the notepad.
Example look up Billing number 462558569 will get array of
3041-5200003479-2022
3041-5200003487-2022
3041-5200003554-2022
3041-5200002374-2022
3041-5200002375-2022
3041-5200002388-2022

Then continue lookup 2nd value in variable1 in variable2… until the last value in variable1.

Below is variable 2, as you can see, 1 Billing number will have multiple ERP Invoice Number
image

I have been searching around, still cant get the answer.
The test I did, the message box produces empty result.
image

2

you have multiple results
so dont use lookup datatable , because that only gives you 1 result

@jmcheong7
instead use filter data table

image
image1110×303 18.4 KB

then use output datatable to assign the filtered data to string variable e.g. txt

image
image1159×325 13.9 KB

now we can write txt to notepad

1 Like
3

if you just want to write erp invoice # to notepad and not the whole filtered table, then instead of usingg output datatable to txt variable, assign

txt = String.Join(Environment.NewLine, filteredDt.AsEnumerable.Select(function(row) row("ERP Invoice Number").toString).ToList)

4

Hi Jack,

Thanks so much for the help.
I will try it out now.

For the lookup value, I want it be dynamic and reading from the variable (Unique_BillingNo)
And want to perform while loop. to use 1st value in Unique_BillingNo then till the last value.

Could you help on this ?

5

can you upload the excel @jmcheong7

just a few rows (maybe 1st 20) and include the header

6

use this
Main (1).xaml (13.6 KB)

result
image

now im writing to text file, if you want to write to webapp, use the txtToWrite variable
image

image
image786×204 16.8 KB

7

it looks like that in general it can be solved by grouping the data

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.