Filter array with multiple conditions

(0) Share

Report

Posted on by Gwill1983

271

Hi all,

Hoping this will be a very simple fix for somebody more experienced than me.

I am creating a report using a flow, but need to filter the entries in my spreadsheet based on 2 conditions.

I currently am listing rows in a table (with no filter) and then using the filter array action to filter the entries. I just cannot work out the syntax / formula required in advance mode to make the 2 conditions work.

I am aiming to filter the entries based on the below conditions:

Column "Age of Non-Complete Entry" is greater than 13

Column "Reported to user" does not equal "Yes"

Is anybody able to assist?

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (0)

grantjenkins 11,063 Moderator on at

Like (0)

Report
greater(item()?['Age of Non-Complete Entry'], 13), not(equals(item()?['Reported to user'], 'Yes')) )

However, if your Age of Non-Complete Entry is being returned as a string, you may need to convert it to a number first.

@And( greater(float(item()?['Age of Non-Complete Entry']), 13), not(equals(item()?['Reported to user'], 'Yes')) )

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

You can try the following:

@And(
Was this reply helpful? Yes No


        
            
            
        
         
        
            
        
          
            
              
                
                  

                    
                    
                    
                        
                         
                    
                    
                      
                     
                        






 
    


 
    
    
    
    
    
    
    
    
      

    

    
    
        
        
    

    
        
     

       

        
            
        

    

  
                        
                            










 











 





 












 





 


  











 





 












 





 








 
    

  

 
    

    
      
        
        

         

         
        
            
            
             
                
                
            
            
        
        
        

        
            
              
                Gwill1983
                271 
                
                 
                
                
             
         
            
                
                        
                 
            

    


 on  at 
                        
                    
                        
                        
                        
                          
                            
                            
                            
                              










 


























 Like
(0)
                            

 




























    



 



 



Report
                        
                          
                        
                      
                    
                  
                
              
              
                
                  
                    
                      
                        Hi @grantjenkins 

Thanks for your suggestions. Unfortunately, both of these are failing!
 
The first option shows the error below in the filter array:
InvalidTemplate. The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@And( greater(item()?['Age of Non-Complete Entry'], 13), not(equals(item()?['Reported to user'], 'Yes')) )' failed: 'The template language function 'greater' expects two parameter of matching types. The function was invoked with values of type 'String' and 'Integer' that do not match.'.
 
The second option fails showing the error below:
InvalidTemplate. The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@And( greater(float(item()?['Age of Non-Complete Entry']), 13), not(equals(item()?['Reported to user'], 'Yes')) )' failed: 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.
 
Any ideas on what to check that could be causing these to fail?
 
 
                      
                    
                        
                        
                            





 





 










 





 






    
    
    
    
        
    
    


                        
                         
                      
                          
                       
                        
                            
                             
                              
                            
                            
                            
                            
                                
                                    
                                    
                                    
                                    
                                    
                                        Was this reply helpful?
                                        Yes
                                        No
                                    
                                    
                                    
                                
                            
                        
                        
                  
                
              
            
          
        
        
            
            
        
         
        
            
        
          
            
              
                
                  

                    
                    
                    
                        
                         
                    
                    
                      
                     
                        






 
    


 
    
    
    
    
    
    
    
    
      

    

    
    
        
        
    

    
        
     

       

        
            
        

    

  
                        
                            










 











 





 












 





 


  











 





 












 





 








 
    

  

 
    

    
      
        
        

         

         
        
            
            
             
                
                
            
            
        
        
        

        
            
              
                Matthy79
                4,180 
                
                 
                
                
             
         
            
                
                        
                        
                             
                                
                                        
                                        Super User 2024 Season 1
                                        
                            
                         
                     
                 
            

    


 on  at 
                        
                    
                        
                        
                        
                          
                            
                            
                            
                              










 


























 Like
(0)
                            

 




























    



 



 



Report
                        
                          
                        
                      
                    
                  
                
              
              
                
                  
                    
                      
                        What are the values in field „Age of Non-Complete Entry“ and is it possible that there are null values too?
 
If you don’t know what I mean, use the select action to select only the field „Age of Non-Complete Entry“ and put in the output of it here.
                      
                    
                        
                        
                            





 





 










 





 






    
    
    
    
        
    
    


                        
                         
                      
                          
                       
                        
                            
                             
                              
                            
                            
                            
                            
                                
                                    
                                    
                                    
                                    
                                    
                                        Was this reply helpful?
                                        Yes
                                        No
                                    
                                    
                                    
                                
                            
                        
                        
                  
                
              
            
          
        
        
            
            
        
         
        
            
        
          
            
              
                
                  

                    
                    
                    
                        
                         
                    
                    
                      
                     
                        






 
    


 
    
    
    
    
    
    
    
    
      

    

    
    
        
        
    

    
        
     

       

        
            
        

    

  
                        
                            










 











 





 












 





 


  











 





 












 





 








 
    

  

 
    

    
      
        
        

         

         
        
            
            
             
                
                
            
            
        
        
        

        
            
              
                grantjenkins
                11,063 
                
                 
                
                
             
         
            
                
                        
                        
                             
                                
                                        
                                        Moderator
                                        
                            
                         
                     
                 
            

    


 on  at 
                        
                    
                        
                        
                        
                          
                            
                            
                            
                              










 


























 Like
(0)
                            

 




























    



 



 



Report
                        
                          
                        
                      
                    
                  
                
              
              
                
                  
                    
                      
                        Can you try the following expressions to see if either of them work as expected.
 
@And(
 greater(int(item()?['Age of Non-Complete Entry']), 13),
 not(equals(item()?['Reported to user'], 'Yes'))
)

//OR

@and(
 greater(float(item()?['Age of Non-Complete Entry']), 13.0),
 not(equals(item()?['Reported to user'], 'Yes'))
)
 
                      
                    
                        
                        
                            





 





 










 





 






    
    
    
    
        
    
    


                        
                         
                      
                          
                       
                        
                            
                             
                              
                            
                            
                            
                            
                                
                                    
                                    
                                    
                                    
                                    
                                        Was this reply helpful?
                                        Yes
                                        No
                                    
                                    
                                    
                                
                            
                        
                        
                  
                
              
            
          
        
        
            
            
        
         
        
            
        
          
            
              
                
                  

                    
                    
                    
                        
                         
                    
                    
                      
                     
                        






 
    


 
    
    
    
    
    
    
    
    
      

    

    
    
        
        
    

    
        
     

       

        
            
        

    

  
                        
                            










 











 





 












 





 


  











 





 












 





 








 
    

  

 
    

    
      
        
        

         

         
        
            
            
             
                
                
            
            
        
        
        

        
            
              
                Gwill1983
                271 
                
                 
                
                
             
         
            
                
                        
                 
            

    


 on  at 
                        
                    
                        
                        
                        
                          
                            
                            
                            
                              










 


























 Like
(0)
                            

 




























    



 



 



Report
                        
                          
                        
                      
                    
                  
                
              
              
                
                  
                    
                      
                        Hi.
 
Neither of those are working. 
 
he execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@and(
greater(float(item()?['Age of Non-Complete Entry']), 13.0),
not(equals(item()?['Reported to user'], 'Yes'))
)' failed: 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.
                      
                    
                        
                        
                            





 





 










 





 






    
    
    
    
        
    
    


                        
                         
                      
                          
                       
                        
                            
                             
                              
                            
                            
                            
                            
                                
                                    
                                    
                                    
                                    
                                    
                                        Was this reply helpful?
                                        Yes
                                        No
                                    
                                    
                                    
                                
                            
                        
                        
                  
                
              
            
          
        
        
            
            
        
         
        
            
        
          
            
              
                
                  

                    
                    
                    
                        
                         
                    
                    
                      
                     
                        






 
    


 
    
    
    
    
    
    
    
    
      

    

    
    
        
        
    

    
        
     

       

        
            
        

    

  
                        
                            










 











 





 












 





 


  











 





 












 





 








 
    

  

 
    

    
      
        
        

         

         
        
            
            
             
                
                
            
            
        
        
        

        
            
              
                Gwill1983
                271 
                
                 
                
                
             
         
            
                
                        
                 
            

    


 on  at 
                        
                    
                        
                        
                        
                          
                            
                            
                            
                              










 


























 Like
(0)
                            

 




























    



 



 



Report
                        
                          
                        
                      
                    
                  
                
              
              
                
                  
                    
                      
                        Hi.
 
The values are below. This column does include null values
 
{
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "129"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "127"
  },
  {
    "Age": ""
  },
  {
    "Age": "126"
  },
  {
    "Age": "126"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "121"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "102"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "99"
  },
  {
    "Age": "99"
  },
  {
    "Age": "99"
  },
  {
    "Age": ""
  },
  {
    "Age": "99"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "85"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "80"
  },
  {
    "Age": "80"
  },
  {
    "Age": "80"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "79"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "77"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "60"
  },
  {
    "Age": "60"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "24"
  },
  {
    "Age": "24"
  },
  {
    "Age": "24"
  },
  {
    "Age": "24"
  },
  {
    "Age": "24"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "16"
  },
  {
    "Age": ""
  },
  {
    "Age": "14"
  },
  {
    "Age": ""
  },
  {
    "Age": ""
  },
  {
    "Age": "8"
  },
  {
    "Age": "8"
  }
 
 
If the null values are causing the issue then I can remove them or change them in my excel formula
                      
                    
                        
                        
                            





 





 










 





 






    
    
    
    
        
    
    


                        
                         
                      
                          
                       
                        
                            
                             
                              
                            
                            
                            
                            
                                
                                    
                                    
                                    
                                    
                                    
                                        Was this reply helpful?
                                        Yes
                                        No
                                    
                                    
                                    
                                
                            
                        
                        
                  
                
              
            
          
        
        
            
            
        
         
        
            
        
          
            
              
                
                  

                    
                    
                    
                        
                         
                    
                    
                      
                     
                        






 
    


 
    
    
    
    
    
    
    
    
      

    

    
    
        
        
    

    
        
     

       

        
            
        

    

  
                        
                            










 











 





 












 





 


  











 





 












 





 








 
    

  

 
    

    
      
        
        

         

         
        
            
            
             
                
                
            
            
        
        
        

        
            
              
                Matthy79
                4,180 
                
                 
                
                
             
         
            
                
                        
                        
                             
                                
                                        
                                        Super User 2024 Season 1
                                        
                            
                         
                     
                 
            

    


 on  at 
                        
                    
                        
                        
                        
                          
                            
                            
                            
                              










 


























 Like
(0)
                            

 




























    



 



 



Report
                        
                          
                        
                      
                    
                  
                
              
              
                
                  
                    
                      
                        You don’t have to. What should happen when the age is null? Just add the coalesce function and put in the value it should be when no age is available.
                      
                    
                        
                        
                            





 





 










 





 






    
    
    
    
        
    
    


                        
                         
                      
                          
                       
                        
                            
                             
                              
                            
                            
                            
                            
                                
                                    
                                    
                                    
                                    
                                    
                                        Was this reply helpful?
                                        Yes
                                        No
                                    
                                    
                                    
                                
                            
                        
                        
                  
                
              
            
          
        
        
            
            
        
         
        
            
        
          
            
              
                
                  

                    
                    
                    
                        
                         
                    
                    
                      
                     
                        






 
    


 
    
    
    
    
    
    
    
    
      

    

    
    
        
        
    

    
        
     

       

        
            
        

    

  
                        
                            










 











 





 












 





 


  











 





 












 





 








 
    

  

 
    

    
      
        
        

         

         
        
            
            
             
                
                
            
            
        
        
        

        
            
              
                grantjenkins
                11,063 
                
                 
                
                
             
         
            
                
                        
                        
                             
                                
                                        
                                        Moderator
                                        
                            
                         
                     
                 
            

    


 on  at 
                        
                    
                        
                        
                        
                          
                            
                            
                            
                              










 


























 Like
(0)
                            

 




























    



 



 



Report
                        
                          
                        
                      
                    
                  
                
              
              
                
                  
                    
                      
                        Given the ages can be blank, you would need to check for that first, then the other conditions. You should be able to just copy/paste the below.
 
@And(
 not(equals(empty(item()?['Age of Non-Complete Entry']), true)),
 greater(int(item()?['Age of Non-Complete Entry']), 13),
 not(equals(item()?['Reported to user'], 'Yes'))
)
 
 
                      
                    
                        
                        
                            





 





 










 





 






    
    
    
    
        
    
    


                        
                         
                      
                          
                       
                        
                            
                             
                              
                            
                            
                            
                            
                                
                                    
                                    
                                    
                                    
                                    
                                        Was this reply helpful?
                                        Yes
                                        No
                                    
                                    
                                    
                                
                            
                        
                        
                  
                
              
            
          
        
        
            
            
        
         
        
            
        
          
            
              
                
                  

                    
                    
                    
                        
                         
                    
                    
                      
                     
                        






 
    


 
    
    
    
    
    
    
    
    
      

    

    
    
        
        
    

    
        
     

       

        
            
        

    

  
                        
                            










 











 





 












 





 


  











 





 












 





 








 
    

  

 
    

    
      
        
        

         

         
        
            
            
             
                
                
            
            
        
        
        

        
            
              
                Gwill1983
                271 
                
                 
                
                
             
         
            
                
                        
                 
            

    


 on  at 
                        
                    
                        
                        
                        
                          
                            
                            
                            
                              










 


























 Like
(0)
                            

 




























    



 



 



Report
                        
                          
                        
                      
                    
                  
                
              
              
                
                  
                    
                      
                        Hi. This is still failing. 
 
Coming up with this error:
 
The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@And( not(equals(empty(item()?['Age of Non-Complete Entry']), true)), greater(int(item()?['Age of Non-Complete Entry']), 13), not(equals(int(item()?['Reported to user']), 'Yes')) ) ' failed: 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.
 
I thought this would be such an easy thing to setup, but it is ruining me! 🙂
 
                      
                    
                        
                        
                            





 





 










 





 






    
    
    
    
        
    
    


                        
                         
                      
                          
                       
                        
                            
                             
                              
                            
                            
                            
                            
                                
                                    
                                    
                                    
                                    
                                    
                                        Was this reply helpful?
                                        Yes
                                        No
                                    
                                    
                                    
                                
                            
                        
                        
                  
                
              
            
          
        
        
            
            
        
         
        
            
        
          
            
              
                
                  

                    
                    
                    
                        
                         
                    
                    
                      
                     
                        






 
    


 
    
    
    
    
    
    
    
    
      

    

    
    
        
        
    

    
        
     

       

        
            
        

    

  
                        
                            










 











 





 












 





 


  











 





 












 





 








 
    

  

 
    

    
      
        
        

         

         
        
            
            
             
                
                
            
            
        
        
        

        
            
              
                Matthy79
                4,180 
                
                 
                
                
             
         
            
                
                        
                        
                             
                                
                                        
                                        Super User 2024 Season 1
                                        
                            
                         
                     
                 
            

    


 on  at 
                        
                    
                        
                        
                        
                          
                            
                            
                            
                              










 


























 Like
(0)
                            

 




























    



 



 



Report
                        
                          
                        
                      
                    
                  
                
              
              
                
                  
                    
                      
                        Hello @Gwill1983 
 
Why do you try to convert "Reported to user" to integer? As far as I read in the last posts the value is a string (like "Yes"). If you provide the information of what should happen when the value is null, I can send you a condition that will work.
                      
                    
                        
                        
                            





 





 










 





 






    
    
    
    
        
    
    


                        
                         
                      
                          
                       
                        
                            
                             
                              
                            
                            
                            
                            
                                
                                    
                                    
                                    
                                    
                                    
                                        Was this reply helpful?
                                        Yes
                                        No








 
                              
                            
                                
                                    
                                        Under review
                                        
                                    
                                    
                                         
                                            
                                            
                                                Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.


                    
                    
                    
                         
                                                                                       
                        
                    
                    
                     
                        
                          Join the conversation