cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lowdmt
Helper II
Helper II

Datepicker error - is not valid. Creating query failed.

I have a re-occurring error displayed on my PowerApp which

 

The requested operation is invalid. Server Response: DailyDataAggregated failed: The expression "(((field_0 gt ())and(field_0 lt null))and(startswith(field_3,'Team1')))and(startswith(field_2,'UK'))" is not valid. Creating query failed.

 

I believe the issue is around the date filter?

 

 

'Date' is Field_0 - which is a date field in my SharePoint List

'Location' is field_2 - which is a single line of text in my SharePoint List

'Team' is field_3 - which is a single line of text in my SharePoint List

 

The result returned in the label is correct and as expected. The issue is the error popping up for users (despite the query seemingly functioning correctly)

 

 

 

 

Round(
    Sum(
        Filter(
            DailyDataAggregated,
            Date > DatePicker_Start.SelectedDate - 1,
            Date < DatePicker_End.SelectedDate + 1,
            StartsWith('Team', "Team1"),
            StartsWith(
                'Location',
                Location.Selected.Value
            )
        ),
        Completions
    ),
    1
)

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hmmm, looks like tis still struggling to evaluate the result of the datepicker minus 1

 

Try using DateAdd instead, and let's have a clause for each datepicker where it detects if there's no date and doesnt try to filter when there's no date :

 

 

Round(
    Sum(
        Filter(
            DailyDataAggregated,

            Len(DatePicker_Start.SelectedDate)=0
            ||
            DateCollected > DateAdd(DatePicker_Start.SelectedDate, -1, Days),

            Len(DatePicker_End.SelectedDate)=0
            ||
            DateCollected < DateAdd(DatePicker_End.SelectedDate,    1, Days),

            StartsWith(
                      'Team',
                      "Team1"
            ),
            StartsWith(
                'Location',
                Location.Selected.Value
            )
        ),
        Completions
    ),
    1
)

 

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

4 REPLIES 4
iAm_ManCat
Super User
Super User

Hi!

 

You are using an ambiguous name for your date as the Power Apps IDE already has a function called Date. You should rename you column in SharePoint to something more defined like AggregatedDate(or whatever kind of name is applicable).

 

Additionally, you are using a > symbol but your order of operations means it works out like this:

 (Date > DatePicker_Start.SelectedDate) - 1

 

So you should make sure to use brackets to define what you are comparing the AggregatedDate against:

Round(
    Sum(
        Filter(
            DailyDataAggregated,
            AggregatedDate > (DatePicker_Start.SelectedDate - 1),
            AggregatedDate < (DatePicker_End.SelectedDate + 1),
            StartsWith(
                      'Team',
                      "Team1"
            ),
            StartsWith(
                'Location',
                Location.Selected.Value
            )
        ),
        Completions
    ),
    1
)
@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Hi @iAm_ManCat 

 

Thanks for your help on this. I hanged the SP list column from 'Date' to 'DateCollected' as suggested. I also added the brackets around my DatePicker filter.

 

Unfortunately I'm still receiving a similar error:

The requested operation is invalid. Server Response: DailyDataAggregated failed: The expression "((field_0 gt ())and(field_0 lt ()))and(startswith(field_2,'UK'))" is not valid. Creating query failed. clientRequestId: 703b21f6-abfb-45e8-a3cf-7afb818c509b serviceRequestId: 703b21f6-abfb-45e8-a3cf-7afb818c509b

 

 

Round(
    Sum(
        Filter(
            DailyDataAggregated,
            DateCollected > (DatePicker_Start.SelectedDate - 1),
            DateCollected < (DatePicker_End.SelectedDate + 1),
            StartsWith('Team', "Team1"),
            StartsWith(
                'Location,
                Location.Selected.Value
            )
        ),
        Completions
    ),
    1
)

 

Anything else I can try?

 

 

Hmmm, looks like tis still struggling to evaluate the result of the datepicker minus 1

 

Try using DateAdd instead, and let's have a clause for each datepicker where it detects if there's no date and doesnt try to filter when there's no date :

 

 

Round(
    Sum(
        Filter(
            DailyDataAggregated,

            Len(DatePicker_Start.SelectedDate)=0
            ||
            DateCollected > DateAdd(DatePicker_Start.SelectedDate, -1, Days),

            Len(DatePicker_End.SelectedDate)=0
            ||
            DateCollected < DateAdd(DatePicker_End.SelectedDate,    1, Days),

            StartsWith(
                      'Team',
                      "Team1"
            ),
            StartsWith(
                'Location',
                Location.Selected.Value
            )
        ),
        Completions
    ),
    1
)

 

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


This worked! @iAm_ManCat 

 

A little confused how though.... my date pickers (start and end) both have dates populated from a variable 'on start'. 

 

Thanks for your helps

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.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

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 (4,325)