Filtering in Canvas App

[ralphaj]

i have table called timesheet project which has a relation 1 to many to the table project task template. And project task template have a 1 to many relationship to the table task group and task group has 1 to many relationship to the table tasks. how can i make a filter in canvas app d365 to get the tasks record when i am selecting a project task template

[v-jefferni]

Hi @ralphaj ,

So, tasks table has a LookUp column to the task group table and task group table has a LookUp column to the project task template table. Since Dataverse does not support multi-level reference of LookUps, you need to get tasks level by level. Please try below method:

1. Use selected project task template GUID to filter in task group table:

Filter('task groups', 'project tasks templates'.'projecttasktemplate' = projecttasktemplatesCombobox.Selected.'projecttasktemplate')

This is a list of task groups that include selected project task templates.

2. Filter the tasks table to list tasks that included in the task groups list:

Filter(Tasks, 'Task Group'.'taskgroup' in 
Filter('Task Groups', 'project tasks templates'.'projecttasktemplate' = projecttasktemplatesCombobox.Selected.'projecttasktemplate').'taskgroup')

'projecttasktemplate' and 'taskgroup' are unique identifier GUID column in respective table.

Best regards,

[ralphaj]

I don't have a lookup in task group to the project task template.

In project task template i have a lookup on task group. and in task group i have the task related to the task group (task entity has a lookup field on task group)

[ralphaj]

Im trying this formula:

Filter(Tasks,'Task Group'.'Task Group (cr884_task_groupid)' in
Filter('Task Groups','Task Group'.'Task Group (cr884_task_groupid)'=ComboBox1.Selected.'Task Group'.'Task Group (cr884_task_groupid)'))

but i am getting error: invalid schema, expected a one column table

[v-jefferni]

Hi @ralphaj ,

So that means project task template table has a many-to-one relationship with task group table, and task group table has a one-to-many relationship with task table. If this is the case, when a project task template is selected in a Combo box Control, then it will be easily for you to get corresponding task group item GUID (if taskgroup column in project task template table only allows single select, otherwise it's a many-to-many relationship of the two tables):

projecttasktemplatesCombobox.Selected.'taskgroup'.'taskgroup'

With this taskgroup GUID, you can filter tasks table on the taskgroup LookUp column:

Edit:

Filter(Tasks, 'Task Group'.'Task Group (cr884_task_groupid)' = ComboBox1.Selected.'Task Group'.'Task Group (cr884_task_groupid)'))

Best regards,

[ralphaj]

I am not getting any data from this expression (Please check the screenshot).

But if i put this same expression in a gallery i get the record. Its very strange

[v-jefferni]

Hi @ralphaj ,

It's some sort of limitation of formula bar previewing data from Filter by GUID value of a LookUp column . You can try and update a variable to store the task group id of selected project task template OnChange of the Combo box:

Set(varTaskGroupID, ComboBox1.Selected.'Task Group'.'Task Group (cr884_task_groupid)')

then change the criteria as below:

Filter(Tasks, 'Task Group'.'Task Group (cr884_task_groupid)' = varTaskGroupID)

Best regards,

[ralphaj]

it is still giving blank value 😞

[v-jefferni]

Hi @ralphaj ,

As I said it's sort of limitation in formula bar to preview data. If you can use the formula to display records in a Gallery, then the formula is working. Do you want to display tasks in the Gallery?

Best regards,

[ralphaj]

The point of this is to clone (Patch) records to another table. I choose the project template then i click on submit.

ForAll(Filter(Tasks, 'Task Group'.'Task Group (cr884_task_groupid)' = varTaskGroupID),Patch(Tasks_1,Defaults(Tasks_1),{Subject:ThisRecord.'Task Name (cr884_task_name)'}))