cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Is there a way to collect data from two tables with a relationship into one table.

I have two table that I want to combine to create chart

Table Structure is as follows:

collect model.JPG

And I would like a collect of be able to  create another table with this:

collect Result.JPG

So basically I would like all the sales detail with the associated company. Is there a way to path this or collect this into one table?

 

Regards,

 

9 REPLIES 9
Highlighted
Super User
Super User

Re: Is there a way to collect data from two tables with a relationship into one table.

@JasonF 

I will have to make a few assumptions before answering your question.

 

  1. You datasource is a Sharepoint List
  2. Your column types are all text and number fields

 

Avoiding use of the LookUp function in Sharepoint makes this pretty-straightforward.  Here's the code to join both tables.

 

ClearCollect(
    myJoinedCollection,
    AddColumns(
        'Sale Data',
        "Company Name",
        LookUp('Daily Reports',ReportNo = 'Sale Data'[@ReportNo])
    )
)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Helper III
Helper III

Re: Is there a way to collect data from two tables with a relationship into one table.

Dear mdevaney,

First, I am so thankful for you taking the time to answer my novice questions.

Next, I actually tried this from from the research I did and got invalid argument on the lookup part of function ('Daily Reports',ReportNo ='Sales Data'[@ReportNo]). I also just reformatted everything to match your's exactly and got the same error.

ClearCollect(myJoinedCollection,AddColumns('Sales Data',"Company Name",LookUp('Daily Reports',ReportNo ='Sales Data'[@ReportNo])))

What you need to know is that these are CDS tables with a relationship defined on ReportNo ('Daily Reports'.ReportNo O>>>M 'Sales Data'.ReportNo). So the ReportNo in 'Sales Data' is looking up ReportNo in 'Daily Reports'

I also tried:

ClearCollect(myJoinedCollection,AddColumns('Sales Data',"Company Name",LookUp('Daily Reports',ReportNo ='Sales Data'[@ReportNo.ReportNo])))

With no success. I know it has something to do with the fact that ReportNo in 'Sales Data' is a lookup to 'Daily Reports', but at a road block as to what I am doing wrong.

Highlighted
New Member

Re: Is there a way to collect data from two tables with a relationship into one table.

Hi Jason, if Company Name is a text field on the parent entity, another option would be for you to create a calculated field on the child entity and enter the action: ReportNo.Company Name. Then you could just use that field in your report..
Highlighted
Helper III
Helper III

Re: Is there a way to collect data from two tables with a relationship into one table.

Thank you for your suggestion, however the 'Company Name" on the parent (Daily Report) is lookup to a different parent called 'Divisions'. I will try your idea as a work around, but my thinking is the whole idea of Relationships is to avoid these kind of work arounds. This would so much easier with an SQL statement.

Highlighted
Solution Supplier
Solution Supplier

Re: Is there a way to collect data from two tables with a relationship into one table.

you just need to compare the GUIDs of the child entity to the parent entity. try this:

in this example, EntityB is my parent, and EntityA is my child entity. EntityB 0 -->M EntityA and A has a lookup column called OfficeLookup to B. I want to add the 'Address 1' column from B to my collection

ClearCollect(colMyJoinCDS, AddColumns(EntityA, "Address", LookUp('EntityB', EntityB = EntityA[@'Office Lookup'].EntityB,'Address 1')));

The GUID column name of each entity is the name of that entity, highlighted in orange

So in your case

ClearCollect(myJoinedCollection,AddColumns('Sales Data',"Company Name",LookUp('Daily Reports','Daily Reports' ='Sales Data'[@ReportNo].'Daily Reports',ReportNo)))

 This is the only way that I have been able to get the data in my projects. but If you only want to display the data in a DataTable or a Gallery, you don't need to do all these things. just use ThisItem.'Your LookUp Column Name'.'Column Name'

--------------------------------------------------------------------

Please Accept as Solution if this post answered your question so other members can find it. If you found this post helpful consider giving my post a Thumbs Up!

Highlighted
Helper III
Helper III

Re: Is there a way to collect data from two tables with a relationship into one table.

Ok, this all looks perfect, and I can even follow the logic, but I am still getting a "invalid arguement" error. See below screen shot. Just to clear the actual name of the "Sales Data" is "Sales Detail" in the CDS entity. I just wrote down the wrong name on the little data model I drew. 

Here is the actual screen shoot from the test app:

collect problem.JPG

Highlighted
Solution Supplier
Solution Supplier

Re: Is there a way to collect data from two tables with a relationship into one table.

you have two entities 'Sales Detail' and 'Sale Details' . I think you are confusing them, you mentioned that your sales data is in 'Sales Detail' but you are using the 'Sales Details' in your formula.

Highlighted
Helper III
Helper III

Re: Is there a way to collect data from two tables with a relationship into one table.

Thanks Edwin, but that was just a typo. The Entity is "Sales Details" and "Sales Data" does not exist other that the data model example i provided, which should have been labeled "Sales Details". Also still have "Invalid Arguement" on this function.

formedit.JPG

Highlighted
Solution Supplier
Solution Supplier

Re: Is there a way to collect data from two tables with a relationship into one table.

the screenshot that you had sent, had both with "s" and without "s" entities, that's why I thought you are using the wrong one. other than that I can't tell what's happening there. It has always worked fine for me this way, and I don't get the error.

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,662)