Best practice for Power App that has multiple share point lists

[slogan]

Hey Everyone,

I have a bit of a conundrum and I am not sure how I should proceed with building my apps. To summarize my issue:

I work for a Pump company that assembles and services pumps. I originally had built a Power App for the service guys to 1. Reduce the amount of paperwork and 2. to make it easier and faster to write reports for our customers. The service app has a master SharePoint list that holds the bulk of the data (About 350 columns currently with a mixture of Single Line, Multiple Line, Choice and Number columns) and 3 subsequent lists that are used in conjunction with it. This was mostly because the other lists hold the same data over and over (An example of this list is one for Impellers, for which a pump could have multiple and contain data such as measured clearances and impeller diameters). This made it easier with Power Automate that we use to auto generate our reports as it was just a For Each (With a filter) to extract all the data into a table and then push everything through from all lists via a JSON to the Adobe Document Generator API. All of that works perfectly. 

What has now happened as we have decided to build a production app for quality control to remove paperwork and also easier to look back at previous jobs. To make it easier I have created a new SharePoint site for both Production and also Engineering. The Production Site holds a master list of all of the pumps we have built, and Engineering holds lists that contain the master data for all the pumps and motors we build as well as service. These lists hold things like torque settings as well as parts for each model of pump and motor. There will be in total 12 lists in the Engineering Site.

What I have done, is to implement these Engineering lists into both apps. I use a master item/value (Pump Type) to filter through the correct lists (Using the Switch Function based on the Pump or Motor Type that gets me to the correct List), to find distinct brands for a drop down, then to the particular pump or motor range, and then down to the pump or motor model. I need to do this as one particular list has over 1000 different pump models. We also hold data for our competitors hence the brand selection. Once the user selects the correct model of either the pump or the motor, it Patches in either the Production App or the Service App to their respective SharePoint lists, the Brand, Range and then the Model of the Pump or Motor.  As well as that, before patching, it goes and finds the Motor ID from the list and the Pump ID from its list and patches that as well. This is so when they click on arrow in the respective galleries, it then does a LookUp using the Motor or Pump List ID and Sets a variable with the data for that particular model that can then be used through out the particular Job they are working on without needing to do multiple look ups. I think this is the best way to do that bit. Let me know if i am wrong.

The next bit is the bit that is confusing and I am not sure how to go about it. What I would like it to do, is to update the respective Service or Productions lists within SharePoint with all of the data that the particular app requires for that job from the Engineering Lists. The Service SharePoint list has multiple dropdowns for things like bearing types, or whether or not that particular pump has a certain part. All of that data is now held in the Engineering SharePoint lists. I want it so when the user selects the particular pump or motor model, it auto populates those items and instead of them being a dropdown box, it is now just a label. I have tried looking at patching it, which can be seen below but it is very ugly. This only has half of the different pump types that we deal with and 5 of the below use the same list. The others ones have their own lists. This also does not contain the motor component to it, but the issues I want to resolve with the below will be used for the motors as well.

//Find the ID of the Pump Model that is selected to be used for setting the Pump Data throughout the APP

Set(varPumpModelID,Switch(inpPumpType.Selected.Value, 
"Starline", LookUp('OurCompanyEndSuctionPumpModels',PumpBaseModel = inpPumpRange.SelectedText.Value && PumpModel = inpPumpModel.SelectedText.Value,ID),
"ISO-Pro",LookUp('OurCompanyEndSuctionPumpModels',PumpBaseModel = inpPumpRange.SelectedText.Value && PumpModel = inpPumpModel.SelectedText.Value,ID),
"Sovereign",LookUp('OurCompanyEndSuctionPumpModels',PumpBaseModel = inpPumpRange.SelectedText.Value && PumpModel = inpPumpModel.SelectedText.Value,ID),
"End Suction - Close Couple", LookUp('OurCompanyEndSuctionPumpModels',PumpBaseModel = inpPumpRange.SelectedText.Value && PumpModel = inpPumpModel.SelectedText.Value,ID),
"End Suction - Long Couple",LookUp('OurCompanyEndSuctionPumpModels',PumpBaseModel = inpPumpRange.SelectedText.Value && PumpModel = inpPumpModel.SelectedText.Value,ID),
"Submersible Pump",LookUp('OurCompanySubmersiblePumpModelDatabase',PumpBaseModel=inpPumpRange.SelectedText.Value && PumpModel = inpPumpModel.SelectedText.Value,ID))
);

//Create a record variable based for the pump data, which is used throughout the app for intital use. The arrow on main gallery sets this variable for subsequent uses

