Switch data source and filter at the same time

[Dorinda]

I have a gallery that I want to repurpose instead of making individual screens for all of my Position.

Currently I have this on the Items property of the gallery

With({_items:
        
            Filter(
           'POWER SYSTEM ELECTRICIAN Work Hours',
           'Apprentice Name' = Upper(varCurrentUserFullName),
           'Year Entered' >= 2020
        )
     },
     
    Sort(
        AddColumns(
            GroupBy(_items,
            "Year", "_data"
        ),
            "_totals",  
                {
                    Codes:Sum(_items,Code),
                    Safety:Sum (_items,Safety),
                    BluePrints: Sum (_items,Blueprints),
                    WiringEqiup: Sum(_items,'Wiring Equip'),
                    InstallSubstationEquip: Sum(_items,'Install Substation Equip'),
                    InstallDispEquipment:Sum (_items,'Install Disp Equipment'),
                    InstallMotorsGenerators: Sum(_items,'Install Motors Generators'),
                    ShopWorkElectEquip: Sum(_items,'Shop Work Elect Equip'),
                    PwrWiringBusBarInstall: Sum(_items,'Pwr Wiring Bus Bar Install'),
                    Welding:Sum (_items,Welding),
                    OJTMeterRelayTech: Sum(_items,'OJT Meter Relay Tech'),
                    OJTLineman: Sum(_items,'OJT Lineman'),
                    OJTDistDispatcher: Sum(_items,'OJT Dispatch'),
                    OJTSrSystemOperator: Sum(_items,'OJT Sr System Oper.'),
                    RSISupvd: Sum(_items,'RSI-Supvd'),    
                    RSIUnSupvd: Sum(_items,'RSI-UnSupvd')
                   
                    
                 
                }
        ),
        Year,
        Ascending
    )
)
   

Works great does what it is supposed to do, however I want to do a switch/if to have it changed based on the users position which I have in  varPosition.

I tried this

With({_items:
        
            Filter(
                   Switch(varPosition,
                 "Power System Electrician",
                 'POWER SYSTEM ELECTRICIAN Work Hours',
                 "Fleet Utility Mechanic",
                 'Fleet Utility Mechanic Work Hours'),
           'Apprentice Name' = Upper(varCurrentUserFullName),
           'Year Entered' >= 2020
        )
     },
     
    Sort(
        AddColumns(
            GroupBy(_items,
            "Year", "_data"
        ),
            "_totals",  
                {
                    Codes:Sum(_items,Code),
                    Safety:Sum (_items,Safety),
                    BluePrints: Sum (_items,Blueprints),
                    WiringEqiup: Sum(_items,'Wiring Equip'),
                    InstallSubstationEquip: Sum(_items,'Install Substation Equip'),
                    InstallDispEquipment:Sum (_items,'Install Disp Equipment'),
                    InstallMotorsGenerators: Sum(_items,'Install Motors Generators'),
                    ShopWorkElectEquip: Sum(_items,'Shop Work Elect Equip'),
                    PwrWiringBusBarInstall: Sum(_items,'Pwr Wiring Bus Bar Install'),
                    Welding:Sum (_items,Welding),
                    OJTMeterRelayTech: Sum(_items,'OJT Meter Relay Tech'),
                    OJTLineman: Sum(_items,'OJT Lineman'),
                    OJTDistDispatcher: Sum(_items,'OJT Dispatch'),
                    OJTSrSystemOperator: Sum(_items,'OJT Sr System Oper.'),
                    RSISupvd: Sum(_items,'RSI-Supvd'),    
                    RSIUnSupvd: Sum(_items,'RSI-UnSupvd')
                   
                    
                 
                }
        ),
        Year,
        Ascending
    )
)
   

However now everything is broke in all of the other stuff

I know I am close but my syntax is a bit off, would really appreciate some help.

[poweractivate]

@Dorinda 

In your formula

