MS Flow ODATA filtering fails on ModerationStatus

Question

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.

Answer 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

You can get the internal name of any of your list/library columns by following this article: How to find the Internal name of columns in SharePoint Online?

Answer 2

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.