Filter by Split(Cell Value) against a column from another table

[alde_zaw]

How can do this? 😥

Please help, thank youuu

[EddieE]

@alde_zaw 

OnSelect of your Department Column gallery item, use this code

Set( vChoiceCol, ThisItem.yourColumnName)

Then, on the gallery, set the Items to

With(
   {
      wData: 
      AddColumns( sourceTableName, "TextStr", Concat(sourceTableColumnName, Value))
   },
   Filter(wData, vChoiceCol in TextStr)
) 

Note: This only works on the first 2,000 records. There's no Delegation error but it is subject to the max Data Row limit of 2,000 records

[alde_zaw]

the flow is reverse not from the gallery, and I wanted to do it for multiple choices.

now I canged to single choice and put this  First(Collection).Colname in Split(SourceCol,","). in Gallery Items' Filter.

Is there another way around for multiple choices?

[EddieE]

@alde_zaw 

Sorry, I don't understand what you are trying to do. Can you please it explain it further.

[alde_zaw]

I created a choices gallery for multiple choices which values are stored in a Collection when clicked because the UI of combobox sucks, then I use these values in collection to filter the result gallery, (like showing results from filter).

That's where I was struggling because it was muiltple values, I don't know how to put conditions on those multiple values against the SourceTable coulmn, and one more thing is that the values in SourceTable column aren't single in one cell, sometimes more than one. 

So, instead of Multiple selected values in a collection, a single selection can be used to condition against this Source Column using First(Collection).Column in SourceTable.Column expression. 

I was wondering what to do if I still want multiple selections.

[EddieE]

@alde_zaw 

This is a little difficult to imagine without code and data but you could try filtering your Choice collection based on selections?

Eg, I assume your Choice collection has some sort of On/Off field eg 1 or 0? If so, you could filter the collection where the On/Off field = 1 (On) and then check that column against your Source Table column.

This is the basic idea, but as I said, difficult to give you specifics without code and/or data.

[alde_zaw]

Filter(CombinedTableForFilter,
        If(!IsEmpty(SelectedFilterProjectCategory), ProjectCategory.Value in SelectedFilterProjectCategory.ProjectCategory),
        If(!IsEmpty(SelectedFilterDepts), First(SelectedFilterDepts).Department in ForAll(Split(DIC_x002d_PIC,","), {Result: ThisRecord.Value}))
)

This one is for my ResultGallery, SelectedFilterProjectCategory  and SelectedFilterDepts are collections created when choices are made. 

The rows in the CombinedTableForFilter are the projects, and the choices with the ProjectCategory is fine because it's only one ProjectCategory for one project but each project has one or more Department-In-Charge/Person-In-Charge (DIC_x002d_PIC).  At this moment, I only made the Departments Filter only for single selection, but I'm wanting it to be Multiple chooseable. 

[EddieE]

@alde_zaw 

This was a reasonable challenge but I think I have a solution for you ... if a little hacky?

With(
    {
        wTable: AddColumns(
            CombinedTableForFilter,
            "ChoiceIsThere",
            CountRows(
                Filter(
                    AddColumns(
                        DIC_x002d_PIC,
                        "CheckChoice",
                        Value in SelectedFilterDepts.Department
                    ),
                    CheckChoice = true
                )
            )
        )
    },
    Filter(
        wTable,
          If(
            !IsEmpty(SelectedFilterProjectCategory), 
            ProjectCategory.Value in SelectedFilterProjectCategory.ProjectCategory
          ),
          // checks for choice in the FilterDepts collection
          If(
            !IsEmpty(SelectedFilterDepts), 
            ChoiceIsThere > 0
          )
    )
)

Note: this isn't delegable and is also subject to the Data row limit - meaning it will only work on the first 2,000 records from your data source.

If you need a delegable option, then this method designed by @RezaDorrani using a Flexible Height gallery and the visible property of controls will work for you as well.

https://www.youtube.com/watch?v=44j2VRbdWjk&t=655s

(Reza talks about the bits you need around the 10-12min mark)