With({_items:Filter
             (
                   Switch
				   (
				       varPosition,
                      "Power System Electrician",
                      'POWER SYSTEM ELECTRICIAN Work Hours',
                      "Fleet Utility Mechanic",
                      'Fleet Utility Mechanic Work Hours'
				   ),
                  'Apprentice Name' = Upper(varCurrentUserFullName),
                  'Year Entered' >= 2020
            )
     },
     
    Sort(
        AddColumns(
            GroupBy(_items,
            "Year", "_data"
        ),
            "_totals",  
                {
                    Codes:Sum(_items,Code),
                    Safety:Sum (_items,Safety),
                    BluePrints: Sum (_items,Blueprints),
                    WiringEqiup: Sum(_items,'Wiring Equip'),
                    InstallSubstationEquip: Sum(_items,'Install Substation Equip'),
                    InstallDispEquipment:Sum (_items,'Install Disp Equipment'),
                    InstallMotorsGenerators: Sum(_items,'Install Motors Generators'),
                    ShopWorkElectEquip: Sum(_items,'Shop Work Elect Equip'),
                    PwrWiringBusBarInstall: Sum(_items,'Pwr Wiring Bus Bar Install'),
                    Welding:Sum (_items,Welding),
                    OJTMeterRelayTech: Sum(_items,'OJT Meter Relay Tech'),
                    OJTLineman: Sum(_items,'OJT Lineman'),
                    OJTDistDispatcher: Sum(_items,'OJT Dispatch'),
                    OJTSrSystemOperator: Sum(_items,'OJT Sr System Oper.'),
                    RSISupvd: Sum(_items,'RSI-Supvd'),    
                    RSIUnSupvd: Sum(_items,'RSI-UnSupvd')
                   
                    
                 
                }
        ),
        Year,
        Ascending
    )
)

If you are switching between two whole data sources and you have to re-use the other part, there are some issues to consider

1. Are all columns the exact same name and columns schema?

2. Is 'Year Entered' a valid column and of the same type in both sources?

If the answer to any of the above is "no" then it's not enough to Switch on the Data source, you have to Switch on the whole column that's returned too and make adjustments if needed on where there's any differences in the schema.

At the very least, 'Year Entered' cannot be used for both.

I don't think I can give a complete formula based on the info provided, because:

A. Is the Grouping by Year necessary here? Whether it is or not will impact what solution is best.

B. If the Grouping by Year is necessary here, is that true for BOTH data sources you're switching on, or just one?

C. Is it intended to return the Sum of those specific columns for BOTH Switch cases of the Variable?

D. Are all the columns having the exact same name and schema in general in both data sources? Whether it is or not will impact what solution is best.

See if it helps as starting point @Dorinda 

[poweractivate · Accepted solution]

Hi @Dorinda 

Based on this scenario, I think it's easiest to Switch on the whole thing instead of the individual components such as just the List name or just the Records, etc. because of there being one or more substantial differences in the schema in one of the two lists.

Also, if you already have to Switch on more than one part of your original formula, you would have to repeat the same Switch statement more than once (at least twice), i.e. multiple times, at that point it would be best to instead have just one Switch statement and to have it for the whole thing!

