You’re offline. This is a read only version of the page.
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
@Laik ,
I am still a bit perplexed how the code you posted gets the result you described, however you can condense as below and not have collections hanging around
Share
Report
All responses (
Answers (
With(
{
_AllCodes:
Distinct(
colAllCodesWDuplicates,
Left(Value, 4)
),
_RequestCode:
Left(
galRequested.Selected.ISICCode.Value,
4
)
},
With(
{
_Matched:
ForAll(
_RequestCode,
Value exactin _AllCodes
)
},
Set(
varShowAoA,
CountRows(
Filter(
_Matched,
!Value
)
) > 0
)
)
)
@Laik ,
You might have a look at some of the suggestions posted and how they match what you were asking. I am wondering how what you have now actually does the job you described, but if it works, that is good.
Hi Warren,
Thanks for all your support and patience.
However, for some reason, your revised code doesn't seem to work when there two or more codes match with the codes in colAllCodes. I played around with your revised code and tried a different way and fortunately, it worked for me, I created three collections of only 4 first four digits, first one with all codes, 2nd collection with the requested codes to add and the third one for the codes that matched exactly in the above two collections.
ClearCollect(colAllCodes,Distinct(colAllCodesWDuplicates,Left(Value, 4)));
ClearCollect(colRequestCode, Left(galRequested.Selected.ISICCode.Value, 4));
ClearCollect(colMatched, ForAll(colRequestCode, Value exactin colAllCodes));
Set(varShowAoA, CountRows(Filter(colMatched, Value=false)) > 0);
and fortunately it worked exactly the way I wanted.
Thanks once again...
@Laik ,
.So if everything other than all items in the gallery having a match , we are back to does not equal the row count of the gallery in the last code I posted.
CountRows(
Ungroup(
ForAll(
Gal As _Match,
LookUp(
Codes,
Col4 = _Match.Value
)
),
"Value"
)
) <> CountRows(_Gal)So if the total number of matches of items in the gallery to the collection does not equal the total number of items in the gallery (in other words they do not all match), the result is true
Nothing Matches (both the requested codes do not match with any code in colAllCodes) - Set(varShowAoA, true)
At least one, but not all match (at least one requested code match with any code in colAllCodes, but if the second requested code do not match or there is a code in the galrequest which does have a match in colAllCodes) - Set(varShowAoA, true)
All match (each and every (ALL) requested code matches with the colAllCodes) - Set(varShowAoA, false)
Thanks for your help Warren.
@Laik ,
I have read your posts several times and to be honest still do not fully grasp exactly what you are trying to achieve, however as I noted of the three possibilities I can get for you:
Which one should set the variable to true (or false if it is one)
@Laik ,
You cannot set a variable inside a ForAll statement if that is what you are asking. All I can deal with are the three end possibilities I listed and set true or false based on the outcome of two of these
#1
WarrenBelz
146,587
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,928
Most Valuable Professional
