cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JR-BejeweledOne
Super User
Super User

Create Collection with columns from 2 data sources

Wow, there are a LOT of possible solutions for this.   I haven't found the right one yet as most are either at one end or the other of what I need to do.

 

I need to create a collection of data that pulls all the detail information for a specific date from one collection and then add the master data to each record.

 

DataSource1 - MasterData:

 

ProjectName

ProjectType

CourseType

ID (Key field)

 

DataSource2 - Detail:

 

Title (Text field, This is a person)

Role

WeekEnding

Hours

MasterID (Key field)

 

I need to create a filtered collection of data from DataSource2 for a specific date and for each record that is found, add the corresponding data (ProjectName, ProjectType & CourseType) from DataSource1 using the ID-MasterID match.

 

The result:

 

Collection:

 

ProjectName

ProjectType

CourseType

Title

Role

WeekEnding

Hours

 

 

I found solutions for adding all columns and adding a single column but not for adding specific multiple columns.

 

Any help will be greatly appreciated.

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
2 ACCEPTED SOLUTIONS

Accepted Solutions
Alex_10
Memorable Member
Memorable Member

@JR-BejeweledOne 

 

didnt test it, should be like :

 

 

Clear(colDetailWithMasterData);
ForAll( Filter(Detail, yourColumn = yourdate) As ThisDetail,
    With({tempMasterData: LookUp(MasterData, ID = ThisDetail.MasterID )},
        Collect(colDetailWithMasterData,
        {
        'ProjectName': tempMasterData.ProjectName,
        .... etc for data taken from masterdata
        'Title': ThisDetail.Title,
        .... etc for data taken from Detail
        }
        )
    )
)

View solution in original post

I found a way to do what I was attempting.   I really didn't need the collection at all.  The only value it was providing was displaying the merged information from the two data sources.   It was only used for seeing what had been added today and providing a vehicle to edit the information.

 

Here is the Video I found:  https://www.youtube.com/watch?v=U-WN4OFZ1kw

 

And this ended up being my formula.  It works exactly as I wanted it to:

 

 

 

Filter(
                    AddColumns(
                    DemoWeeklyProjectHoursReporting,
                    "ProjectDetails",
                    LookUp(DemoProjectList,
                        ID = MasterID)
                    ),
                    SortDate = Text(DatePickerWeekEnding.SelectedDate)
            )

 

 

The Gallery fields are like this:

 

ThisItem.ProjectDetails.CourseType.Value

ThisItem.DevHours

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

View solution in original post

7 REPLIES 7
BCLS776
Super User
Super User

How about?:

ClearCollect(colCollection,Blank());
ForAll(DataSource2 As ItemTable,
    Collect(colCollection,
        {
            ProjectName: LookUp(DataSource1, ID = ItemTable.MasterID,ProjectName),
            ProjectType: LookUp(DataSource1, ID = ItemTable.MasterID,ProjectType),
            CourseType: LookUp(DataSource1, ID = ItemTable.MasterID,CourseType),            
            Title: ItemTable.Title,
            Role: ItemTable.Role,
            WeekEnding: ItemTable.WeekEnding,
            Hours: ItemTable.Hours
        }
    )
);
_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
Alex_10
Memorable Member
Memorable Member

@JR-BejeweledOne 

 

didnt test it, should be like :

 

 

Clear(colDetailWithMasterData);
ForAll( Filter(Detail, yourColumn = yourdate) As ThisDetail,
    With({tempMasterData: LookUp(MasterData, ID = ThisDetail.MasterID )},
        Collect(colDetailWithMasterData,
        {
        'ProjectName': tempMasterData.ProjectName,
        .... etc for data taken from masterdata
        'Title': ThisDetail.Title,
        .... etc for data taken from Detail
        }
        )
    )
)

Alex_10
Memorable Member
Memorable Member

@JR-BejeweledOne 

oops, you have got the answer already

This is great.    After thinking about it, I am wondering if this formula could be adapted to provide the same capability (displaying the related information) without creating a collection to do it?



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

With this kind of ForAll structure, you could Patch back to your DataSource2 and add columns to it.

 

Or, a ForAll can directly return a table, but it doesn't work to add columns that way.

 

Maybe an AddColumns with Lookup inside is what you are after?

 

I'm curious, why avoid making the collection?

 

 

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

It's not necessary to avoid the collection, it might make it a little easier on the updating side of things.   It was more curiosity than anything else as I work through the best way to keep the overhead down.



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

I found a way to do what I was attempting.   I really didn't need the collection at all.  The only value it was providing was displaying the merged information from the two data sources.   It was only used for seeing what had been added today and providing a vehicle to edit the information.

 

Here is the Video I found:  https://www.youtube.com/watch?v=U-WN4OFZ1kw

 

And this ended up being my formula.  It works exactly as I wanted it to:

 

 

 

Filter(
                    AddColumns(
                    DemoWeeklyProjectHoursReporting,
                    "ProjectDetails",
                    LookUp(DemoProjectList,
                        ID = MasterID)
                    ),
                    SortDate = Text(DatePickerWeekEnding.SelectedDate)
            )

 

 

The Gallery fields are like this:

 

ThisItem.ProjectDetails.CourseType.Value

ThisItem.DevHours

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,454)