The requested operation is invalid. SharePoint Form

[RTW]

I have created an app for tracking sick calls and other absences. Its piloted right now and there are some changes to be made before it rolls out but I've encountered a bit of an issue with it.

When an associate calls in sick, a manager logs it in the app. The information is stored on SharePoint. The information and data type captured is

1) Associate name (Person)

2) Date of shift (Date, not the "Created" column as calls associates may call in the day before a shift, for example)

3) Team - This is a text feild, which captures the Job Title when the associate is added to the form. Its stored separately for record keeping - in case the associate changes position

When viewing the app, there is a gallery that displays by default all absences for today. There are toggles set up to change the filters so that you can view all absences for all days, as well as absences for specific teams. When the "TeamToggle" value is TRUE, then a dropdown appears, which is where the user selects which team they are filtering for. This is where the error occurs.

If(DateToggle.Value=true, 
	Distinct(Filter(SickCalls, ShiftDate = Today()), Team),
	Distinct(SickCalls, Team)
)

When I view the app in the editor I get the following error.

The requested operation is invalid. Server Response: The expression "ShiftDate eq 2019-04-25" is not valid. 
clientRequestId: 718f7537-c937-4f65-9dda-d8b85390c2fd 
serviceRequestId: 536fd69e-7042-0000-37ec-8b4466c2af94

The dropdown does not populate with any items, which then breaks the filter. The issue is, this used to work. It works when I use the Web Player to use the app, it only seems to affect it when editing.

I assume this is something to do with the date format not jiving with Sharepoint, so I tried adding to the equation to account for that and it did not seem to work either.

Let me know if there is anything else I can do, I'm hesitant to work on this app right now lest I push this error out when I publish my changes.

[v-xida-msft · Accepted solution]

HI @RTW ,

Do you type the If(...) formula within the Items property of the Dropdown box?

Based on the issue that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. Please consider take a try to modify your formula as below:

If(
   DateToggle.Value=true, 
   Distinct(Filter(SickCalls, Text(ShiftDate, "mm/dd/yyyy") = Text(Today(), "mm/dd/yyyy")), Team),
   Distinct(SickCalls, Team)
)

please take a try with above formula, check if the issue is solved.

If the issue still exists, please consider take a try with the following workaround:

Set the OnStart property of App control or OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, SickCalls)   /* <-- Save your SickCalls list records into a Collection firstly */

Set the Items proeprty of the Dropdown box to following:

If(
   DateToggle.Value=true, 
   Distinct(Filter(RecordsCollection, Text(ShiftDate, "mm/dd/yyyy") = Text(Today(), "mm/dd/yyyy")), Team),
   Distinct(RecordsCollection, Team)
)

Please take a try with above solution, check if the issue still exists.

Also please consider take a try to re-create a new app based on your SP list, try above solution again, check if the issue is solved.

Best regards,

[RTW]

@v-xida-msft , thank you!

The first solution worked... sort of.

Your first solution, using the Text() commands fixed it and its working fine. Interestingly enough, while it was behaving as expected, I was still getting the same error message, even though it didn't seem to be affecting anything.

To satisfy my curisoity, I created a new app from blank, added a gallery and the same controlls as my original app. I then added my original formula and it worked. After I tested this, I went back to my original app, reverted it to the original formula, and had issue again.

I'm not sure why it occured all of a sudden, and why I am not able to recreate it.

The only thing I can think of is that my admin hasn't updated the CDS, but seeing as this app doesn't tap in to that, I'm not sure if that would affect anything.