How to Manage 95 Excel Files as a Data Source in power apps

Set(selectedResidence, ThisItem.ResidenceName)

Items Property of Interventions Gallery:

Filter(InterventionsTable, ResidenceName = selectedResidence)

Data Source: Ensure correct data connections and use dynamic filtering based on the selectedResidence.

// OnSelect event of the residence gallery
Set( SelectedResidence, ThisItem.Name )

// OnVisible event of the screen
UpdateContext( { SelectedResidence: "" } )

// Data source formula
= Table( SharePointIntegration.GetFileContent( "https://your-sharepoint-site.sharepoint.com/sites/your-site/Shared%20Documents/" & SelectedResidence & ".xlsx" ) )

Also another approach is 

• Create a SharePoint List: Create a SharePoint list with columns similar to those in the Excel file.
• Upload Data: Use tools or scripts to upload data from all Excel files into the SharePoint list with an added column for Residence.
•  

Problem:

How can I make this application dynamic so that only the database changes based on the selected residence, without having to create separate screens for each residence?

Solution:

You can achieve this by using dynamic data loading and a single set of screens to display the data for each residence. Here's how you can proceed:

1. Single Set of Screens:

   • Create a single set of screens to handle the display and interactions (e.g., list of interventions, details of interventions, etc.).

2. Using a Collection to Store Data:

   • When a user selects a residence, load the relevant Excel data into a collection. This can be done using the ClearCollect function.

3. Gallery and Data Connections:

   • Set up your gallery and other controls to reference the collection that you populate when a residence is selected. This way, the data displayed in your controls changes dynamically based on the residence selected.

4. Navigation and Data Context:

   • Use the Navigate function to move between screens and pass the context (e.g., residence ID or name) so that the data can be loaded accordingly.

Here is an example of how you might set this up:

1. OnSelect of the residence in the Gallery:

   ClearCollect(SelectedResidenceData, Filter(SharePointList, ResidenceName = Gallery1.Selected.ResidenceName));
   Navigate(InterventionScreen, ScreenTransition.Fade);
   

    

2. Binding Controls:

   • Bind your controls (e.g., galleries, labels) on the InterventionScreen to the SelectedResidenceData collection.

3. This approach ensures that you only have a few screens, but the data displayed is dynamic based on the selected residence.

   If you need further details, feel free to ask. I hope this helps!

If this answer solves your problem, please accept it so others can benefit from it as well. Thank you!