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

[Anonymous]

OK after after 6 more attempts at importing the data, I finally managed to get the Attendance data to report in the Attendance app control. To do it, however, I had to import the percentages as Text values rather than numbers or decimals. I'm not sure if this is a bug, but clearly there is not something right about the import of number values.

I'm not out of the woods yet however, since one of the issues I reported at the outset (namely the App does not pull all rows of data through).  For eg if you look at the Dataverse table image below, you will see there are 6 values named '107073-...."

When you look at the data pulled through in the app dropdown however, there are only 4.

Another issue I have discovered is the App has duplicated some values that don't exist in duplicate in the Dataverse table:

I'm not sure if these discrepancies have something to do with the fact these 4 dropdowns have yet to be cascaded?

Any ideas?

If you feel the shortfall does have something to do with the absence of fixing the cascade, I'm happy to draw a line under this and open another case. However, if fundamentally I cannot get the App to pull all available data through, I see little point on us sweating over this another minute. 

[Amik]

Have you tried creating a new screen and adding a fresh form to check if the issue is not limited to your current Form?

What happens if you create a dropdown control outside of the form control and set the Items property of it to your data source, and then set it to return the Attendance field?

Regarding the missing rows, you correctly surmised in your first post that 3065 rows of data will result in a delegation issue. Your app will only be "aware" of the first 2000 rows in your data.

Difficult to diagnose why the duplication without going under the hood.

[Anonymous]

I created a new screen but for some reason it didn't want to connect to the Dataverse gateway. However, I tried a different tack and would appreciate any advice.

In the Items property of the Offering code control, I have inserted the Distinct function...

Distinct('Course ReviewsCSVs','Offering Code').

If I type into the search box, the first 3 letters of the very last record "YEIWELD", it brings up the 3076th record. If I tie the Offering Code control as the lookup for the other 3 columns, will that not work.

With that in mind I attempted to tie Offering Code to Department, but I encountered the following error.

Distinct(Filter('Course ReviewsCSVs',Title=ddOfferingCode.Selected.Result),Department)

Where 

'Course ReviewsCSVs' is the Dataverse table that was imported successfully with Attendance as text rathe rthan percentage.

ddOfferingCode is the name of the dropdown control that pulls data from the 'Offering Code' column in the Dataverse table

Department is the other column name.

Or is this a delegation issue in disguise?

[Amik · Accepted solution]

Hi @Anonymous.

As noted earlier, there are too many topics here to unpack in a forum. It is difficult to diagnose your problem without looking at it inside your environment.

For example, not being able to connect to a Dataverse table already inside the app, even though it is already being used by a different screen, does not make sense to me (in fact

I never heard of this).

The syntax used in your expression: "Title = ddOfferingCode, Selected.Result" is not valid syntax.

Assume the screenshot is wrong because your formula looks correct. Although I do not understand why you’re argument is checking if the “Title” column equals the selected Offering Code ComboBox (no idea what Title  is, but it should be your Offering Code column).

I think you should close this post, and raise separate posts one problem at a time. The issue with not being able to connect your Datable table should be priority.

I wish you best of luck with this app. Feel free to @ me on your future posts.

-----------------------------------------------------------------------------------------------------------------------------

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

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