Dynamic data source using Switch, then add filters

[AuburnMist]

Hello,

I have an app that displays employee leave records in a gallery. Due to the large number of employees, I have built a SharePoint list for a number of different fiscal years. When the Supervisor is entering leave records, they must first select the fiscal year using a dropdown. Based on the selection in the drop down; (2021/2022, 2022/2022, etc.), I am able to use the Switch function in both my form and my gallery so that the records save to the right list in SharePoint. 

The issue I am having is that I cannot work out how to apply filters in my gallery along with the Switch function. Is this even possible? 

The Items property for both my form and my gallery is; 

Switch(cmbFiscalYear.Selected.Value, "2021/2022", EmployeeLeave2021/2022, "2022/2023", EmployeeLeave2022/2023)

I need to add the above to this code inside of this, somehow:

SortByColumns(
  Filter(
     [my dynamic data source goes here?],
     EmployeeName = galSelectEmployee.Selected.Result,
     StartDate >= 'date(From)Leave'.Value,
     StartDate <= 'date(To)Leave'.Value && (AbsenceType.Value = cmbAbsenceType.Selected.Value || cmbAbsenceType.Selected.Value = Blank()) && (Status.Value = cmbStatus.Selected.Value || cmbStatus.Selected.Value = Blank())
        ),
        "StartDate",
        If(
            SortDescending,
            Ascending,
            Descending
        )
    )

What appears to be happening is that PowerApps is not recognizing the columns inside of the list, even though each list is exactly the same (only the name of the list is different). The error message indicates that EmployeeName is not recognized, same for StartDate, AbsenceType and Status. 

Any way around this? 

Thanks!

[poweractivate]

@AuburnMist Regarding your formula:

//pseudo-formula, untested, adjust as needed

SortByColumns
(
     Filter
     (
         Switch
         (
            cmbFiscalYear.Selected.Value
           ,"2021/2022"
           ,EmployeeLeave2021/2022
           ,"2022/2023"
           ,EmployeeLeave2022/2023
         )
        ,EmployeeName = galSelectEmployee.Selected.Result
           && StartDate >= 'date(From)Leave'.Value,
           && StartDate <= 'date(To)Leave'.Value 
           && 
             (
                AbsenceType.Value = cmbAbsenceType.Selected.Value 
                  || cmbAbsenceType.Selected.Value = Blank()
             ) 
           && 
             (
               Status.Value = cmbStatus.Selected.Value 
                  || cmbStatus.Selected.Value = Blank()
             )
     )
    ,"StartDate"
    ,If
     (
         SortDescending
        ,Ascending
        ,Descending
     )
)
    

Although I did not test it, the formula that you gave seems to be correct in terms of syntax. If not, see if using the above version works better, but I think it is probably identical to the one you gave.

To resolve the issue, you could try this:

1. Try using only one of the data sources by themselves. First try EmployeeLeave2021/2022. Then try  just EmployeeLeave2022/2023. See which one gives the error, or see if both give the error.

//pseudo-formula, untested, adjust as needed