(I'm suspecting it's the second List:

'Fleet Utility Mechanic Work Hours'

for which it is really complaining about.

As a troubleshooting step, try to remove the Switch in your original formula, and try only one of the Lists.

I recommend trying with the first list only.

'POWER SYSTEM ELECTRICIAN Work Hours'

If there are no errors (and I suspect there are probably none when using this List), now try the second list

'Fleet Utility Mechanic Work Hours'

If there are errors now, there's where your issue is. You are trying to return a Record for this List  which does not match the schema, so Power Apps simply cannot do it.

However, what you want to do should still be possible.

Based on the above, I'd recommend switching on the whole thing - and I mean you have a whole second With statement and everything in it for the other Switch case and the other List.

Here's how you would do it:

In case you want to try it on your own first, I will put the below in a Spoiler tag, and you may reveal it in case you want to check for a starting approach:

Spoiler

//untested pseudo-formula - you may need to modify it as appropriate

Switch
(
    varPosition
   ,"Power System Electrician"
   ,With
   (
         {_items:Filter
                 (
                       'POWER SYSTEM ELECTRICIAN Work Hours'
                      ,'Apprentice Name' = Upper(varCurrentUserFullName)
                      ,'Year Entered' >= 2020
                 )
         }
        ,Sort(
            AddColumns(
                GroupBy(
				    _items
                   ,"Year"
				   ,"_data"
                )
                ,"_totals"
				,{
                      Codes:Sum(_items,Code),
                      Safety:Sum (_items,Safety),
                      BluePrints: Sum (_items,Blueprints),
                      WiringEqiup: Sum(_items,'Wiring Equip'),
                      InstallSubstationEquip: Sum(_items,'Install Substation Equip'),
                      InstallDispEquipment:Sum (_items,'Install Disp Equipment'),
                      InstallMotorsGenerators: Sum(_items,'Install Motors Generators'),
                      ShopWorkElectEquip: Sum(_items,'Shop Work Elect Equip'),
                      PwrWiringBusBarInstall: Sum(_items,'Pwr Wiring Bus Bar Install'),
                      Welding:Sum (_items,Welding),
                      OJTMeterRelayTech: Sum(_items,'OJT Meter Relay Tech'),
                      OJTLineman: Sum(_items,'OJT Lineman'),
                      OJTDistDispatcher: Sum(_items,'OJT Dispatch'),
                      OJTSrSystemOperator: Sum(_items,'OJT Sr System Oper.'),
                      RSISupvd: Sum(_items,'RSI-Supvd'),    
                      RSIUnSupvd: Sum(_items,'RSI-UnSupvd')
                       
                        
                     
                }
            )
            ,Year
            ,Ascending
        )
    )
	,"Fleet Utility Mechanic"
	,With
    (
          {_items:Filter
                  (
                        'Fleet Utility Mechanic Work Hours'
                       ,'Apprentice Name' = Upper(varCurrentUserFullName) //adjust if needed
                       ,'Year Entered' >= 2020 //adjust if needed
                  )
          }
         ,Sort(
             AddColumns(
                 GroupBy(
	 			    _items
                    ,"Year" //adjust if needed
	 			   ,"_data"
                 )
                 ,"_totals"
	 			,{
				       //adjust ANY or ALL of the below, where applicable and if needed
                       Codes:Sum(_items,Code), 
                       Safety:Sum (_items,Safety), 
                       BluePrints: Sum (_items,Blueprints),
                       WiringEqiup: Sum(_items,'Wiring Equip'),
                       InstallSubstationEquip: Sum(_items,'Install Substation Equip'),
                       InstallDispEquipment:Sum (_items,'Install Disp Equipment'),
                       InstallMotorsGenerators: Sum(_items,'Install Motors Generators'),
                       ShopWorkElectEquip: Sum(_items,'Shop Work Elect Equip'),
                       PwrWiringBusBarInstall: Sum(_items,'Pwr Wiring Bus Bar Install'),
                       Welding:Sum (_items,Welding),
                       OJTMeterRelayTech: Sum(_items,'OJT Meter Relay Tech'),
                       OJTLineman: Sum(_items,'OJT Lineman'),
                       OJTDistDispatcher: Sum(_items,'OJT Dispatch'),
                       OJTSrSystemOperator: Sum(_items,'OJT Sr System Oper.'),
                       RSISupvd: Sum(_items,'RSI-Supvd'),    
                       RSIUnSupvd: Sum(_items,'RSI-UnSupvd')
                        
                         
                      
                 }
             )
             ,Year
             ,Ascending
         )
     )
	 
)

Check if it helps @Dorinda