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

Lookup Column Population

Hello,

 

I've just started getting my feet wet with Power Apps and am running into some roadblocks. I've created 2 entities (Projects and Status) which I loaded with data from Excel, then created a 1:N (Projects:Status) relationship between the tables. I do have matching keys between the entities but it appears that the lookup column that was created in Status is completely empty so I need to populate it.

 

Is the correct approach to create a table, then use a lookup function to find the matching key, then patch the missing data (something like LookUp(Projects, Projects.ProjectID = ThisItem.StatusID) followed by patching)? Did I do something incorrect when creating the relationship which prevented it from populating automatically? I realize I could manually do this by editing the entity in Excel, but it isn't possible to copy/paste the field value using the add-in and I have 50k+ records to update.

 

I've been struggling to get this lookup to work so any direction or confirmation that I'm going down the correct path would be appreciated.

 

Thanks.

3 ACCEPTED SOLUTIONS

Accepted Solutions
v-monli-msft
Community Support
Community Support

Hi @danderson2692

 

If you want to specify the lookup column use the GUID from the Employee entity on the Tests Entity when importing data, I afraid that there is no way to achieve your needs in PowerApps currently.

When importing data from Excel table into CDS Entity, there is no way to specify a lookup column on one Entity to use the GUID from another entity. In other words, there is no way to do the LookUp field match when importing data.

If you would like this feature to be added in PowerApps, please submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Ended up figuring this out. I created a gallery to display the items which didn't have the field populated using:

 

Filter(Status, Len(Projects.'Project Name') = 0)

 

 

Then the patch function was:

 

ForAll(
    RenameColumns(
        Gallery2_1.AllItems,
        "cre88_meta_status","StatusID"),
    Patch(
        Meta_PMStatusSummaries,
        LookUp(Status,cre88_meta_status=StatusID),
        {Projects:LookUp([@Projects],cre88_projectid = Label3_2.Text)}
    )
)

 

 

Note that I had to use Label3_2.Text rather than ThisItem.'Project Id' in the final lookup because I had the same issue as was described and solved in this thread: https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Patching-a-LookUp-field-The-right-s...

View solution in original post

Hi all,

 

One last quick update... I just found that I was trying to use the wrong column while editing in Excel so I could have avoided the trouble around filtering/patching/etc. Rather than using the (Lookup) column, you should dump the ID in the column without "(Lookup)" like I show below. Doing it this way works and populates the lookups after publishing.

DateIssue10.PNG

 

View solution in original post

4 REPLIES 4
v-monli-msft
Community Support
Community Support

Hi @danderson2692

 

If you want to specify the lookup column use the GUID from the Employee entity on the Tests Entity when importing data, I afraid that there is no way to achieve your needs in PowerApps currently.

When importing data from Excel table into CDS Entity, there is no way to specify a lookup column on one Entity to use the GUID from another entity. In other words, there is no way to do the LookUp field match when importing data.

If you would like this feature to be added in PowerApps, please submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thanks Mona, I appreciate the help!

 

Am I taking the correct approach by trying LookUp(Projects, Projects.ProjectID = ThisItem.StatusID,'ProjectName') to populate the values in the lookup column? To clarify, ProjectID and StatusID are the keys from my dataset, not the GUIDs of the entities.

 

Thanks again,

David

Ended up figuring this out. I created a gallery to display the items which didn't have the field populated using:

 

Filter(Status, Len(Projects.'Project Name') = 0)

 

 

Then the patch function was:

 

ForAll(
    RenameColumns(
        Gallery2_1.AllItems,
        "cre88_meta_status","StatusID"),
    Patch(
        Meta_PMStatusSummaries,
        LookUp(Status,cre88_meta_status=StatusID),
        {Projects:LookUp([@Projects],cre88_projectid = Label3_2.Text)}
    )
)

 

 

Note that I had to use Label3_2.Text rather than ThisItem.'Project Id' in the final lookup because I had the same issue as was described and solved in this thread: https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Patching-a-LookUp-field-The-right-s...

View solution in original post

Hi all,

 

One last quick update... I just found that I was trying to use the wrong column while editing in Excel so I could have avoided the trouble around filtering/patching/etc. Rather than using the (Lookup) column, you should dump the ID in the column without "(Lookup)" like I show below. Doing it this way works and populates the lookups after publishing.

DateIssue10.PNG

 

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,983)