Skip to main content

Notifications

Power Apps - Microsoft Dataverse
Answered

Dataverse Lookup Tables and Canvas Apps Forms - How to reference non-primary column

(1) ShareShare
ReportReport
Posted on by 76

Hi Everyone,

 

Im really struggling with this. Ive been using Canvas app related to SharePoint tables. But Im developing more of a database structure using related tables in Dataverse and Im now trying to build a canvas app for the user experience.

 

But Im struggling with something that seems like it should be possible. 

 

One of my tables has a lookup column to a related table. 

 

In my Canvas app, when using a "New" Form, how do I reference a column, from that related lookup table that is NOT the primary key column? 

 

So for example if my lookup is to a user table, and the primary key is "Name" how would I create a form in Canvas apps that allows me to enter information into a non-primary key column, such as email?

 

Better still, if one of the non-primary columns in the related table is a choice column, how do I reference that in the edit form?

Categories:
  • Ami K Profile Picture
    Ami K 15,655 on at
    Re: Dataverse Lookup Tables and Canvas Apps Forms - How to reference non-primary column

    @Paultys  you're welcome!

  • Paultys Profile Picture
    Paultys 76 on at
    Re: Dataverse Lookup Tables and Canvas Apps Forms - How to reference non-primary column

    @Amik thank you so much. I was trying all sorts of codes and formulas and couldn't get there. Simple when you know how. I tried all sorts of Google searches too and nothing took me to an answer. Im always amazed and very grateful that people give up their time to help others on this platform. Really thank you so much.

  • Verified answer
    Ami K Profile Picture
    Ami K 15,655 on at
    Re: Dataverse Lookup Tables and Canvas Apps Forms - How to reference non-primary column

    Hi @Paultys ,

    To understand how this works, let’s use a basic Form as an example. 

     

    Suppose we have a Table with a list of Issues, and another Table with a list of associated Actions. The Primary Name column in the Issues Table is Record ID.

     

    In the Actions table, we create a Lookup column associated to the Record ID column in the Issues Table. We will call the Lookup column “Related Issue” (a one-to-many relationship).

     

    1. Create a auto generated three screen app based on the Dataverse Actions Table.

     

    Amik_0-1684958489182.png


    2. Add the Lookup field into the Edit Screen of the Form if it not there already. You will notice Power Apps will automatically create a Combo Box control for the Data Card Value.

    3. Note the Choices expression used in the Items property of the Data Card Value, it is using our relationship Lookup column ("Related Issue").

     

    Amik_1-1684958559426.png

     

    4. If you select Edit on the Combo Box properties pane on Fields, you will notice you can select any related field from the Issues table.

     

    Amik_2-1684958582091.png

    5. Suppose we want to include a Single Line Text column from the Issues table. Add a custom card and include a Label control inside it.

     

    Amik_3-1684958623211.png

     

    6. In the Text property of the Label control, enter ThisItem.'Your Lookup Column Name' followed by a period/full stop.

     

    Amik_4-1684958656969.png

     

    7. At the period sign, notice you can now select any column from the related Issues table

    8. Final result:

     

    Amik_5-1684958703789.png

     

    9. Suppose we now want to include a Choice field from the related table. Add another Custom Card and insert a Combo Box control into the Data Card

    10. In the Items property of the Combo Box control, enter the following: Choices('Sample Issue Datas'[

     

    11. Notice after entering the left bracket, all Choice columns associated to the Issues table are available for selection.

    Amik_7-1684958807501.png

     

    12. Final Result:

     

    Amik_9-1684958840232.png

     

     

    I hope this walkthrough gives you a steer on how all this works. @Shanescows also has a wonderful walkthrough on this here:

     

    https://www.youtube.com/watch?v=BPkv7S11Zp8&t=1603s 

     

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

     

    If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution

    If you like my response, please give it a Thumbs Up.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,526

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,907

Leaderboard

Featured topics