cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JasonF
Level: Powered On

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
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."

JasonF
Level: Powered On

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.

SeanKelly
Level: Power Up

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..
JasonF
Level: Powered On

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.

Edwin-Abdalian
Level 10

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!

JasonF
Level: Powered On

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

Edwin-Abdalian
Level 10

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.

JasonF
Level: Powered On

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

Edwin-Abdalian
Level 10

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
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (4,741)