App from Excel with Dependencies

[sandyabegail]

Hi,

I'm trying to build an app to track our team's tasks and productivity. We're currently using an Excel file with dependent dropdowns. Here's how our reporting currently works:

1. Each team member maintains there own Excel file.

2. Someone collates the data and creates the dashboard using Pivots and Charts via Excel.

Our struggle:

1. It takes time to collate all files.

2. The consolidated report is saved as an Excel file in our SharePoint folder. 

3. Whenever there'll be changes in our Productivity Tracker, we have to create a new version and get everyone to use the new tracker.

Our goal:

1. Create an app that will serve as a centralized tracker.

2. Updates to be done in one file/app only - that will reflect in the app. - This is for sure something we can do via Power Apps

3. Have an automated dashboard that will update every time someone will encode their entries. 

Question is:

1. Does PowerApps support dependent dropdown?

2. What's the best way to achieve our goal?

Attaching a sample document. 

[RandyHayes]

@sandyabegail 

So for your questions:

1) Yes, you can certainly design all of that into PowerApps

2) Best way to achieve - SharePoint.  Don't start out trying to make Excel your datasource as you will inevitably end up with issues.

  - Move lookup information into SharePoint lists.  Create lists for all other pages in your file (except the charts).

  - Design your app and use the lists as datasources to your app.  

  - You can ultimately store your data in SharePoint (recommended) or put into Excel to act as a Linked sheet to another Excel file that would have all of your charting and pivot tables.

There are many other considerations along the way, but this is definitely achievable in PowerApps.

I hope this is helpful for you.

[sandyabegail]

Thanks @RandyHayes 

Just found out that we may have licensing issues for Power Apps. We currently have it as a trial in our O365 E3 sub. Do you think my project is worth pushing for? My only goal, for now, is just to create a standard form that will be used by less than 15 people – and have all data pushed in a central database. I’ll be using PowerQuery for my reporting. (Yeah, waiting for our PowerBI subs)

If not, I might end up using SharePoint’s customize form feature via PowerApps instead. I’m having struggles with multiple form submissions. But that’s another issue.

I just want to see what my options are before I take any step further.

[RandyHayes · Accepted solution]

@sandyabegail 

If you have E3 then you have licenses already.  There is nothing else you need to add for PowerApps licensing in order to use SharePoint as a data source.

I would give strong consideration to not using SharePoint as a front-end. Although it is viable, if your users are not already SharePoint aware, they will struggle with usability.  A standalone app would still meet your needs and you can make it as user friendly as you would like.

If you have other issues with submitting information, please feel free to bring those up as well.

[sandyabegail]

Just an update. I was able to get our form working. 🙂 Thanks for the help. I have another help need around formula. But I guess that entails for a new thread. 

[RandyHayes]

@sandyabegail 

Excellent!

Yes, if you have an unrelated question, feel free to post a new thread.  If you would like for myself or others to be notified of your thread so we can answer directly - please just put an '@' with my name in your post, then I will get a notification.