App to create cross-reference records

[agradisher]

Hi all,

I am looking to build a proof-of-concept in Power Apps for the following use case: 

We will be receiving data from two different sources. One data source will populate Table A (I am assuming this will be a Dataverse table). The other will populate Table B (also a Dataverse table). A user will use the Power App to review records from both tables, and match them based on whatever business logic/knowledge they have. The records should match one-to-one, and we would like the matched data to be stored in Table C as a cross-reference table. So the user should not be able to edit records from table A or B, but make edits to Table C should the cross-reference relationship change. I hope that all makes sense. 

From a UI perspective, I think it would be efficient if the user could see a list of Table A records, and a list of Table B records. Perhaps checking a checkbox on a List A record, and then checking a checkbox on a List B record, and then a Save button to create the Table C record. On save it then clears the checked boxes, so the user can do it again with other records. Not sure on this, open to ideas.

With this use case in mind, could you recommend what type of UI components and functionality I should look into using? I'm new to Power Apps so any ideas (or existing app templates) to get me started would be great. Thanks!

[FLMike · Accepted solution]

Hi @agradisher 

I would do this and you are close.

0. Add a Table for C, that has 2 Columns. Now.. this is where it gets tricky. Do you create a LookUp mapping table, where you have 2 LookUp Columns and you populate the looks up. OR do you have a table with 2 text fields and you store the GUID of each perspective A and B record.

NOTE: Its important to know, can multiple A be associated to Multiple B or 1:1 it makes a massive difference in design.

Its really a personal Choice. I personally hate building LookUp tables like that. But it can have some benefits depending on further UI's you make.

Let's Pretend that you created a LookUp.

Create Table C. Don't add the lookups add anything else you want

Go to Table A, Select the Relationship option for the table, Choose New and make a 1-M to Table C. Give the lookup a name. Now it will show up IN Table C

Do the same to Table B

Now you will have 2 lookup columns in Table C

Lets Pretend you called them TableALookUp and TableBLookUp

1. Create a new solution

2. Create a Canvas App

3. Go to the Datasource Tab and add your 3 tables

-now they will show up

NOTE: If you change the columns or schema at all, you will need to go into the App, click the datasource and choose Refresh (as it refreshes more than data)

4. Have a simple screen that has a Gallery of Table A, with as many of the fields as they need added to the Gallery

-Set the Items to NameOfTableA, if its has spaces 'Name Of TableA'

-Call it GalleryTableA

5. Create a Gallery of Table B again add whatever columns you need

-Do the same for items in B

-Call it GalleryTableB

Select a Row in A

Select a Row in B

have a button

OnSelect 

Create your record in C
Patch(TableC, Defaults(TableC), 
        { 
           TableALookUp: GalleryTableA.Selected,
           TableBLookUp: GalleryTableB.Selected
         }
);

Done

Thats an easy Dirty but fast way to get you going. Then you can customize to our hearts desire.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[Amik]

@agradisher - my two cents:

Dataverse does not support a one-to-one relationship cardinality, so some more context on your business scenario would have helped here. I would question the purpose of building a one-to-one relationship as opposed to joining both tables as one table. This will far more performant and will simplify your App significantly.

If there is a true business need to create a one-to-one relationship, and you only need to View the linked data as one table rather than create Table C, you can leverage a Canvas App or Model Driven App.

Use a Canvas App:

1. In Table B, create a LookUp column to Table A.

2. Create a View in Table B and include the related Column. In the View builder, filter that column to "Contains data".

3. Create a Canvas App

4. Add Gallery control and link the Items property to Table A (GalleryA)

5. Add another Gallery control and link the Items property to Table B (GalleryB)

6. Add another Gallery control and link the Items property to Table B and the View you created (GalleryC)

7. Add an EditForm control and link the DataSource property to Table B

8.  On the Item property of the EditForm control, enter: 

GalleryTableB.Selected

9. Ensure the LookUp column is included as a DataCard in the EditForm

10. Add logic on your Save/Submit button to ensure the Primary Column in Table does not exist more than once in Table B (CountRows is delegable with Dataverse). E.g.

CountRows(
    Filter(
        'Table B',
        'Your LookUp Column'.'Table A Unique Identifier Column' = DataCardValue1.Selected.'Table A Unique Identifier Column'
    )
)

You can then add Label controls in Gallery C to return data from both Table A and Table B. If you do need to create a separate table, you can leverage a combination of this approach together with the suggestion offered by @FLMike.

I would start with this first, prior to doing anything more complex like using Checkbox controls.

Use a Model Driven App

You can broadly achieve the same functionality as above in a Model Driven App by leveraging a real time workflow.

In Table A, create a LookUp column to create a one-to-many relationship from Table A (one) to Table B (many).

In Table B, create another LookUp column to create a many-to-one relationship from Table B (many) to Table A (one).

To prevent a record from being related to another record if a relationship has already been established, you can leverage a real-time workflow: 

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/configure-workflow-steps 

https://ideas.powerapps.com/d365community/idea/0efbff56-caad-42f6-94cf-79d6794b443b