GetItems() Odata query filter with formatdatetime()

[Calcioscacchi]

Hi there. 

I have a sharepoint list wich had 2 datetime columns, "Inizio" and "Fine".

my getItems() function should take only the rows where the "Inizio" date is not equal to the "Fine" date. 

I tried with 

formatDateTime(['Inizio'],'dd/MM/yyyy') ne formatDateTime(['Fine'], 'dd/MM/yyyy')

without success. 

Some hints to do it?

Thanks.

[ScottShearer]

@Calcioscacchi 

I don't believe that you can directly compare two columns in filter query.

[Calcioscacchi]

Well it could be an interesting development. As I can compare a column to a value, what if that value is taken from another column? 

[abm]

Hi @Calcioscacchi 

Alternate way to do this is use the Get Items without the filter. Then next action step add Filter array. Here use the formatDateTime() expression to 'yyyy-MM-dd' , so in your case

formatDateTime(['Inizio'],'yyyy/MM/dd')

formatDateTime(['Fine'],'yyyy/MM/dd')

[Calcioscacchi]

Could be a nice hint. 

I need to study how it does work because:

-I need to get confident with ConvertTimeZone() function, because sometimes my input is 14/02/2023 00:00:00 but I live in UTC+1 region so powerautomate converts it in 13/02/2023T23:00:00. The day changes.

-I need to study Filter array function and how to use its output in a cicle.

[abm]

Hi @Calcioscacchi 

Try the below for convert time zone. Of course you can use the expression as well.

[Calcioscacchi]

Yeah, I used the expression inside the filter array block and it works fine. 

Now I have to understand how to pass the filter array to my next steps, in my previous flow I use the whole "value" items from GetItem() function but it means A LOT of time to finish the flow (there are almost 10000 rows to check and only a few of them needs to be modified).

Now i'm trying to pass to the UpdateItem() function just the filter array in order to process only the rows with Inizio != Fine.

Honestly it sounds tricky: I have a filtered array with the information about the IDs of the row I want to edit. 

How can I say to my UpdateItem() function to edit ONLY the items which have the IDs taken from my filtered array?

[abm]

Hi @Calcioscacchi 

Is this going to be a one time update only? Then declare all the Id's in a compose action step with comma separated (use split() expression to split the ids in an array format) or declare all in one array. You could iterate each item and use it under the update item.

Thanks

[Calcioscacchi]

Well yes, I need to edit few rows but just one time. It's not a time recurrence flow.

I just create a easy flow as example.

In this example I filter all the elements with "TEST_ARRAY" = B in order to edit them to "A".

This is my example flow (not working), don't worry if you don't see the part where I update to "A", it is out of screen XD.

As I aspected, I have to manage with filter array output. Can you give me an example of what you explained above? I'm sorry but I don't understand your solution, I'm quite newbie in power automate.

[abm]

Hi @Calcioscacchi 

Could you please let me know the ID is the internal Id of the record or a custom id which you maintain? 

[Calcioscacchi]

Is the ID of my row, automatically generated in sharepoint list when I create an element.

I suppose that it is the first one you asked for.

[abm]

Hi @Calcioscacchi 

Thanks for your quick reply. I know you want to update the items. Yes using the Id we can update the list items. Where you going the the source of data for the update? How you going to filter the source data?

[Calcioscacchi]

I'll try to answer. 

This is a part of my list: as you can see only one element (ID = 146, 124, 137) has the "B" value. I want to get all items from my list, filter only the ID 146 and then update it with "A" value.

To do this, I have to pass someway the list of my IDs to updateelements(), or my flow will update all the items of my list.

Of course, I have thousands of row and it could be more "B" value somewhere.

[abm]

Hi @Calcioscacchi 

Thanks for the clarifications.

There are different ways you could do this. 

Here is the flow.

Expression I used above is

int(item())

In your case Set column value 'Test_Array' to 'A'

Thanks

[Calcioscacchi]

Ok I got it. 

It is a good hint if, for example, I extract my list in excel and then I take all the IDs that I need. It was my second strategy, getItem() with multiple IDs.

But, You wrote a compose function knowing the IDs numbers. 

How to create the same thing, with the output of filter array? 

[abm]

Hi @Calcioscacchi 

Are you going to replace the all the values from 'B' to 'A'?

[abm]

Hi @Calcioscacchi 

Add apply to each loop and map the output of filter array. To get the items from the filter array use the below expressions

item()?['ColumnName']

eg: item()?['TEST_ARRAY']

Thanks

[Calcioscacchi]

The filter array have to get all items where it fouds "B" values. 

This means that filter array output will be a matrix and the ID will be a field of that matrix.

My goal is to use the IDs fileter by filter array and use them to update my rows.

In your previous example, you use a compose function to make an array, but you write it manually knowing the IDs. 

I would like that the same array (the one with the IDs) would be builted automatically with all the IDs taken from filter array block

[abm · Accepted solution]

Hi @Calcioscacchi 

See below 

You may need to insert the Get Item action step like I explained earlier for mapping the other mandatory values from the list item.

Thanks

[abm]

Hi @Calcioscacchi 

Also you could filter by using the ODATA query