cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrtnM
Helper V
Helper V

get items from last year

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Convert the value to int.

 

Try the below

 

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

View solution in original post

22 REPLIES 22
abm
Super User
Super User

Hi @MrtnM 

 

Please follow the below steps:

 

image.png

 

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


Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
fchopo
Super User
Super User

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.

2020-02-04 11_05_15-Window.png

Hope it helps.

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

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.

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?

Hi @MrtnM 

 

Is that apov is a datetime column?



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

@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.

 

Namnlöss.pngNamnlös.png

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.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

@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:

Namnlös.png

 

the flow:

ew.png

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

the output:

Namnlöst-2.jpg

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

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

Copy this under the expression

 

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

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

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.'.

ddfgdfg.png

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

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

 

sdfsdsdf.png

abm
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Thought so to earlier, but no:

 

 

Namnlöst-2.jpg

 

dsfdfgdfgdfg.png

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

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)

 

111.png

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

 

Convert the value to int.

 

Try the below

 

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (3,711)