Check value of choice column in multiple tables with one-to-many relation.

(0) Share

Report

Posted on by BradonK

375

Hi all,

I am just starting out learning Dataverse and need some pointers.

I have a table called 'Tasks' which contains the general information about a task. The task table has a task number as a primary column. There is a separate column called 'Categories' which is a choice column allowing you to select the categories required for this task.

The task is then split up into categories such as drawings, certs, etc. Each category has it's own table.

Each category table has a lookup column that points to the parent task number.

Each category table also has a column called status. There are three values; 'Incomplete', 'Needs Attention', and 'Complete'.

In Power Apps I have a gallery that gets it's items from the 'Categories' choice column on the 'Task' table.

In the gallery template there is a circle shape that I want to change colour to reflect the categories status value.

How do I, for each category in the gallery, lookup the relevant table, then the relevant status column, and use the value to change the color.

I tried using a table as the data source for the gallery that looked like this:

Table({label: "Drawings", table: 'Task Data'})

Also, the 'Tasks' table has a one-to-many relationship with the other tables, so I can have multiple records in a category table linked to one task. The idea is that if any of the records in a category are 'Incomplete', color the status red. If none are 'Incomplete' but some are 'Needs Attention', color it yellow. And if all are complete, green.

Appreciate any help on this.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Shaheer Ahmad 2,194 Moderator on at

Like (0)

Report
Hello Brother,
Kindly try the below approach:

Create a gallery in Power Apps and set its Items property to the 'Categories' choice column of the 'Tasks' table.
Inside the gallery, add a circle shape control and set its Fill property to a formula that determines the color based on the status of the corresponding category.
You can use the LookUp function to find the relevant table based on the category and then access the status column. The LookUp function allows you to search for a record in a table that meets specific criteria.
Here's a formula that you can use for the Fill property of the circle shape control:
If( LookUp(DrawingsTable, ParentTaskNumber = ThisItem.TaskNumber).Status = "Incomplete", Red, If( CountIf(DrawingsTable, ParentTaskNumber = ThisItem.TaskNumber And Status = "Needs Attention") > 0, Yellow, Green ) )
Customize the gallery and circle shape control further based on your specific design requirements.

Was this reply helpful? Yes No
BradonK 375 on at

Like (0)

Report
Thanks for the reply @ShaheerAhmad,

Unfortunately that did not work.
Since there can also be more than one record that is incomplete, I can't use LookUp.
This is the formula I ended up with but still does not work.

If(CountIf(ThisItem.table, galTaskList.Selected.'Task Number' = 'Task Number'.'Task Number' And 'Status (bk_status)' = "Incomplete") > 0, Color.DarkRed)

I get an error on the equal sign between 'Status (bk_status)' and "Incomplete" that says:
"Incompatible types for comparison. These types can't be compared: OptionSetValue (MDR Category Status), Text"

'bk_status' is the logical name for my status column but it still gets that error.

Thanks

Was this reply helpful? Yes No
Verified answer

BradonK 375 on at

Like (0)

Report
After moving on to something completely different that also happened to use the status column, I accidentally found a solution.

Simply wrapping the 'Status (bl_status)' field in a Text() function let me compare the two.

The new function looks like this:

If(CountIf(ThisItem.table, galTaskList.Selected.'Task Number' = 'Task Number'.'Task Number' And Text('Status (bk_status)') = "Incomplete") > 0, Color.DarkRed)

Was this reply helpful? Yes No