get items from last year

[MrtnM]

HI,

I'd like to perform an "update item" action on every item in a list that is created two years ago or earlier (i e: this year this would correspond to 2018 or earlier, for 2025 it will correspond to 2023 or earlier).

I suppose the setup must be: Recurrence trigger - Get items - Update item.

What is the correct expression for the filter? And where do I put it: in the Get items as a Filter query, in a separate Data operation Filter array, or even in Condition?

[abm]

Hi @MrtnM 

Please follow the below steps:

The above expressions I used are as follows:

formatDateTime(item()?['Created'],'yyyy')

sub(int(formatDateTime(utcNow(),'yyyy')), 2)

If you need any further help in this please let me know.

Thanks

[fchopo]

Hello @MrtnM 

You can use Get Past time action to get the date (two years ago), store in a variable where it will be formatted as string, and then use the Get Items SharePoint action, applying a filter where created column is less or equal (le operand) to the specified date. After that, you could iterate over each item and update it.

Hope it helps.

Ferran

[MrtnM]

Hi @fchopo , 

and thanks.

Before I try this; does this really get the items that are created "anytime during the year that was 2 years ago", and not items that are "2 years old"? I would like this to work for items created from Jan 1 to Dec 31 in the specific year.

[MrtnM]

Thanks, @fchopo  and @abm, I was wondering about another variety of the setup, that I also might need:

to get items where the information in a Alternatives column designates the year that was 2 years ago, or earlier. This column is called "apov", and the information is a year in numbers, but this is manually chosen from alternatives and thus not related to Created or Modified columns. Is it possible to filter items from this column as well? I tried the suggestion from @abm 

formatDateTime(item()?['apov'],'yyyy')
sub(int(formatDateTime(utcNow(),'yyyy')), 2)

 but of course that did not work.

Any ideas?

[abm]

Hi @MrtnM 

Is that apov is a datetime column?

[MrtnM]

@abm  - no, sadly not, then it would be easy, I think.

"apov" is an alternatives/options column, where the options are numbers that are years.

[abm]

Hi @MrtnM 

In that case you don't need to do formatDateTime(item()?['apov'],'yyyy'). Directly map the apov in your filter and use the other format expression for finding the current year minus 2.

[MrtnM]

@abm ,

thanks - I tried this, but only got zero output from that? (Same result if I use the "apov" or the "apov value")

the items:

the flow:

sub(int(formatDateTime(utcNow(), 'yyyy')), 2)

the output:

[abm]

Hi @MrtnM 

Not sure this is a data type issue or not. Could you convert the apov to int (Integer) using the expression and see whether that works or not?

Thanks

[MrtnM]

@abm  - I sure could ... if I knew how to 🙂 Sorry, I think I need some guiding to do that 🤔

[abm]

Copy this under the expression

int(item()?['apov'])

[MrtnM]

Like this? Didn't work:

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(int(item()?['apov']), sub(int(formatDateTime(utcNow(), 'yyyy')), 2))' failed: 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

[abm]

Could you please tell me what's the type of this column?

[MrtnM]

I call it alternatives column, but the correct English word is, I think, Choice.

[abm]

Hi @MrtnM 

Thanks for your reply.

For choice fields you need to select the suffix with 'value'. You should be able to see adv value under dynamic properties.

Directly map this value and see.

[MrtnM]

Thought so to earlier, but no:

[abm]

Hi @MrtnM 

I did tried this from my end as well. So it looks like you can't filter using apov Value. Believe there is some restrictions due to choice field. The only option is remove the filter array option, add a for each loop and check using the apov Value. This should work.

Thanks

[MrtnM]

THANKS @abm for all the effort you put into this!

You mean like this? This time the Flow processed every item in the list, came out successful but didn't update anything.

sub(int(formatDateTime(utcNow(), 'yyyy')), 2)

And these are some of the items, the "kolumn 2" in the apov 2017 and 2018 should be updated but are not.

[abm · Accepted solution]

Convert the value to int.

Try the below

int(items('Apply_to_each')?['apov']?['Value'])