I have two table that I want to combine to create chart
Table Structure is as follows:
And I would like a collect of be able to create another table with this:
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?
I will have to make a few assumptions before answering your question.
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."
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.
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.
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!
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:
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.
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.
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.
Read the announcement for more information!
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Features releasing from October 2019 through March 2020
Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications