How do I connect tables in Dataverse? What's the difference between lookup columns and unique identifier columns?

[apangeles]

Hi, everyone, 

I'm currently creating a supplier portal for our company and a lot of information is needed from our vendors. I'm eyeing using the different OOTB tables because there are parts of our application where we need multiple entries from them (like addresses, accounts, contacts, etc.). In the case of addresses, How do I connect the information from separate tables like the mentioned addresses, accounts, and contacts tables? 

I assume that the following tables should store the following information:

1. Contact = login user, contacts from the vendors, any persons related to the vendors

2. Accounts = company information (I'm thinking of placing the addresses here but placing the multiple address entries would mean multiple companies, so I decided to use the addresses table)

3. Addresses = multiple company addresses like main office, subsidiaries, warehouses, etc. 

^ how do I connect these three for when the vendor checks their profile, it can be viewed on one page?

Another important question:

For lookup columns, what necessarily does it look up? Is it looking up the primary name of the table being looked up? If so, which column it is comparing to my original table? For example, I have a lookup in the contacts table to the accounts table, how does the system know which columns are being matched? 

[Manar]

Hi,

1- You will need to ensure the 3 tables are connected via 2 relationships 1:N (Contact > Account) and (Account> Address) 

2- Create Quick View form for Account that has only Address subgrid to display only associated addresses to the account

3- Add the Account Quick form to Contact profile and check the result in dataverse (Dynamics 365 or model driven app) 

if you got the desired outcome, you should be able to render same form (#3) using basic form in portal

[Fubar]

Contact = Person (may not just be for vendors), Portal User is also represented as a Contact

Account = Organisation

for HQ etc you often would just set the Parent Account field of the child to the HQ etc (actual structure may depend on exactly what you need but this would be the usual way to represent a HQ and subsidiary branches etc ) 

Out of the box there are existing relationships between Contact and Account (1:N, and also a Primary Contact field on the Account), and as mentioned Account can have a parent Account.

Lookup is a field that behind the scenes will hold the internal id (guid) of a parent entity - in db speak it is a foreign key.  The Lookup field is linked by the internal guid, but will display the Primary Name field of the parent record (this behavior is not able to be changed). When you start typing into a Lookup it used to use the Quick Find /Search columns defined on the out of the box Quick Find View. 

[apangeles]

Hi @Manar, amazing tips on how to do this, however, I'm trying but struggling with the quick view forms and subgrids.

Correct me if I'm wrong, but I'm going to use quick view forms rather than main forms to present subgrids, correct? 

About the connections, how do I create contacts and relate those contacts automatically to an account? Is this possible? Because for example, the user would input multiple contacts associated to an account, he/she would always put the company/account name in that line item. Is there any way to do this automatically? 

[apangeles]

Quoting this, 

" for HQ etc you often would just set the Parent Account field of the child to the HQ etc (actual structure may depend on exactly what you need but this would be the usual way to represent a HQ and subsidiary branches etc ) "

Where will the information about subsidiaries, warehouses, etc. live? Also in the Accounts table?

[Manar]

Hi, glad to help.

the connection should automatic between contact and account ,and if you can open an account and see its related addresses then it does have relationship already (good news).

now because “Address” is not connected directly to "Contact" then you need to do it via "Quick View"

1- Open Accounts and create new "Quick Form" and add Addresses subgrid to it

2- Open Contact main form (any main form you want) then add the #1 quick view to it