Developing data model with dataverse and populating columns from a lookup table

[Connork94]

Hello all,

I would like to use powerapps to develop a data model to use with a model driven app. I have a pre-existing data source (an excel table of data plus several lookup tables also in excel files) and I would like to use these tables to create a data model in a powerapps solution.

My plan was to load the main table and lookup tables into dataverse and connect the unique ids in the tables with relationships. I had expected that I could use lookup columns to link tables and populate columns with data.

For instance, I have a lookup table of country names with unique ids, which correspond to a unique ids in the country_name column of the main data table.

I had expected to create a relationship between these two tables and populate the main table with the values from the lookup tables connected via the unique keys.

So the table would look something like this:

However it looks like the data model will not work in this way. I am able to add lookup columns using the relationship between tables, but the existing unique keys in the main table are not mapping to the data present in the lookup tables, as I had expected they would.

Instead i'm getting a table like this, with the connected columns being blank:

However, if I add a new row and select a value from the lookup columns, then the related columns will populate.

For an example, in the image below you can see that I have selected the unique id in the "Country Information" lookup column and the country name has populated in the "country_names" column.

I would like this process to be done automatically when I create a relationship between the two tables.

So I would load the main table and the lookup table and add a lookup column for the country names, which would then populate and display the country names from the lookup table.

Is this possible?

Thanks,

Connor

[Jmanriquerios]

hi @Connork94 

Hello

It will only return the name field of the relational table.

To achieve what you ask for, you must create the same country name and country information fields in the table.

Then you must create a Javascript function to fill in the fields

function ObtenerCorreo(e) {
    debugger;
    
        // Get the Form Context
        var formContext = e.getFormContext(); 
    
        // Set variables for the related entity to get data from
        try{var recordGuid = formContext.getAttribute("jm_profesor").getValue()[0].id; }
        catch(err) {}
    
        // If the recordGuid contains data
        if (recordGuid != null) {
        
            // Use the WebApi to get data    
            Xrm.WebApi.retrieveRecord("jm_profesor", recordGuid, "?$select=jm_nombre,jm_correoelectronico").then(
                
                // If successful, set variables for the results 
                function success(result) {
                    var CorreoElectronico = result.jm_correoelectronico;
                                    
                    // Set the form fields
                    formContext.getAttribute("emailaddress").setValue(CorreoElectronico);
    
                },
                // If there's an error in the WebApi call, log the error and alert the user
                function (error) {
                    console.log(error.message);
                    // handle error conditions
                    var errorMessage = 'Error: An error occurred in the GetRelatedInfo function: ' + error.message;
                    var alertStrings = { confirmButtonLabel: "Yes", text: errorMessage };
                    var alertOptions = { height: 120, width: 260 };
                    Xrm.Navigation.openAlertDialog(alertStrings, alertOptions).then(
                        function success(result) {
                            console.log("Alert dialog closed");
                        },
                        function (error) {
                            console.log(error.message);
                        }
                    );  
                }
            );
        }
    }

Also depending on the business need you have, you can use the quick view forms to show that data within the form.

If I have answered your question, please mark your post as Solved.

If you like my response, please give it a Thumbs Up.

You can accept more than one post as a solution

[dpoggemann]

Hi @Connork94 

In Dataverse, each record on a table has a GUID to uniquely identify the record.  When you want to relate tables in Dataverse, you do not use alternate keys in the same manner you would in a SQL database.   The Lookup fields in Dataverse will relate two tables together using their "Primary Name" field.  I usually try to set this up on my tables as a "alternate key" to verify uniqueness (https://www.linkedin.com/pulse/alternate-keys-microsoft-dataverse-kumar-manish-csm-pmp-/).

For your process, I would do something like the following:

1. Create your table for the countries where the Primary Name field (defaults to Name on the table unless you  change). 
2. Load your countries to this Country table
3. Create an Application table which would have the list of all the applications with the Application Name in the Primary Name field and another Lookup column for the Country that would relate to the Country table you created.
4. Load the Applications with two fields including the Application Name and the Country Name (not the ids).  

Step 4 will automatically create the relationship between the Application record and the appropriate Country record in Dataverse populating the lookup field.  

You can then display additional fields (Quick View) from Country table on the Application form if you like and in views you can pull columns from the Country table to display on the view of Applications.

[Connork94]

Hi Jmanriquerios, thanks for the response. Ive taken a look and this might work, but its not the solution which I am looking for. I've come to the conclusion that PowerApps doesnt really have the functionality which I need for my business case.

Thanks for your help though!

[Connork94]

Hey dpoggemann,

Thanks for the response, but I am not sure this is exactly the solution which I was hoping for. It seems you're saying I should add the country names into the main table, which sort of defeats the purpose of having the lookup table to begin with.

I was hoping to replicate the functionality of a relational database in dataverse, but im not sure that this is really possible in the way that I want it to be.

Thanks for your help though!

[dpoggemann]

Hi @Connork94 

This is a relational model.  I am saying you have the Country names in the Country table along with any other fields that are related to the Country.  What I am saying is that the "Primary Name" field on the Country table would be the country name.  Note, behind the scenes there is a GUID for each record as well.

Now, when you are populating the Application table the foreign key is established behind the scenes to the GUID but in Dataverse you do this by setting the "Primary Name" value in the Lookup column on the Applications table.  When you load the data through an import, Dataverse will find the name of the Country in the Country table (since it is the Primary Name field) and create the relationship automatically populating the lookup column.