Switch(varInspectionRecord.PumpType.Value, 
"Starline", Set(varEndSuctionPumpData,LookUp('OurCompanyEndSuctionPumpModels',ID = varPumpModelID)),
"ISO-Pro",Set(varEndSuctionPumpData,LookUp('OurCompanyEndSuctionPumpModels',ID = varPumpModelID)),
"Sovereign",Set(varEndSuctionPumpData,LookUp('OurCompanyEndSuctionPumpModels',ID = varPumpModelID)),
"End Suction - Close Couple",Set(varEndSuctionPumpData,LookUp('OurCompanyEndSuctionPumpModels',ID = varPumpModelID)),
"End Suction - Long Couple",Set(varEndSuctionPumpData,LookUp('OurCompanyEndSuctionPumpModels',ID = varPumpModelID)),
"Submersible Pump",Set(varSubmersiblePumpData,LookUp('OurCompanySubmersiblePumpModelDatabase',ID = varPumpModelID)));

Set(varInspectionRecord,Switch(varInspectionRecord.PumpType.Value,
"Starline",
Patch('OurCompanyServiceReportData',varInspectionRecord,
    {
        PumpModel: inpPumpModel.Selected.Value,  
        PumpModelID: varPumpModelID,
        PumpRange: inpPumpRange.Selected.Value,
        PumpBrand: inpPumpBrandUpdate.Selected.Value,
        PumpNoOfStages: varEndSuctionPumpData.NoOfStages
        
    }
),
"ISO-Pro",
Patch('OurCompanyServiceReportData',varInspectionRecord,
    
    {
        PumpModel: inpPumpModel.Selected.Value,  
        PumpModelID: varPumpModelID,
        PumpRange: inpPumpRange.Selected.Value,
        PumpBrand: inpPumpBrandUpdate.Selected.Value,
        PumpNoOfStages: varEndSuctionPumpData.NoOfStages
    }
),
"Sovereign",
Patch('OurCompanyServiceReportData',varInspectionRecord,
    
    {
        PumpModel: inpPumpModel.Selected.Value,  
        PumpModelID: varPumpModelID,
        PumpRange: inpPumpRange.Selected.Value,
        PumpBrand: inpPumpBrandUpdate.Selected.Value,
        PumpNoOfStages: varEndSuctionPumpData.NoOfStages
    }
),
"End Suction - Close Couple",
Patch('OurCompanyServiceReportData',varInspectionRecord,
    
    {
        PumpModel: inpPumpModel.Selected.Value,  
        PumpModelID: varPumpModelID,
        PumpRange: inpPumpRange.Selected.Value,
        PumpBrand: inpPumpBrandUpdate.Selected.Value,
        PumpNoOfStages: varEndSuctionPumpData.NoOfStages
    }
),
"End Suction - Long Couple",
Patch('OurCompanyServiceReportData',varInspectionRecord,
    
    {
        PumpModel: inpPumpModel.Selected.Value,  
        PumpModelID: varPumpModelID,
        PumpRange: inpPumpRange.Selected.Value,
        PumpBrand: inpPumpBrandUpdate.Selected.Value,
        PumpNoOfStages: varEndSuctionPumpData.NoOfStages
    }
),
"Submersible Pump",
Patch('OurCompanyServiceReportData',varInspectionRecord,
    {
       PumpModel: inpPumpModel.Selected.Value,  
        PumpModelID: varPumpModelID,
        PumpRange: inpPumpRange.Selected.Value,
        PumpBrand: inpPumpBrandUpdate.Selected.Value,
       PumpNoOfStages: varSubmersiblePumpData.NoOfStages
    }
)));

 As you can see it is very messy and I am thinking that it is not the best way to do it. Would trying to do something with a Flow be better? Or the other option would be to have a multiple Pump or Model Selection dropdowns that are visible based off the particular pump type with a more simple setup for patching. The ones above are only the initial values I was playing with. Most of these patches will be 20-30 variables long. Power Apps already seems to lock up on this OnSelect for the dropdown box when its trying to check the formulas when editing them. My aim is to once I have it all running to try and merge all the individual components into one Switch function however I don't know whether it would speed things up

Any help would be greatly appreciated

Kind Regards,

Sam

[IAmIntractable]

If I understand this, you have lists that represent your configuration and then lists that represent the work in progress. You want to configure things once (configuration), and then copy that configuration as new work when something new starts.?

Essentially, to get the data from the configuration lists to the work lists requires patching/insertion. How you do that depends on the app presentation/use. The code will likely be long and unruly. You could use a flow to do all the set up and pass back the ID of the new work. The app could wait for that ID to be returned and then refresh the presentation. I don't think this is specifically a SharePoint multiple list issue but a general issue depending on the approach you want to take. If you were using Dataverse the same actions would be needed but perhaps some of the work could be pushed to the server for performance.

[slogan]

Hey @IAmIntractable sort of. The master lists hold data about the particular pump model and some of the data is used for each of the service and production apps. I am thinking that patching might be the best option as although you can run a flow, the update item for the service app with so many columns and a lot of choices columns would slow the flow right down giving a poor user experience. Also I would then have to run a refresh of the app to pull the data through. Although the patching is going to look ugly, its only patching 20-30 items. 

Unless there is another way someone can think of.

Kind Regards,
Sam