cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Smudgers9
Helper III
Helper III

Fetching items failed. Possible invalid string in filter query

I have been using the below code for over 12 months in a PowerApp and now without any changes to the app or the datasource it has stopped working.  I can't work it out but the common suggestion is using an As statement.  can someone please advise how to correct this code?

If(And(datesSelected, varType <> ""), Filter( 'Loan Equipment', varType = HardwareType, IsEmpty( Filter( 'Loan Dates', Ref = AssetID, Or( BEndDatePicker.SelectedDate <= EndDate && BStartDatePicker.SelectedDate >= StartDate, BEndDatePicker.SelectedDate >= EndDate && BStartDatePicker.SelectedDate <= StartDate, BStartDatePicker.SelectedDate >= StartDate && BStartDatePicker.SelectedDate <= EndDate, BEndDatePicker.SelectedDate <= EndDate && BEndDatePicker.SelectedDate >= StartDate ) ) ) ) )

Really appreciate your help 

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks for the response @RandyHayes but unfortunately I still get the same error when I run this formula.  Looking elsewhere online there is talk of an AS statement missing but I have no clue where this would go.

View solution in original post

4 REPLIES 4
RandyHayes
Super User
Super User

@Smudgers9 

Your logic seems a little off on this formula, but a couple things...if this is an Items property, then you run the risk of leaving the Items empty - this is never a good thing and has bad results are run/play time.

 

Consider the following formula instead:

Filter( 'Loan Equipment', 
    HardwareType=varType &&
    
    datesSelected &&
    IsEmpty(        
       Filter('Loan Dates', 
           Ref = AssetID && 
           BEndDatePicker.SelectedDate > StartDate && 
           BStartDatePicker.SelectedDate < EndDate &&
           BEndDatePicker.SelectedDate <= EndDate &&
           BStartDatePicker.SelectedDate >= StartDate
       )
    )     
)

 

I couldn't really figure out your date logic because it seemed that you restricted to before and after in one logic statement and then shifted to in-between in another one.  So, I assumed you wanted the dates in between the selected start and end.

 

In the formula above, there is no need for the If statement you started out with.  Using that If would cause the Items to be blank if there was not a datesSelected (assuming variable) of true and varType to equal something.  The formula above incorporates that into the filter so the results are the same except that you are always returning a signature based table - even if with no rows.

 

I hope this is helpful for you. 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thanks for the response @RandyHayes but unfortunately I still get the same error when I run this formula.  Looking elsewhere online there is talk of an AS statement missing but I have no clue where this would go.

@Smudgers9 

Can you provide more details on why you expect an AS statement would help?

Are you stating that either Ref or AssetID or both are used in both lists?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

I worked it out in the end.  It looks like some of the data sources naming conventions were a little strange from what I was provided so I have amended these along with changing how the filtering is run and this looks to have resolved the issue.  Thanks for the effort and advice though @RandyHayes I appreciate your time looking into this with me.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,352)