SortByColumns
(
     Filter
     (
         EmployeeLeave2021/2022
        ,EmployeeName = galSelectEmployee.Selected.Result
....

2. If just one gives the error,  which I suspect it might do, let's suppose for example only EmployeeLeave2022/2023 - then make a note which one gives the error. 

3. Based on the error, try revising your formula like this:

//pseudo-formula, untested, adjust as needed. Replace YourTableName with the name of the data source it is complaining about.
...
        ,YourTableName[@EmployeeName] = galSelectEmployee.Selected.Result
...

4. If the above does not work, then EmployeeName is not actually the name of the field, and it only seemed like the two lists were identical, but were not. See what field it really wants for just EmployeeLeave2022/2023 . Let's suppose for sake of example it is just EmplName . If so, then try this:

//pseudo-formula, untested, adjust as needed

SortByColumns
(
     Filter
     (
         Switch
         (
            cmbFiscalYear.Selected.Value
           ,"2021/2022"
           ,EmployeeLeave2021/2022
           ,"2022/2023"
           ,EmployeeLeave2022/2023
         )
        ,Switch
         (
            cmbFiscalYear.Selected.Value
           ,"2021/2022"
           ,EmployeeName = galSelectEmployee.Selected.Result
           ,"2022/2023"
           ,EmplName = galSelectEmployee.Selected.Result
         )
 ......

5. If the above does not work, try using the disambiguation operator once more to see if it works:

//pseudo-formula, untested, adjust as needed

SortByColumns
(
     Filter
     (
         Switch
         (
            cmbFiscalYear.Selected.Value
           ,"2021/2022"
           ,EmployeeLeave2021/2022
           ,"2022/2023"
           ,EmployeeLeave2022/2023
         )
        ,Switch
         (
            cmbFiscalYear.Selected.Value
           ,"2021/2022"
           ,EmployeeLeave2021/2022[@EmployeeName] = galSelectEmployee.Selected.Result
           ,"2022/2023"
           ,EmployeeLeave2022/2023[@EmployeeName] = galSelectEmployee.Selected.Result
         )
 ......

6. If nothing like the above works, or you keep on getting more and more errors that all have to be individually fixed with more of the same kind of switch statements like the above on more clauses, then switching on every variation on so many clauses may end up seriously becoming more cumbersome and difficult to maintain, than just outright repeating the whole giant formula again by switching on the entire thing instead. If that ends up working better for you, then you should try that instead:

//pseudo-formula, untested, adjust as needed

Switch
(
     cmbFiscalYear.Selected.Value
	,"2021/2022"
	,SortByColumns
	 (
	 	 Filter
	 	 (
	 		 EmployeeLeave2021/2022
	 		,EmployeeName = galSelectEmployee.Selected.Result
	 		   && StartDate >= 'date(From)Leave'.Value,
	 		   && StartDate <= 'date(To)Leave'.Value 
	 		   && 
	 			 (
	 				AbsenceType.Value = cmbAbsenceType.Selected.Value 
	 				  || cmbAbsenceType.Selected.Value = Blank()
	 			 ) 
	 		   && 
	 			 (
	 			   Status.Value = cmbStatus.Selected.Value 
	 				  || cmbStatus.Selected.Value = Blank()
	 			 )
	 	 )
	 	,"StartDate"
	 	,If
	 	 (
	 		 SortDescending
	 		,Ascending
	 		,Descending
	 	 )
	 )
	,"2022/2023"
	,SortByColumns
	 (
	 	 Filter
	 	 (
	 		 EmployeeLeave2022/2023
	 		,EmployeeName = galSelectEmployee.Selected.Result //adjust as needed
	 		   && StartDate >= 'date(From)Leave'.Value, //adjust as needed
	 		   && StartDate <= 'date(To)Leave'.Value  //adjust as needed
	 		   && 
	 			 (
	 				AbsenceType.Value = cmbAbsenceType.Selected.Value //adjust as needed
	 				  || cmbAbsenceType.Selected.Value = Blank() //adjust as needed
	 			 ) 
	 		   && 
	 			 (
	 			   Status.Value = cmbStatus.Selected.Value  //adjust as needed
	 				  || cmbStatus.Selected.Value = Blank() //adjust as needed
	 			 )
	 	 )
	 	,"StartDate" //adjust as needed
	 	,If //adjust as needed
	 	 (
	 		 SortDescending //adjust as needed
	 		,Ascending //adjust as needed
	 		,Descending //adjust as needed
	 	 )
	 )   	
)    

See if it helps @AuburnMist 

[AuburnMist · Accepted solution]

Hi! Thank you so much for your thorough advice! I carefully reviewed all of your suggested solutions and had already tried a couple before your response came in. But I did not try the final two. I just attempted them and the last one proposed, was the winner! Yes, the code is quite long, but it operates very quickly so I'm not concerned about the length at all. Thank you so very much! 

[poweractivate]

@AuburnMist 

Yes, in general, just switching on the whole thing and even repeating the same giant inner formula again, is just easier. Even if it worked by using inner witch statements, if you need to put so many Switch statements for every possible variation in the inner formulas it's even more difficult to maintain in that case, and may even end up actually being longer itself than the seemingly giant formula repeating the huge inner formula! It is actually the single outer switch with the repeated giant inner formulas, that ironically may end up being easier to maintain and even, possibly itself be shorter, than the alternative of attempting to repeat the same switch statement to account for variations in the inner formula.

However, just in case:

Not sure you have time to try it, but I am curious if the following works. I suspect it won't work though:

//pseudo-formula, untested, adjust as needed
With
(
	{
		myDataSource: Switch
			 (
				cmbFiscalYear.Selected.Value
			   ,"2021/2022"
			   ,EmployeeLeave2021/2022
			   ,"2022/2023"
			   ,EmployeeLeave2022/2023
			 )
	}
	,SortByColumns
	 (
	 	 Filter
	 	 (
	 		 myDataSource
	 		,EmployeeName = galSelectEmployee.Selected.Result
	 		   && StartDate >= 'date(From)Leave'.Value,
	 		   && StartDate <= 'date(To)Leave'.Value 
	 		   && 
	 			 (
	 				AbsenceType.Value = cmbAbsenceType.Selected.Value 
	 				  || cmbAbsenceType.Selected.Value = Blank()
	 			 ) 
	 		   && 
	 			 (
	 			   Status.Value = cmbStatus.Selected.Value 
	 				  || cmbStatus.Selected.Value = Blank()
	 			 )
	 	 )
	 	,"StartDate"
	 	,If
	 	 (
	 		 SortDescending
	 		,Descending
	 		,Ascending
	 	 )
	 )
) 

If not, I wonder if replacing the Switch with the below works (however, the Switch is preferable to the below):

If
(
   cmbFiscalYear.Selected.Value = "2021/2022"
  ,EmployeeLeave2021/2022
  ,If
   (
      cmbFiscalYear.Selected.Value ="2022/2023"
     ,EmployeeLeave2022/2023
     ,EmployeeLeave2022/2023 //add a default clause and use one of the data sources, to give it the data source it likes most anyway, especially prefer which one it complains least about when you try it by itself, and put that one here so it always has something valid, and never a blank value. This will ideally never be reached - but in case it ever is, or to make the evaluator think it's valid anyway, just try this anyway and see if it for some reason works
   )
)

 If for some reason the above works, see if the below works:

Switch
(
   cmbFiscalYear.Selected.Value
  ,"2021/2022"
  ,EmployeeLeave2021/2022
  ,"2022/2023"
  ,EmployeeLeave2022/2023
  ,EmployeeLeave2022/2023//add a default clause and use one of the data sources, to give it the data source it likes most anyway, especially prefer which one it complains least about when you try it by itself, and put that one here so it always has something valid, and never a blank value. This will ideally never be reached - but in case it ever is, or to make the evaluator think it's valid anyway, just try this anyway and see if it for some reason works
)

And then trying the above (both of them) but without the "With" and using the original way you had it, but just making sure to have that default clause in there - try the Switch version, and the If version - see if either of them work. 

I did not try these above, however, I was curious if for some reason, any of the above actually end up working. If you did want to try it (and had a moment) and for some reason it really worked, I'd be curious to know. If it really does work, you may prefer it then.

You always could use the long formula, which is still probably the best way, especially if there's more than one variation since multiple Switch statements to account for multiple variations may actually end up being more difficult to maintain, and actually end up being possibly longer itself, even than simply using one giant Switch statement with the repeated giant inner formulas!

It is possible, however, that you really do have absolutely no variation in the List. If that's the case, you can try the above in case it works. However, if you discover that ultimately, there was something as minor as a single letter difference in as few as two columns, it's probably better to use the giant Switch statement anyway.

I presume that even if your two data sources are really "identical", the above might still not work because Power Apps may not like this sort of situation on trying to use two different data sources here in this way, and may believe that the schema does not match, cannot properly infer the schema for the inner parts of the formula, and ultimately may just prefer the giant switch statement instead to begin with. In general, the giant switch statement is probably generally easier. I included this response just in case that for some reason, anything I mentioned here actually worked and if so, it could, in some select few cases, reduce the repetition and create some benefit or could be preferable in some select few situations (however, there is still a possibility that it could complain with an error later on changes of either data source, and the giant Switch statement is in a way, safer and probably just easier to maintain in general).