cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

An App connected to ExCel through OneDrive

Hey Everyone,

 

I could use some help. I am creating an Inspection Report app and am experiencing some issues with the way I chose to handle the data. I will explain why I am making the app, how I went about acheiving my goal, and the issues I'm experiencing.

 

This app is being created to allow the user to document an inspection on their phone. The reason that it is beneficial to document the inspection through an app is because we want to create a database of these inspections and we want to have our reports automatically written based on the information in that database. So, to sum it up, the goal was to have an app that could be used to document an inspection, have that inspection be saved to a database, and to have the information in the database to be accesible so a report could automatically be generated from it.

 

I am somewhat familiar with the more advanced side of microsoft products but really I am a novice at best. So, with that in mind, this is how I chose to build this app/system. The project before this app I became familiar with mail merging information from ExCel into Word documents. I believed this would be the best way to take information from a database and turn it into a report. So I chose to use ExCel as the base for the database. My company uses Office365 so the ExCel database file is located in OneDrive. Then, I came across PowerApps in my research and chose to use it because I found it very easy to connect to ExCel files in OneDrive. So, on a macro level, my vision was to have a PowerApp that would write to an ExCel file in OneDrive and the information in that ExCel file could be mail merged into Word to create reports.

 

Now, to zoom in some. Strictly related to the PowerApp the first thing I did was generate an app from data. This taught me some about how the gallery, display, and edit forms worked. I felt that we wanted a little more customization to the user interface than the basic edit form provided. I learned how to connect other text input boxes to data cards so you didn't have to type directly in an edit form to be able to edit that form. I would change the Update portion of a card in an edit form from DataCardValue.Text to TextInput.Text. When I submitted a form the text input value would appear in my ExCel table. Because i knew I could attach any text input to a data card I made multiple pages of user interface and had a hidden form page. The first attachment is an example of the user interface. The idea was that the user would go through the app and fill out all of the UI pages and then at the end press submit. The submit button would submit the hidden form and each text input in the UI would be connected to a data card in the form. 

 

We did not start out knowing what fields we wanted in the app. Our breadth of information we wanted to gather expanded as I developed the app. I knew that we would add to the app as it was developed so I decided to go about finishing the whole UI first, then create the ExCel databse file, then link the app to the database through the edit form. So I spent a few months building the entire UI and then put together the database. After completion of the database I checked how many columns of data it had. There were 1100 columns of data. 

 

I started out using the online PowerApps developer. I was able to create the gallery and connect the table through OneDrive without any problems. Then I created the first edit form and began clicking the check boxes to add the fields. At first the developer was responding fine and I was able to add in fields no problem, but as I continued adding fields it grew slower and slower. I currently have 800 of the 1100 fields added and the developer is essentially crawling. I have to wait between 2 and 5 minutes between each addition now. To relieve some stress from the one edit form I created a second one in a different page. I observed that the other pages I had were more responsive so I theorized that isolating some of the form from itself could speed things up. I also switched to the actual developer program I got off the Windows store. I added 500 fields in the first form on the online developer and 300 fields in the second form on the dedicated developer to total the 800 fields I have added. For awhile the second form sped things up but I am back to a crawl. I know I could add a third form but I believe I will experience diminishing returns on this solution.

 

Task Manager shows that an app generated from data (a table with 5 columns) uses 499 MB of memory while open. Task manager shows that my app with the 800 fields is using 10,207 MB of memory while open. The next two attached files are screenshots of my task manager. I have not had any success opening the app on my phone at all, it just says opening and the loading bubbles animation runs but it has yet to finish after being left to load for an hour.

 

I am rather at a loss for what to do. I do not mind slogging along slowly adding cards to the form if it means that I would have something that works in the end, but if I can't get this to open on an iphone then it is sort of all for nothing. Is 1100 fields too many for a powerapp? Am I going about this in an inomptimum way? I am open to any and all ideas and advice. 

 

I appreciate the time spent reading this, I really hope someone is able to point me in the right direction. Thank you!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,766)