Creating relationship between external imported table (dataflow) and dataverse table

[Sierfruit]

Hi everyone,

I have a hard time figuring out the following scenario:

TABLE 1: a table coming from SQL which has it's own primary key, imported with dataflow into dataverse.

TABLE 2: a dataverse table which contains a column to reference to the primary key in TABLE 1

I want to create a one-to-many relationship, with TABLE 2 (dataverse) on the one side, TABLE 1 (sql) on the many side. If I create a record in the dataverse table (TABLE 2), it should then be able to show matching records coming from SQL (TABLE 1).

Looked like something easy to do but I have been struggling to find a solution. I have tried creating alternative keys to make this relationship but can't seem to choose on which key the relationship should work. I'm I looking at this in the wrong way?

Hope you can give me some guidance. Thanks in advance! 🙂

[gulshankhurana]

Hi @Sierfruit 

If it's a model driven app, you simply need to create a lookup field/column on the Form for Table 1. That lookup field should be pointing at Table 2. This will automatically create 1:n relationship between Table 2:Table 1. 

In Table 2 form, you can then insert a subgrid pointing at Table 1. This way, you will be able to see all related Table 1 records on Table 2's form.

I hope this information helps. If you appreciate my efforts then please give it a thumbs up and if it helps resolve your query then please also accept it as a solution.

Kind regards

Gulshan 

[Sierfruit]

Hi Gulshan, 

Thanks for the quick reply but that is not really what I want to do.

I want to be able to create that lookup table with a key other then the dataverse ID. So I have a column called 'SampleNumber' in the SQL imported table that I want to see showing up when choosing a value in the lookup table instead of these:

In this way the user can fill in the samplenumber and create the relationship between the two tables.

Thanks for the help!

[parvezghumra]

Hi @Sierfruit

As @gulshankhurana has advised, standard lookups (foreign keys) in Dataverse work on the basis of the system-generated, GUID-based primary record unique identifier. So the foreign key column on your secondary table will hold a GUID value which references the unique record identifier of the target record in your primary table.

But this does not mean to say this GUID needs to be exposed to the user in the application. You can additionally configure more user-friendly, human-readable and meaningful record identifier columns on each table. The data type of these could be plain text and you can apply an alternate key on such a column to enforce uniqueness. 

Additionally you can design your app such that it's this more human-readable unique identifier that is exposed to the user and used in the selection, searching, browsing and referencing of records.

Hope this helps 😀

[Sierfruit]

Hi Parvez,

Thank you for the reply 🙂

I'm new to this so sorry if I'm seeing it wrong. I think I understand what you are saying but I don't see how to do the following in this way:

I have a stock table where I have a column 'Sample number' and the lookuptable 'Sample':

This stock table is the 'one-side' of the relation. 

The user fills in the sample number in this stock table. With this sample number I have to make a relation to an existing SQL Table, the many side, to show more information (coming from our lab system) about this sample number in our stock system.

This is the table coming from SQL, which has the 'Number' column that needs to be filled in the Stock table by the user (called 'Sample number').

I don't get how to link the two tables by only having the user filling in the sample number in the stock table. The GUID  of that record does not exist in the already existing SQL table. 

Hope this clarifies my problem a bit more. 🙂 Thanks again for your answer!

[parvezghumra]

@Sierfruit Are you not planning to recreate your SQL table(s) in Dataverse as native Dataverse tables, with the data from the SQL tables being migrated to Dataverse and the SQL database being decommissioned at some point? 

What type of app are you expecting your users to use? A Canvas App or a Model Driven App?

[Sierfruit]

Indeed, the SQL table comes from a productional database that runs the lab system (LIMS). So there is no option to migrate.

I would like to build a model driven app. But if that's not possible for this situation then a canvas is also an option, or a combination?

Thanks again!

[Sierfruit]

@parvezghumra Any thoughts on how to proceed? 🙂

[parvezghumra]

@Sierfruit If you're not looking to replace your SQL table (over time), you probably need to consider somehow surfacing the data that is in your SQL table into Dataverse (perhaps using a Virtual table). One of the columns on this table will be your sample number, and this could potentially be the primary column on this table.

Your Stock table could then potentially be a standard table in Dataverse and have a natural lookup to the Sample (SQL Virtual) table using the Sample Number