I have a rather complex app that I've been working on. When someone first logs in I would like the application to load numerous collections from data in an excel sheet. I want the collections to be collected into one large collection, and I want to be able to update these collections within the larger collection in the future when they log in.
I have found out how to collect collections into a main collection:
Collect(CollectionMain({Collection1: Collection1, Collection2: Collection2})
This gives me my large collection, but I also have each collection existing on its own.
My ultimate goal is to be able to do something like the following:
1. First time they log in: CollectionMain is created and saved with Collection1, Collection2, etc embedded. Thus each person who logs in will have a unique CollectionMain. I assume I can do this via something like this:
Collect(CollectionMain({Collection1: Collection1, Collection2: Collection2})
This is where I'm not sure what to do:
2. Each additional login: Collection main autopopulates Collection1, Collection2 etc. within the app, and each update I make to Collection1, Collection2, etc, updates that collection within the main collection, so at log-out, CollectionMain's latest updates are saved and next time they log in, they can pick up where they left off.
Additionally, I would like this "CollectionMain" to be able to be backed up/exported or run from the cloud so that it can be accessed by others.
Is this possible?
Thanks
You can have have multiple tables in one Excel file. If your data is not going to be static, I suggest that you only have one table per worksheet. Since PowerApps adds an "ID" column to tables that you add to your app, your Excel file may be corrupted if two tables are too close together when the column is added. The column will cause the two tables to merge formatting and then everything will be ruined. /Personal experience 🙂
For that matter, there are benefits to separating each table into its own spreadsheet or keeping them altogether.
Benefits of multiple tables in one Excel spreadsheet:
Benefits of each table in its own Excel spreadsheet:
Can I use tables instead of collections and store multiple tables in a single excel file instead?
Collections are great for information that is local to the current device and the current user. On the local device, be sure to check out the LoadData and SaveData functions and the Import and Export controls which could help out with your second point.
But collections can't easily be shared between users or even between different devices used by the same user. For this reason, we aren't doing much with collections to move them forward.
The ansewr lies with using a connector to store information in the cloud. The templates we ship use Excel stored in Dropbox as an example. You could have rows in the spreadsheet marked with a field that is unique per person (using User().Email for example), and then filter the data source based on this value. You have to ask how secure the information needs to be too - is it important that each user can only see their information?
Excel may not scale sufficiently for you, you may need to use a SharePoint list, Salesforce, a SQL table, or many others. Is there already a place whre information of the type you need is stored? There are lots of considerations when deciding where to store the information, but this is really your next question to answer if you want to take this app beyond one device and one user.