cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ameeru18
Frequent Visitor

How do I combine multiple Dataverse tables into single gallery

Hello Power Community,

 

  • I am hoping for some detailed clarity other than use AddColumns.

    I am creating a Canvas App using Dataverse/CDS as the datasource. I have the following tables:
  • Projects  (Primary Key: Project ID) - Connections   Projects- 1:N -Tasks, Applications, Customers, AAD Users
  • Tasks       (Primary Key: Task ID)- Connections    Tasks- N:1 -Projects
  • Customers  (Primary Key: Customer ID) - Connections 1:N Projects
  • Applications  (Primary Key: Applications ID) - Connections  Applications Projects

 

  • Projects has a lookup column to AAD Users for our 365 user profiles and to Customers.
  • Tasks has a lookup column to Projects
  • Customers has a lookup column to Projects
  • Applications has a lookup column to Projects

 

I am trying to figure out how to create a single Gallery that pulls multiple columns from Tasks, Applications and Customers into Projects controlling delegation with some dropdowns that come from a lookup and choice column from the Projects and Tasks Tables.

 

I have no issue with creating galleries that reference another as in viewing specific data from Tasks by selecting something in Projects, but I am trying to create a new Grid view for sorting and search between the Galleries.

 

The below filter does not work.

 

Filter(
AddColumns(
'JBA Projects',
"Tasks",
LookUp('JBA Tasks','Project ID' = 'JBA Projects'[@'Project ID'].Value)
)
)

 

With this filter layout I do not get any suggestions after the last ]. Can anyone help with the correct Syntax to connect these tables? It would be much appreciated.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @ameeru18 ,

 

What is the filter condition?

 

You could try this formula to merge these two tables:

        AddColumns(
           'JBA Projects',
           "Tasks",
            Filter('JBA Tasks','JBA Tasks'[@'JBA Projects'].'Project ID' = 'JBA Projects'[@'Project ID'])
         )

 

Best Regards,

Bof

View solution in original post

3 REPLIES 3
v-bofeng-msft
Community Support
Community Support

Hi @ameeru18 ,

 

What is the filter condition?

 

You could try this formula to merge these two tables:

        AddColumns(
           'JBA Projects',
           "Tasks",
            Filter('JBA Tasks','JBA Tasks'[@'JBA Projects'].'Project ID' = 'JBA Projects'[@'Project ID'])
         )

 

Best Regards,

Bof

Thanks for the reply.

 

That filter statement doesn't work with my setup. I can combine tables easily by using AddColumn and LookUp with Collections, but a live datasource doesn't respond the same.

 

When using collections, Projectcol and Taskcol I can combine them by using the code below:

//Create a Combined Table
ClearCollect(
ColJoined
AddColumns(
ProjectsCol,
"Priority",
LookUp(TasksCol,cr577_ProjectID.'Project ID' = ProjectsCol[@cr577_projectid],cr577_jbapriority),
"Task",
LookUp(TasksCol,cr577_ProjectID.'Project ID' = ProjectsCol[@cr577_projectid],cr577_projecttask),
"Task Status",
LookUp(TasksCol,cr577_ProjectID.'Project ID' = ProjectsCol[@cr577_projectid],cr577_jbataskstate)
)
)

 

Once this is done I can use the information in the column I have connected as if it is part of a single table. I am trying to extend this functionality to multiple live Dataverse datasource tables. This allows me to use Filters to deal with the fact that my main table has over 3000 rows. With Collections I have to utilize buttons, views, and other trickery to allow access to all of the records since a Collection only consists of what it pull up to 2000 rows until it is reloaded under a different parameter again. This is fine for view using a smaller amount of our data, but when the users want to search everything it takes longer to recreate a Collection than it does to refresh a live datasource with a dropdown, checkbox, combo box, etc.

I see what the problem was with the Filter. For my case the Filter function needs to be used as a like the LookUp just like you have above, but it kept giving an error due to the "ID" being a record and therefore needing to be "looked into" with a "."

 

One of my working Filters, and this works for a Collection or straight from the Datasource:

Filter(TasksCol,cr577_ProjectID.'Project ID' = Gallery6.Selected.cr577_projectid)

 

Typical filter, but when calling upon cr577_ProjectID you need to put a "." to dig into the record before setting what it matches up with in the other table. Thank you for getting me to the finish line of this particular issue.

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,228)