Filter a Collection to match and compare

[Charl]

Hi

I have a Collection with title and color column. I want to filter and return the color for title but I want it to be dynamic. As the system admin's could add more projects and colors.

Collection:

I used a Switch to hardcode the effect I want but that's not practical as I don't want to

change code every time a project is added.

Firstly were do I add the code I have it in the Fill of a button control as I want the

project color to be set as the Fill of the button.

This is the statement I am trying but I can't figure out how to get the "Color" from the Collection

If(CountRows(Filter(ColColorOfProjects,title = ThisItem.Project.Value)) > 0,RGBA(209, 232, 178, 1),RGBA(0, 0, 0, 0))

Home it make sense and that's for your time.

C

[jcfDaniel]

Hi,

I am not sure I got your requirement but I built an example with a collection similar to yours:

Collect(

    colProjects,

    {

        Project: "The Testaments",

        Color: RGBA(150, 150, 150, 1)

    },

    {

        Project: "Cheque book",

        Color: RGBA(50, 150, 150, 1)

    },

    {

        Project: "The Overstory",

        Color: RGBA(150, 50, 150, 1)

    },

    {

        Project: "Come! Let's Run",

        Color: RGBA(150, 150, 50, 1)

    },

    {

        Project: "Spare",

        AutColorhor: RGBA(50, 50, 50, 1)

    }

)

And then used it in a gallery. The fill property is ThisItem.Color.

Also tested it in a text label:

The Fill on this one: 

LookUp( colProjects, Project = Self.Text ).Color

Is this helpful?

[BCBuizer · Accepted solution]

Hi @Charl ,

The values you have in the color column are not typed as a colors, but rather as a text, so you can't use them as such to give a color to any object in your app. To address this, you have two options: 1. use hard coded colors 2. use hex values as text and convert to a color using the ColorValue function.

For instance, using the below formula you can generate a collection that has both options in it: the color column has the actual color values (note the lack of double quotes) and the colorHex column has the hex values of the colors as text (note the use of double quotes):

ClearCollect(
    colColors,
    Table(
        {color: RGBA(255,0,0,1), colorHex: "#Ff0000", Title: "Red"},
        {color: RGBA(0,255,0,1), colorHex: "#00FF00", Title: "Green"},
        {color: RGBA(0,0,255,1), colorHex: "#0000ff", Title: "Blue"}
    )
)

Using hard coded colors

For the first method you can leverage this collection through the color column by using the below formula is the property of an object inside a gallery where you need to return a color through the below formula:

Coalesce(
	LookUp(colColors,Title = ThisItem.Title).color,
	RGBA(0, 0, 0, 0)
)

The result of this is:

The downside of the first method is that it will only work with hardcoded colors in the app, which excludes using an external data source. If you want to use an external data source, consider using the second method.

Using color hex values

This method leverages the ColorValue function by using the below formula is the property of an object inside a gallery where you need to return a color through the below formula:

Coalesce(
	ColorValue(LookUp(colColors,Title = ThisItem.Title).colorHex),
	RGBA(0, 0, 0, 0)
)

Again this results in a similar result:

[Charl]

Thank you @BCBuizer that was helpfull using the ColorValue and Hex instead of the RGBA colors..

[BCBuizer]

Hi @Charl ,

Has this issue been resolved? If so, please mark the post that was key to the solution by pressing the "Accept as Solution" button so it's easier to find for other members of the community.