How to compare a SharePoint list value to an Excel Table value

[Anonymous]

Hello,

I am trying to structure a Get items step in my Flow that will get items from a SharePoint list if the Project ID in the SharePoint list matches the Project ID in an Excel table and if the Extract Date in the SharePoint list matches the Extract Date in an Excel table, but it's returning the following error:  "message": "The expression \"Project ID eq 'PRJ-XXXXXXXX' Extract Date eq '2020-08-03'\" is not valid. Creating query failed.

The date format expression I'm using in the Filtery Query is formatDateTime(addDays('1900-01-01',add(int(items('Apply_to_each')?['Extract Date']),-2)),'yyyy-MM-dd')

Any ideas why the overall expression isn't working?

[abm]

Hi @Anonymous 

If you have spaces between the field then use the below.

Project ID to Project_x0020_ID & Extract_x0020_Date

Thanks

[v-litu-msft]

Hi @Anonymous,

And if there is multiple conditions in the filter query, please use "and" to connect them, for example:

Column1 eq 'value1' and Column2 eq 'value2'

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Given that I'm comparing to a value within a SharePoint list, should I be inserting dynamic content from SharePoint, that would have a SharePoint icon the way the Excel item does? Also, in order for a comparison of items in a column to work, do the items in a SharePoint list column and Excel table column need to be sorted the same way? 

[abm]

Hi @Anonymous 

Thanks for your reply. You don't need to sort the list or excel sheet for the ODATA query.

Did you tried the _x0020_ options I suggested in my earlier post?

Thanks

[Anonymous]

Yes. I'm still not able to get it to work. Did you mean to replace the dynamic content with _x0020_? If so, why? 

[abm · Accepted solution]

Hi @Anonymous 

Sharepoint column with space names need _x0020_ as an internal schema name. You can't use the display name which contains spaces to filter the ODATA.

Please have a look at this

https://blog.softartisans.com/2009/12/08/list-of-internal-names-for-sharepoint-fields/

Thanks

[Anonymous]

Thank you, @abm and @v-litu-msft 

The Flow is now running without errors.

[Anonymous]

@abm  Do you think I'm using the correct step in my flow (circled) if after the comparison in the filter query in the Condition to Append or Replace step, I want to get items from an Excel table and insert them in to my SharePoint list where those filter query conditions are met? Or would I need to use a Get Items from Excel step, assuming one exists?

[abm]

Hi @Anonymous 

It looks ok to me. Is that working as expected?

Thanks

[Anonymous]

The "Yes" branch is working fine and the "No" branch doesn't return errors any more, but the steps in the "No" branch don't update rows in SharePoint when both the Project ID in the SharePoint list matches the Project ID in the Excel table and the Extract dates in both match as well. That's why I'm wondering if under the "No" branch I should be using a Get items from Excel step instead of a Set items from the SharePoint list.

[abm]

Hi @Anonymous 

Thanks for the reply. Under the no condition I can see there is a ODATA filter you using? Are there any records it returning?

[Anonymous]

@abm  Thanks for your reply! When the "No" condition is satisfied, i.e. when the ETL Action is not equal to new, the flow is appending all rows to the SharePoint list except for the rows where I've changed the ETL action to <> New in the Excel table. What I want the Flow to do is to replace those existing rows in the SharePoint list because I've changed the ETL action from "New" to "Update" and I've entered some financial information that didn't previously exist in the SharePoint list for those rows.

I truncated the last list of items but it does contain the UpdatedDate mentioned in the Filter Query above.