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

(0) Share

Report

Posted on by 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

Categories:

Building Power Apps

I have the same question (0)

All responses (26)

Answers (4)

Verified answer

WarrenBelz 153,084 Most Valuable Professional on at

Like (1)

Report
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

Was this reply helpful? Yes No
Laik on at

Like (0)

Report

Hi Warren,
Superb! this structure of yours worked very well for me.
Thanks for the prompt respond 😀

Was this reply helpful? Yes No
Laik on at

Like (0)

Report

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
)
)

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

Hi @Laik ,

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

Was this reply helpful? Yes No
Laik on at

Like (0)

Report

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.

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

@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)

Was this reply helpful? Yes No
Laik on at

Like (0)

Report

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.

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report
@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

Was this reply helpful? Yes No
Laik on at

Like (0)

Report

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"!

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

@Laik ,

Looking at the logic I posted, I am simply comparing the total number of rows in the gallery with the total number that match the collection and if they do not equal, set the Variable to true. You understand the required logic better than me. Is it possible that you have more than one match in the collection for an individual item in the gallery ? If so, this is going to be difficult if not impossible to solve.

Was this reply helpful? Yes No