Unanswered

Comparing Comma Separated Text Field Values to values in a Collection during Filter

(0) Share

Report

Posted on by SR-30011133-0

Hi folks,

I have a column in my source table that has a text string of values e.g. "A,B,C". In my app I have a collection which I'd like to search when I filter my data to see if any value of the column ("A", "B" or "C" in this case) exists in the collection.

If creating a Choices column or similar simplifies this, happy to know how to amend my import data (currently a single column in Excel as "A, B, C" etc) to successfully import into a Choices column.

I tested some logic on a button which worked OK but having issues embedding this in my Items formula as part of the Filter function:

ForAll(Split("A,B, C", ","), If(Value in colMatchValues, Notify("Value Exists"), Notify("Value Does Not Exist")))

Any help or ideas on how best to approach this?

EDIT: I've amended the topic title to better reflect the issue hopefully!

Many thanks

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (0)

ivan_apps 2,187 Moderator on at

Like (0)

Report

You can load your Collection with Split("your string", ",")
When you search, you can do a Filter(yourCollection, Value = [your search term]) to return your criteria.

Was this reply helpful? Yes No
SR-30011133-0 on at

Like (0)

Report

Thanks for your reply @ivan_apps. Apologies for not getting back sooner as I was at an event yesterday.

Note I already have a collection holding the values I wish to filter by - let's say it only contains the value "B". My Dataverse table has a column with comma-separated values. e.g. "A","B","C". I'd like to check this column as part of the Filter to check if any of the column-separated values appears in my collection. In the case of "A","B","C" that would be fine as it contains "B" but "X","Y","Z" should fail the check and its row not returned by the Filter.

As per my initial post, happy to convert the column to a Choice column if that will make my life easier interacting with the data in Power Apps - but would like to know if I'm creating the table from an Excel file via import, how would I present the selected values in Excel e.g. would "A"; "B";"C" be understood by import for example?

But happy to leave the data in its current form if I can get the Filter logic to work.

Was this reply helpful? Yes No
SR-30011133-0 on at

Like (1)

Report

UPDATE: I've since worked out how to import my data as Choices data (it likes "A","B","C" etc. and determines all the unique choices and maps them to an editable value as part of the import process) - so I'll look to see if that makes the Filter easier to write (I now essentially want any selected choice(s) for any given row to appears in my collection to mean I include that record)

Was this reply helpful? Yes No
SR-30011133-0 on at

Like (0)

Report

OK, so I'm got it to work with a Choice column in my Dataverse table using the logic below:

Filter('MyTable' As t,
true in
ForAll(myCollection As c, c.'Valid Choice ID' in Concat(t.'MyChoices', Value & ",")))

However, I get a delegation warning for this noting my data source is a Dataverse table. My result set is never going to exceed my delegation limit but I'd still like to understand if there is a better way.

Any ideas?

Incidentally, I wrestled with this for a while as when I imported my data from Excel into my new table and amended my Choice column, under Choices it showed "Label" (the value being imported) and Value (a unique ID presumably). The list of choices correctly identified all distinct values in the file (I added dummy rows to ensure I had all acceptable values covered because I didn't fancy entering these one by one or via flow) - but as you can see from the code "Value" returned the imported choice, not "Label" (neither of these names I was able to change in the Edit Column screen). That was hugely ambiguous!

Also, after import, the record count was wrong (in the Table details screen) until I published the table - another curiosity that slowed me down! I think I need therapy after using that import process! So many quirks to be wary of. 😬

The data imported for the Choice column was comma-separated as this seemed to be what the import process coped with for a Choice column.

It's essentially a Many-to-many relationship so I could go down that path if I really need to.

Was this reply helpful? Yes No
ivan_apps 2,187 Moderator on at

Like (0)

Report

Take a look at this video, I think you can use this to resolve that delegation issue: https://youtu.be/44j2VRbdWjk?si=bh24G6jXl0HIqMln

basically you can use a flexible height gallery to show/hide rows instead of filtering them out. To the end-user the result looks the same.

Was this reply helpful? Yes No
SR-30011133-0 on at

Like (0)

Report

Thanks @ivan_apps and apologies for taking so long to get back - this issue has consumed me 😀

I had seen that video before but not considered it as I had though delegation issues would be reduced as I was using a Dataverse data source. As it appears not to be the case (I can only assume Enhanced delegation for Dataverse" is now baked in and not an upcoming feature as it's gone from the latter in Settings?) I re-visited this as a workaround and was dismayed to hit another issue that rendered it unreliable.

What happened to me was that whilst it initially appeared to work, after interacting with the combo box that influences the Visible property of the gallery a bit, rows were not being reliably shown. Iterating through the gallery programmatically indicated that the correct number of records were set as Visible but fewer than that number were actually displayed. I had two text labels , both set with Auto Height enabled. The Visible property runs a formula that determines whether the row should show or not (it loops through the list of values in the Choices column and checks to see if any (and there can be several) are found in the combo box's selected items.

I did note that setting Template Padding to anything but 0 left gaps so ensured this was zero.

EDIT: I forgot to mention if when back in the editor when the app displayed fewer rows than expected, if I changed the Template Size from its value of 40 to 41 and back again to 40, the missing rows appeared in the editor window! 🤔

I'd rather not have to fudge this - I struggle to come to terms to why when using Dataverse I can't seem to check whether any of the selected choices in a Choices column appear in a combo box/collection without a delegation warning as that seems a pretty common scenario. I've tried it with a column that's just Text instead of Choices (so holds "A","C" for example) but that doesn't help me either.

Would using a many-to-many relationship help? I've set one up but not related anything yet as I'm reticent to engineer that only to hit the same issue - not what the Filter should look like.

For example, Table 1 = Student with Student Name & Classes (one or many Class IDs). Table 2 = Class with Class ID & Class Name.

Student Jack attends Class ID's 1, 3 & 5 & Student Jill attends Class ID's 2, 4 & 5.

Combo Box/Collection has 1, 2, 3, 4, 5 to filter the above Students.

Was this reply helpful? Yes No