Power App to pull up Dataverse data and post it to SharePoint list.

[Anonymous]

Hi Community,

I have been working on an app for some months and it has evolved from a simple form input to post data to a SharePoint list, to the additional need to import external data into the Dataverse, selectable in the Power App form, to then add to rows in the SharePoint list.

External data is batch downloaded in xlsx format, comprising 4 entities (Department, Course Offering, Offering Name, Attendance). The Excel sheet was imported as one table into the Dataverse [I used the PowerQuery template to load the data into a new table, not the existing one (don't know if that was a wrong move?)] At the table set up stage in the Dataverse, I added an autonumbering column (“Review No,” - again not sure if this was the right move)

 I have a number of challenges:

• The first three entities pull through correctly Department, Course Offering, Offering Name but Attendance is pulling through the auto number column instead (I think this has something to do with the Attendance being in percentage format). There are also 3065 rows of data in the Excel sheet (I've read about delegation issues and the max number of rows being 2000)
• Not all the data appears to be pulling through. For instance, if the table is ordered alphabetically by Department, 6 records are visible (see screenshot below), but only 4 are pulling through into the form control.
• Although I selected the searchable option in the Form, when I begin typing the first few letters into any one of the form controls, no results show.

Dataverse table view

App Form view

I need to have cascading lookup, whereby selections on the Course Offering form will cascade correctly to Department, Offering Name and Attendance data. 

Any guidance would be greatly appreciated.

Thanks in advance

[Amik]

@Anonymous - You will find you will get more traction in community responses if you break your problem up into separate posts outlining a specific problem, as opposed to posting a giant problem with multiple components you need solved. It is unreasonable to expect someone to take the time to look into this entire scenario piece by piece. Something to consider for your next post!

Back to the problem.

Regarding the first issue. As I understand it:

• You've imported an Excel table into Dataverse using Power Query. You say an Excel sheet was imported as one table into Dataverse. You also say it comprises of 4 entities (which in “Microsoft speak” means 4 tables). However by looking at your screenshot, I think you mean you imported one Excel table which contains 4 columns: Department, Course Offering, Offering Name and Attendance.
• You added an Auto number column called Review No. into the Dataverse table.
• You  have an App which uses this table as a data source and this App contains an Edit form control linked to this table.

When you say the first three columns “pull through correctly” - pull through in what? pull through correctly during the import process into Dataverse? Or do you mean pull through correctly in your Form?

Looking at the screenshot, I assume the data was imported correctly into Dataverse, so I am guessing the problem is that the Attendance field is not pulling through in your App?

[Anonymous]

Thank you @Amik, I appreciate your constructive criticism. It took quite some time to articulate the challenges I am facing, and I half expected to get no response. Consider myself rebuked, it shall not happen again.

In my defence, I wasn't exactly sure where to begin on the smorgasbord of the multifactorial issues I, a relative novice, was challenged, especially with the Dataverse.  Thankyou again however, for being brave enough to grasp the nettle so to speak and give it a bash. 

Apologies also for the incorrect nomenclature. You are correct, I should have stated columns rather than entities (that's a novice for you!)

Yes, regarding the scope of the app, you more or less have summarised it correctly.

The excel import has the 4 columns. I added an additional column at the Power Query setup stage (called Review No), which is really an auto number. In retrospect, I think the addition of the auto number was unnecessary because the  Course Offering column would most probably do the job.

All data except that for the Attendance column pulled through correctly*. Somehow the autonumbers in the Review No, column are pulling through into the Attendance field (see image below).

The Attendance data is supposed to be percentages as in the Dataverse. Somehow that field has picked up Review No. data (see next image):

So that's the first issue. The second issue is that not all the data is pulling through to Department ( you can see from my previous post that 6 records exist in the Dataverse Department column as "107073-............". And yet the App is only pulling 4 records through. Not sure if this is because of a delegation issue that I have been reading about?

The third challenge is to achieve a cascading lookup ( my best guess is to base the primary lookup field as the Course Offering data, since that is unique.) So the app should let the user select the Course Offering, which in turn should pull up data for Department, Offering name and Attendance.

I have a 4th challenge, how to post that look up data into my SharePoint list ( I have already managed to do this for the other controls). I shall  however, reserve that for a separate query if I can fix the first 3 issues.

If you are able to take any one of the 4 issues and advise on that, I should be most grateful.

Sincerely.

[Amik]

Creating an auto-number column was probably unnecessary because Dataverse includes auto-number column type.

Focusing on the first issue for now, and sorry to state the obvious, but what is the Items property of the Data Card for Attendance?

[Anonymous]

No need to apologise for stating the obvious. At this stage, I need it 🙂

Out of the gate, I decided to reimport the data without Review No, into a new table "Course Review Quality" (why does it add an 's' to make it plural? It comes out the other end as "Course Review Qualities") I honestly don't know how I managed it previously as the power query template let me upload the data to a new table. That caused confusion further down the track when trying to find the data source at the App interface. So I made things simpler this time and imported through the "Get from excel" option.

So here is Data card for the connection. You didn't ask for it but I have included it because I'm not sure if I am supposed to connect to the Dataverse gateway or to the Dataverse table or if it doesn't matter?

The 4 column headers now are Department,  Offering Code, Offering Name and Attendance. Now Attendance is picking up the data from Offering Name.

Caveat: I'm wondering if the problem is at the import stage. When mapping the columns, I notice there was a default header called "Name". It doesn't represent anything close to my data, but you have to select it to make the import happen. It is here that I recall previously mapping Name to Review No. For this import, I aligned it to Offering Code, and Attendance is picking up that data up instead. See image below.

So I now know why Attendance is picking up Offering code data. It's because by default it has adopted Name (which previously I said I had to map it to some value to get it to import, so I mapped it to Offering code)See below:

Why it does that perhaps you would know. But that's where another challenge begins.

The primary text cannot find Attendance:

It is in the Search Field however but, if I select it, I get an error:

What am I doing wrong?

[Anonymous]

 @Amik  Forgot to provide you with this...

[Amik]

There is quite a lot to unpack here and we have not even started talking about your other issues. I do not think a forum is the best place for this but I will provide a rough steer.

As you correctly noted, during the import process, “Name” must be selected before you can proceed on executing the import. Name refers to the Primary Column of your Dataverse table. Think of the Primary Column as similar in spirit to the Title column in SharePoint - a table needs at least one column by default before it can be created. It is important to note that the Primary column is not the primary key column. A typical use case for a primary column would be an auto number column you can use in your Power Apps to identify records. If you ever need  to relate this table to another table, it is the Primary Column you will use to create the relationship. There are many resources online which can provide more detail.

Back to your problem. It seems rather than select Auto-generated during the import process, you selected one of your existing columns, hence the confusion.

What I would suggest is that you Create a new table in Dataverse without using an import. Go to the Home screen, select Tables on the left and then + New table.

 Configure the Primary Column by selecting the Primary Column tab.

Name this column to anything you want but be careful because the logical/schema name of it cannot be changed. As with SharePoint, you can still change the display name afterwards.

For example name this column “Record ID” and save. Note that you cannot change the data type of the primary column on initial creation of the Dataverse table. The default data type for this column is always single line text.  However, you do have the option to change it to a very handy auto-number column after you create the table.

Once the table has been created, create as many additional columns you need.

Next, click Import data from Excel and go through the mapping steps to map the columns in this table to your Excel file.

Once you have migrated your data, connect this table to your EditForm and let me know how you get on.

On a separate note, I strongly recommend you read up more on Dataverse. A good place to start are the wonderful starter tutorials from:

@ShaneYoung here: https://www.youtube.com/watch?v=kxxEC1xH9sI 

@RezaDorrani here: https://youtu.be/byUuEoDQjiU?list=PLTyFh-qDKAiHr7HwkvlHXpCNf73xNBqj_&t=732 

[Anonymous]

Thank you for the update @Amik . I followed your guidance and I now get how the Primary column is supposed to work. I mapped the primary column to the first of the 4 columns (Offering Code) I mentioned importing to the Dataverse (shown below with the first line of data:

I then connected these 4 in the app, but  Attendance is still not pulling correctly.

The actual data in Attendance is another issue. The actual import data on the Excel file is shown as percentages. The first line should read 81.9%, (First line shown below):

As you can see, Dataverse converts it to a 3 decimal figure of 0.819.

The greater issue however, is that the Attendance control is still pulling down the Offering code (which was mapped to the Primary column).

The 4 controls linked to the 4 imported columns are connected through this.

And the view is this..

I should point out that there are 14 controls on the form, but only 4 map to the Dataverse.  Consequently I cannot link the entire edit form to the Dataverse connecter, only the relevant controls.

As you can see, its pulling the  same data in Offering Code

There's something about the percentages it doesn't like??

[Amik]

A percentage is stored as a number divided by 100. Dataverse does not convert anything. If you want to FORMAT this column to DISPLAY as a percentage, this is not possible. The only alternative is to create a calculated formula which references the percentage column and adds a "%" sign. If you want to display the attendance column as a percentage in power apps, you can use a text function, .e.g Text(0.5,"0%").

Regarding the actual attendance data. I assume the Default property for the attendance DataCard is still ThisItem.Attendance and the DataCardValue is still Parent.Default?

[Anonymous]

Hi @Amik , I thought you were on to something for a minute there. When I checked, the Datacard default was still as you have stated, but the default value had no value. When I changed it to Parent.Default however, it didn't make blind bit of difference. I think this is why...

If I select the Edit Field option on any one of the first three controls (ie Department, Offering Code, Offering Name) I will see that name reflected in the Primary text, while the search option shows the Primary key field "Offering Code" (for eg see below for Department)

However, when I do the same for Attendance, it cannot find it in the primary text.

It's only visible in the search field:

Understandably, if I select Attendance for the search field, the app breaks.

Thanks for the heads up about the percentage expression btw....