0

I have a calendar in SP Online. I'm operating on it with a flow. To keep the load limited, I try to filter items when I use GetItems by using ODATA-filtering. This works on most columns I created, on EventDate and EndDate.

But it doesn't when I try to filter for the ModerationStatus. I'm using the ModerationStatus, as entries have to be approved.

{
"@odata.etag": "\"2\"",
"ItemInternalId": "25",
"ID": 25,
"Title": "ietnen",
"EventDate": "2020-07-13T04:00:00Z",
"EndDate": "2020-07-13T06:00:00Z",
"EventType": 0,
"Mitarbeiter": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
  "Claims": "i:0#.f|membership|Max.Koenig@contoso.com",
  "DisplayName": "Koenig, Max",
  "Email": "Max.Koenig@contoso.com",
  "Picture": "https://contoso.sharepoint.com/sites/bungsteam/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Max.Koenig@contoso.com",
  "Department": "SMT-ETEI",
  "JobTitle": null
},
"Mitarbeiter#Claims": "i:0#.f|membership|Max.Koenig@contoso.com",
"Raumnutzung": [
  {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    "Id": 0,
    "Value": "45-3"
  }
],
"Raumnutzung@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
"Raumnutzung#Id": [
  0
],
"Raumnutzung#Id@odata.type": "#Collection(Int64)",
"Editcounter": 0,
"Editbybot": true,
"VControl": 2,
"ParticipantsPicker": [],
"ParticipantsPicker@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser)",
"ParticipantsPicker#Claims": [],
"ParticipantsPicker#Claims@odata.type": "#Collection(String)",
"Modified": "2020-07-13T06:20:30Z",
"Created": "2020-07-13T06:19:53Z",
"Author": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
  "Claims": "i:0#.f|membership|Max.Koenig@contoso.com",
  "DisplayName": "Koenig, Max",
  "Email": "Max.Koenig@contoso.com",
  "Picture": "https://contoso.sharepoint.com/sites/bungsteam/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Max.Koenig@contoso.com",
  "Department": "SMT-ETEI",
  "JobTitle": null
},
"Author#Claims": "i:0#.f|membership|Max.Koenig@contoso.com",
"Editor": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
  "Claims": "i:0#.f|membership|Max.Koenig@contoso.com",
  "DisplayName": "Koenig, Max",
  "Email": "Max.Koenig@contoso.com",
  "Picture": "https://contoso.sharepoint.com/sites/bungsteam/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Max.Koenig@contoso.com",
  "Department": "SMT-ETEI",
  "JobTitle": null
},
"Editor#Claims": "i:0#.f|membership|Max.Koenig@contoso.com",
"{Identifier}": "Lists%252fRaumkalender1%252f25_.000",
"{IsFolder}": false,
"{Thumbnail}": {
  "Large": null,
  "Medium": null,
  "Small": null
},
"{Link}": "https://contoso.sharepoint.com/sites/bungsteam/_layouts/15/listform.aspx?PageType=4&ListId=551e7570-c2a8-461c-a780-bf620cc93fb9&ID=25&ContentTypeID=0x010200DD4EB6A2F8DB4045A9B8456D9B8B36AE",
"{Name}": "ietnen",
"{FilenameWithExtension}": "ietnen",
"{Path}": "Lists/Raumkalender1/",
"{FullPath}": "Lists/Raumkalender1/25_.000",
"{ModerationStatus}": "Pending",
"{ContentType}": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedContentType",
  "Id": "0x010200DD4EB6A2F8DB4045A9B8456D9B8B36AE",
  "Name": "Ereignis"
},
"{ContentType}#Id": "0x010200DD4EB6A2F8DB4045A9B8456D9B8B36AE",
"{HasAttachments}": false,
"{VersionNumber}": "2.0"

},

An item looks like above. Note the curly braces around the ModerationStatus. What is the purpose of the curly braces?

I try to filter:

Raumnutzung eq '@{items('Alle_Räume_abarbeiten')}' and EventDate le '@{triggerBody()?['EndDate']}' and EndDate ge '@{triggerBody()?['EventDate']}' and Genehmigungsstatus eq '@{items('ModerationStatus')}'

All other filters have been tested in advance. Error message reads:

{
 "status": 400,
 "message": "Die Spalte 'ModerationStatus' ist nicht vorhanden. Möglicherweise wurde sie von einem anderen Benutzer gelöscht.\r\nclientRequestId: 68d403cc-2f76-44fb-bbac-fcd2501315aa\r\nserviceRequestId: 68d403cc-2f76-44fb-bbac-fcd2501315aa"
}

I tried to find out an internal name of the column, but without success.

1

Internal name of moderation status column is OData__ModerationStatus. So you need to use this in your filter query.

It has below values

0 = Approved

1 = Rejected

2 = Pending

3 = Draft

So, in REST APIs you would use a URL ending with an OData filter command like:

.../items?$filter=OData__ModerationStatus eq 0
| improve this answer | |
  • Interesting thing. Worked! I had some difficulties and realised I had to remove the single quotes which I had to use for all other filters. Do I have to prepend OData__ on all columns which have curly brackets in the JSON block? – Ariser - reinstate Monica Jul 20 at 12:56
  • 1
    No, you should always check the internal name of your field before using it in REST APIs or if it is giving you an error saying the column doesn't exist. – Ganesh Sanap Jul 20 at 13:04
  • How do I find out internal names of such obscure columns like this one, which don't show up within the settings of a list? I remember faintly there's some way to see all site columns as a list, isn't there? – Ariser - reinstate Monica Jul 20 at 19:53
1

The correct internal name for ModerationStatus column is OData__ModerationStatus. It is usually hidden from the UI.

That's why you are facing the error that column doesn't exist .

Please change it to OData__ModerationStatus, there is double under-score here , and it should work.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.