Invalid operation/query is not valid error with filter of SharePoint data

[rmclaughlin]

Hi - I have a function that creates a list of date ranges and then for each of those ranges, collect information from a SP list. It worked fine while I was building it yesterday, but then it wouldn't work when I loaded it in the published app. Today, when I run it in the editor, it shows an error of invalid operation/query is not valid. Here is my code:

Clear(colRecords); //clears the colRecords collection
    ClearCollect(colNumberPulls, //create collection of date ranges
        AddColumns(AddColumns( //add columns for start and end of date range
        Sequence( //starting on a launch date of 1/1/21, create a row for each quarter of the year until the current quarter
        RoundUp(
            DateDiff(DateValue("01/01/2021"),Today()) / (365 / 4),

            0)
        ),
"DateStart", //add column of start of date range
DateAdd(DateValue("12/01/2020"),Value * 3,Months)),
"DateEnd", //add second column, which is 3 months after the DateStart value.
DateAdd(DateStart,3,Months)
));

//this collection results in date & time stamp saved to both of the date fields.

//This is the For All loop that fetches data from a SP list where a date field falls within the ranges
ForAll(colNumberPulls,
    Collect(colSPRecords,
        Filter(SPListName,
            Status = "Closed",
            'Publish Date' > DateStart,
            'Publish Date' <= DateEnd
        )
    )
);

The "Filter(SPListName..." portion is all highlighted with the mentioned error. "The requested operation is invalid. Server Response: SPListName failed. The query is not valid." Troubleshooting, it DOES get data if I hard-code a date in place of the DateStart and DateEnd fields in the last filter section. So, it's as if it's failing to recognize that those fields are date values or where those values are pulling from?

[WarrenBelz]

Hi @rmclaughlin ,

I just got this to run fine on a test list with a date field

With(
   {
      wStart: DateValue("12/01/2020"),
      wSeq: 
      RoundUp(
         DateDiff(
            DateValue("01/01/2021"),
            Today()
         ) / 91.25,
         0
      )
   },
   With(
      {
         wNumberPulls: 
         AddColumns(
            AddColumns(
               Sequence(wSeq),
               "DateStart",
               DateAdd(
                  wStart,
                  Value * 3,
                  Months
               )
            ),
            "DateEnd",
            DateAdd(
               DateStart,
               3,
               Months
            )
         )
      },
      ClearCollect(
         colSPRecords,
         ForAll(
            wNumberPulls,
            Filter(
               SPListName,
               Status = "Closed" &&
               DateTest > DateStart &&
               DateTest <= DateEnd
            )
         )
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[rmclaughlin · Accepted solution]

Thanks, this solution works with a minor tweak:

ForAll(
         wNumberPulls,
         Collect(
         colSPRecords,
         
            Filter(
               SPListName,
               Status = "Closed" &&
               DateTest > DateStart &&
               DateTest <= DateEnd
            )
         )
      )
   )
)

 The For All needs to precede the collection of data, otherwise it spits out a table of just 4 rows (the # of rows generated in the the With section.

For those curious, the issue I was having was due to a DateTime vs. Date field issue. The 'Publish Date' field on SharePoint is saved as a "Date Only" field as opposed to a "DateTime" field, and so the ForAll in my original formula was struggling to compare the to. (I don't know WHY, because they're both rooted in date info.)

The workaround I found is to find some way to make sure that when you're first collecting the rows of date ranges to a PowerApps collection, be sure that the dates are being stored as date-only info vs date-time. And be wary that once you save a date or datetime to a collection, it doesn't matter if you clear the collection, it will still remember what type it was using. The fastest way to get around that issue is to have it go into a new collection (so instead of saving back to a collection called "test", you might instead save to a collection called "test1"). Otherwise, you have close and reopen the app or test after publishing.

Clear(colNumberPulls);
UpdateContext(
    {
        vTempVal: DateAdd(
            DateValue("12/1/2020"),
            3,
            Months
        )
    }
);
UpdateContext(
    {
        vTempVal2: DateAdd(
            vTempVal,
            3,
            Months
        )
    }
);
ClearCollect(
    colNumberPulls,
    {
        Value: 0,
        DateStart: vTempVal,
        DateEnd: vTempVal
    }
);

 This method feels a bit workaround-y, but it does work by defining a variable as a date value and then storing those initial date values to the collection before creating the additional rows of date ranges. Then once you've finished pulling the data you want, you can delete the row with Value = 0.