Hi, I recently had an issue when trying to s...

[Green_Gorilla]

Hi,

I recently had an issue when trying to select data from a table. I used an ANY(SELECT( function, which had worked perfectly on several other tables.

In this particular table, however, I was looking to select based on whether a particular text value appeared in a column.

So I was looking for, say, “T5”, in a column with values “T2, T5, T9, T18, T27, T58” using the CONTAINS() function. It wouldn’t do it…it couldn’t “find” the value “T5” in that list, and hence returned the wrong data from the table.

When I replace the T numbers with words, so for example, looking for “Bruce” in the column containing “Adam, Adrian, Bruce, Craig, Dave, Michael”, then it works perfectly, finding “Bruce”, and hence selecting the correct data.

Is this a bug? Or something I am not understanding about the nature of the data?

I managed to make my select function work, but only by replaccing the T numbers with the longer words they were representing. I had to type a lot of words in!

Cheers

Craig

[Aleksi_Alkio]

Would you please give us the formula first you were using, thanks.

[Green_Gorilla]

Something like this:

=IFS(

[WidthRequired]=600, ANY(SELECT(Blinds[600], AND([Length]=[_ThisRow].[LengthRequired],CONTAINS([Colours],[_ThisRow].[ColourCode1])) )) )

Where [ColourCode1] is the data T1, T2, T5, etc etc.

[Aleksi_Alkio]

Try with…

IFS( [WidthRequired]=600, ANY(SELECT(Blinds[600], AND([Length]=[_ThisRow].[LengthRequired],CONTAINS([_ThisRow].[ColourCode1],[Colours])) )) )

[Green_Gorilla]

I managed to sort the problem, the long way around before I posted here… I used exactly the same formula but with words instead of T numbers to search for. It worked… so if youir suggestion

works, then order of the statement in the CONTAINS() instruction only applies in certain cases… strange!

[Aleksi_Alkio]

Or is it possible that in that record you had only one option and then it could find a solution bcause situation was like “Bruce” = “Bruce”?

[Green_Gorilla]

But each of the T numbers were unique…

… unless, there is some confusion between searching for T5 and T54, both of them containing the string T5…

… to test this, I made it search for T8017, which only occurred once. It still didn’t find it, and returned incorrect data…

[Aleksi_Alkio]

May I ask what is the app name and account ID? I can check the reason quickly if it’s okay for you.

[Aleksi_Alkio]

CONTAINS will find both of them for sure. If your [ColourCode1] field is an EnumList, it could be better idea to use IN expression instead of CONTAINS.

[Green_Gorilla]

The data is no longer in the app in the form where it doesn’t work. I changed it because I couldn’t get around the problem. You won’t see the problem in there now… Sorry.

I just posted in case it was a bug, or something. At least there are a couple of things I can try if it happens again…

[Aleksi_Alkio]

Gotcha