Compare/Match/Read a column in a table with another

[Laik]

Hi Guys, 

I have a column of Codes, which has 6 digits stored as TEXT field in a Collection 'colAllCodes'.

I have a Gallery of Codes 6 digits stored as TEXT field, requested to be Added to a SharePoint List.

Before I add to the SharePoint List, I want to check if the first FOUR digits only of the Requested Codes exist in the first four digits of colAllCodes.

If any of the Requested Codes first four characters match with the first four characters of colAllCodes,  Set(varShowAoA, false) else Set(varShowAoA, true).

In the following case, 3314 matches but 7010 do not match, so the function outcome should be Set(varShowAoA, true)

Requested Codes Gallery

Codes

331412
701012

Collection colAllCodes

Value

422010
422060
432220
439050
773012
331411
331412
521093
351011
477312

Hope my query is clear, thanks in anticipation for your assistance. 

Laik

[WarrenBelz · Accepted solution]

Hi @Laik ,

A little more complex than you probably think as you also have a many-to-many relationship - try this structure

With(
   {
      _Codes: 
      AddColumns(
         colAllCodes,
         "Col4",
         Left(
            Value,
            4
         )
      ),
      _Gal: 
      AddColumns(
         YourGalleryName,
         "Gal4",
         Left(
            Codes,
            4
         )
      )
   },
   Set(
      varShowAoA,
      CountRows(
         Ungroup(
            ForAll(
               _Gal As _Match,
               Filter(
                  _Codes,
                  Col4 = _Match.Gal4
               )
            ),
            "Value"
         )
      ) > 0
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Laik]

Hi Warren, 

Superb! this structure of yours worked very well for me.

Thanks for the prompt respond 😀

[Laik]

Hi Warren, 

Sorry, but for some reason, this structure is not working the other way around.

I mean it worked in the above scenario, whereas, in the following scenario, if the first four digits codes exists in both the tables, still it is showing varShowAoA as true! it should actually be false!

I am using the same code you have shared:

With(
{
Codes: AddColumns(
colAllCodes,
"Col4",
Left(
Value,
4
)
),
Gal: AddColumns(
galRequested.Selected.ISICCode,
"Gal4",
Left(
Value,
4
)
)
},
Set(
varShowAoA,
CountRows(
Ungroup(
ForAll(
Gal As Match,
Filter(
Codes,
Col4 = Match.Gal4
)
),
"Value"
)
) > 0
)
)

[WarrenBelz]

Hi @Laik ,

To reverse the logic, you just need = 0 rather than > 0.

[Laik]

Dear Warren,

When I changed it to = 0 rather than >0, it works fine when the codes match in both the Tables, but then, in the other scenario, where one of the requested code DO NOT match with the code in colAllCodes, varShowAoA still shows false! here again I will have to manually change it to >0?

the requirement is that system should determine this automatically...

Thanx for your prompt response.

[WarrenBelz]

@Laik ,

I need to understand exactly what you want here. You posted

If any of the Requested Codes first four characters match with the first four characters of colAllCodes,  Set(varShowAoA, false) else Set(varShowAoA, true).

So you either have one or more matching (false) or none matching (true)

[Laik]

Oh! so sorry! It should be:

If any of the Requested codes first four characters DO NOT match with the first four characters in the colAllCodes, then the varShowAoA should be set to True.

Sorry the previous condition, as mentioned earlier, was misleading.

[WarrenBelz]

@Laik , 

Does this do the job ?

With(
   {
      _Codes: 
      AddColumns(
         colAllCodes,
         "Col4",
         Left(
            Value,
            4
         )
      ),
      _Gal: 
      AddColumns(
         YourGalleryName,
         "Gal4",
         Left(
            Codes,
            4
         )
      )
   },
   Set(
      varShowAoA,
      CountRows(
         Ungroup(
            ForAll(
               _Gal As _Match,
               Filter(
                  _Codes,
                  Col4 = _Match.Gal4
               )
            ),
            "Value"
         )
      ) <> CountRows(_Gal)
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Laik]

I'm afraid, Not! it is not doing the required job when Requested first four digits matches with the first four digits in the colAllCodes, the varShowAoA is still true where as it should be false, after applying the code you shared.

Here in the following case both the Requested Codes first four digits are in the ColAllCodes "3314" & "7010"!