Dataverse Join Newbie Question - Power App Project Tracker

[todaytoo]

I am having trouble understanding joins in dataverse and hoping someone can help me connect the dots. I have a few tables that I am going to be using to track project capacity.

My main table will have the project id and project name - I am running into issues with how dataverse will work to tie the multiple records in the Capacity Tracker Note table back to the Capacity tracker main table if there are no PK/FK to tie the records together?

Can someone help explain this to me? Once I created my 4 custom tables, I did a lookup on the Resource, Detail, Note table back to the Main table to create the Joins. In Power App I can create a new record in a form in the Tracker Main table, but I cannot create a new entry in the note table once the project id is selected in the form or gallery. 

What ID / lookup would I use for this?

Any help is appreciated.

[AhmedSalih]

Hello, @todaytoo , you have to add SubGrid into the The Tracker Main table and assigned it to the Related records of your Note Table. 

Lisa has this Crosbie great tutorial about subgrids: https://www.youtube.com/watch?v=baRLW8bak9Q

If my reply helped you, please give a 👍, & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

[dpoggemann]

Hi @todaytoo ,

When you add a Lookup field to the related tables that is from the table of your parent you are creating that foreign key relationship.  Behind the scenes it creates a GUID of the ID from your Tracker Main table on the Tracker Note table as an example.  The lookup field provides a friendly way to update this relationship on your child table by selecting the appropriate parent record.  

Are you utilizing a model app or a canvas app to create the records here?  if a model app it would be fairly straight-forward to create your parent "Tracker Main" table and then you can add tabs in the form designer (or subgrids on the main form) to create your related entries for your other tables.  When you do this from the main table (as a subgrid) it will automatically populate the parent "Tracker Main" relationship on the record as well.  Please let me know if this is confusing, and I can try to explain better.

[todaytoo]

Hello @dpoggemann ,

Thanks for helping. I am using a canvas app. The part that is still not connecting the dots for me is:

I have the 2 tables, Tracker Main and Tracker Note. I have them both created in dataverse, then I created the additional lookup column on the Tracker Note table to complete the join. But now, as I am building out the canvas app, what functions do I use to add the notes into the Tracker Note table and keep it related to the Tracker Main table? I read the GUID does that, but where in the code do I reference this?

For example, I have Gallery 1 that will populate all of the projects listed in Tracker Main table, once selected I want a corresponding Form 1 to be used to submit a note that will be entered against that project. There could be many notes against any given project. 

What is not connecting the dots for me is how do I reference the data as I am inserting records into the Tracker Note table and then how do I then correlate the 2 tables so I can have the form populate gallery1.selected and know that all of those records belong to that specific project id, when I am not passing any keys or project id between the tables?

[AhmedSalih]

Hello, @todaytoo, to achieve your requirement in Canvas App:

1. You will call a form on a New Mode. The form Data Source will be the Tracker Note Table.
2. All the Form fields will be empty because the Form is in a new Mode, except the Lookup column Field, this one should has the value of the Gallery1.Selected in its DefaultSelectedItems Property.
3. So, you should allow the New Form to appear if there is a Selected Record in the Gallery1. 
4. To do this, you can Disable the Button that user click to create new Note except when Gallery1  has a selected record. On that button DisplayMode property type this formula: If(IsBlank(Gallery1.Selected), DisplayMode.Disapled, DisplayMode.Edit) 

If my reply helped you, please give a 👍, & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

[todaytoo]

Thank you! I still cannot fully get this concept. 

Following your instructions, I have a few questions I am hoping you can answer. When I set up Form1, there is not a DefaultSelectedItems on the Form level, only on the actual data card so I set that value in the DataCardPR (lookup column). I would not want the lookup column to show in the form, only the comments section. When I select an item from the Gallery1, I was assuming the DefaultSelectedItem would populate the project ID from the Gallery1 into the Form1 lookup column field (PR in my case), which I then would submit with the form to the note table? Then I could create another Gallery where the items would be set to TrakerMain.PR = TrackerNote.PR. Is this correct thinking on how the relationships in dataverse is working, or do I not even need to be passing the project id from the TrackerMain table to the TrackerNote table in order to relate the rows? 

Gallery1

• Items = Tracker Main (Main data source)

Form1

• DataSource = Tracker Note
• Items = Blank()
• DataCardPR = this is the lookup column in the Tracker Note table, it defaults to a dropdown
• Items = Choices(Tracker Notes. PR)
• DefaultSelectedItems = Gallery1.Selected
• DataCardComment
• This is where new notes will be entered

[AhmedSalih · Accepted solution]

@todaytoo, 

"When I select an item from the Gallery1, I was assuming the DefaultSelectedItem would populate the project ID from the Gallery1 into the Form1 lookup column field (PR in my case), which I then would submit with the form to the note table? "  << That is Correct

To not show the Form1 lookup column field, have its visible property as false. 

"I do not even need to be passing the project id from the TrackerMain table to the TrackerNote table in order to relate the rows? " << That is Correct

The relationship already established by passing the Gallery1.Selected into the Lookup Column in the Form. The new created Note will be associated with eh Project you selected in the Gallery1 in the Dataverse.

If my reply helped you, please give a 👍, & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com