Match Arrary variable with DT column

Help Studio
1

Input data available array variables :

Variable 1 : Arr_Code

Variable 2 : Arr_CompanyCode

DB Table stored in Datatable:

image
image395×542 159 KB

Requirement:

Need to validate if anyone of the value available in array Arr_Code variable is available in “Code” column in data table and also if anyone the value available in array Arr_CompanyCode variable is available in “CompanyCode” Column in data table

If both values matches then stored in str_status as YES else No

Note : Attached DB table file for your reference
HMN_TSA_VENDOR_APPROVAL_MEXICO.xlsx (11.4 KB)

2

You need to convert the array in ‘A,B,C,D’ and move to db, then you convert this to data table and perform the logic in the database level because your data is already in the database

if you do this then your bot will me much faster then you put logic in local

@Sathish_Kumar_S I know its complex but its much faster

3

please share some sample values representing items from Arr_CompanyCode
For Arr_Code we would assume items like E110, F000…

4

Sure… Here is the some sample values

Arr_Code = E110 or E120 or E200 etc…

Arr_CompanyCode = 3105 or 3112 or 3113 or mexico

5

just be more specific is it

  • String Array like {“3105 or 3112 or 3113 or mexico”, “12 or 15 or italy”}

Do you want to check for exact match like:
grafik
is
grafik

or only if 3105 as part of the company code

Any reasons why there is an derivation of & vs or and different letter cases?

6

I can create another table just for company code and the DT will look like below

image

or it is just 4 (3105,3112,3113,Mexico) values that we need to validate… We can hardcode these values in Bot code itself for validation

Attached both Dt… Please use as per your convenient.

CompanyCode.xlsx (9.6 KB)

Code.xlsx (11.2 KB)

7

Arr_CompanyCode and Arr_Code may contain multiple values or single values

Company code validation :
We need to validate if any one of the value available in Arr_CompanyCode is matching with DT CompanyCode column rows

Code Validation:
We need to validate if any one of the value available in Arr_Code is matching with DT Code column rows

How i am getting values to Arr_Code Array :

Input text : ProfessionalServices P000 , Human resource development 801115, HR Services P100 , Compensation/payroll P131

Using Regex to capture each values in BOLD and stored in Arr_Code

Example Value in Arr_Code = {“P000”,“P100”,“P131”} or {“P000”}

How i am getting values to Arr_CompanyCode Array :

Input text : Cocode 3105, Plant 0310, CoCode 3112

Using Regex to capture each values NEXT to CoCode and stored in Arr_CompanyCode

Example Value in Arr_CompanyCode = {“3105”,“3112”,} or {“3105”}

8

@Sathish_Kumar_S

Try this in assign

Dt = dt.AsEnumerable.Where(function(x) arr_Code.Any(function(y) x("CompanyCode").ToString.Contains(y)) AndAlso arr_Code.Any(function(y) x("CompanyCode").ToString.Contains(y))).CopyToDatatable

This would give all matched rows…of if you need to write again each row that if it is matched or not then use below in invoke code

dt.AsEnumerable.ToList.ForEach(sub(x) x("Status") = If(arr_Code.Any(function(y) x("CompanyCode").ToString.Contains(y)) AndAlso arr_Code.Any(function(y) x("CompanyCode").ToString.Contains(y)),"Yes","No"))

Cheers

9

Thank you for you response during weekend.

Let me explain my requirement again:

There are 2 Data tables DT_CommodityCode & DT_CompanyCode (Attached the files).

Two String array variables ( Arr_Commodity & Arr_Compamycode)

Example Values in Arr_Commodity = {“P000”,“P100”,“P131”} or {“P000”}

Example Value in Arr_CompanyCode = {“3105”,“3112”,} or {“3105”}

Requirement :

  • Need to validate if any one of the values available in Arr_Commodity variable is available in DT_CommodityCode. If it is matching, then write to str_CommodityStatus = “Yes” else “No”
  • Need to validate if any one of the values available in Arr_CompanyCode is available in DT_CompanyCode.. if it is matching then write to str_Companycode = “Yes” else “No”
    DT_CommodityCode.xlsx (11.2 KB)
    CompanyCode.xlsx (9.6 KB)
10

@Sathish_Kumar_S

Then use the same formula…but remove the and condition and then use each condition separately

Cheers

11

Sequence.xaml (22.2 KB)

image
image1582×817 54.6 KB

I am not sure if i am following your instruction correctly… Here is the error that i am getting (Please review attached the XAML )

12

@Sathish_Kumar_S

As per error there are no matching rows…
Also you dont need the assign you need only invoke code for your requirement

Cheers

13

image
image1566×432 16.1 KB

Got it… but getting the error in invoke code

image
image1745×697 26 KB

image
image778×577 23.7 KB

14

@Sathish_Kumar_S

Can you please tell the error

Is the new column you need has been added to datatable

Cheers

15

Error : Invoke Code: Exception has been thrown by the target of an invocation.

I don;t want new column… just want to know if validation is success or not?

Are you able to open the attached XAML file (new_Sequence.xaml)
new_Sequence.xaml (25.4 KB)

16

@Sathish_Kumar_S

Dude if new column is not there then how would you know which rows are matching and which are not matchingV?

If you opwn exception details from local you would see the column is missing

Cheers

17

I have applied below simple logic and my requrement is fullfilled… Shall i use this same logic?

image
image469×781 22.6 KB

image

if = arr_commodity.Contains(CurrentRow(“CommodityCode”).ToString())

18

@Sathish_Kumar_S

Basically even if one matches you want to know then we can use linq…if you want to check for each row then what you have already done is what you need to do

Cheers