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?
@Paultys you're welcome!
@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.
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.
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").
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.
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.
6. In the Text property of the Label control, enter ThisItem.'Your Lookup Column Name' followed by a period/full stop.
7. At the period sign, notice you can now select any column from the related Issues table
8. Final result:
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.
12. Final Result:
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.