cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
feilda
Helper IV
Helper IV

pulling column dates in a month

I am building a flow on a library that has an expirables column.  I want to know how to  query for all expirables by month within 30 days of expiration.  For example, i want to query the list so that it will pull out all August expirable dates on July 1. Can you help me understand how i can do this?

9 REPLIES 9
v-LilyW-msft
Community Support
Community Support

Hi @feilda 

Thank you for posting.

According to your description you want to select all items that expire next month.

You can create Scheduled cloud flow, run on the first day of every month. Then use the month as the judgment condition to filter out the items that you want.

I have set up the flow likes below for your reference.

v-LilyW-msft_0-1621910514750.png
v-LilyW-msft_1-1621910514753.png

Expression on the left of Condition: formatDateTime(items('Apply_to_each')?['expiringdate'],'MM')

Expression on the right of Condition: formatDateTime(addToTime(utcNow(),1,'Month'),'MM')

v-LilyW-msft_2-1621910514756.png

v-LilyW-msft_3-1621910514760.png

Then save Flow and can test it.

 

This is my list

v-LilyW-msft_4-1621910514762.png

After my flow runs, Items expired in June will be created in the new list.

v-LilyW-msft_5-1621910514763.png

 

Hope the content above may help you.

Best Regards

Lily

feilda
Helper IV
Helper IV

Thank you this is helpful.  I have a library and not a list so i tried it with Get files(properties only) and it says the expression formulas are invalid.

Hi @feilda 

Sorry for misunderstanding. I created a simple test again and it works fine for me.

This is my library

v-LilyW-msft_0-1622010097424.png

Flow in details:

v-LilyW-msft_4-1622010119462.png

Note:Please change ‘Expiringdate’ to the name corresponding to your expirables column.

Expression on the left of Condition: formatDateTime(items('Apply_to_each')?['Expiringdate'],'MM')

Expression on the right of Condition: formatDateTime(addToTime(utcNow(),1,'Month'),'MM')

v-LilyW-msft_2-1622010097434.png

After my flow runs, files expired in June will be created in the new library.

v-LilyW-msft_3-1622010097436.png

Best Regards

Lily.

Expirables 2.PNG

for my expressions I am using:

Expression on the left of Condition: formatDateTime(items('Apply_to_each')?['Renewal_0020_Date],'MM')

Expression on the right of Condition: formatDateTime(addToTime(utcNow(),1,'Month'),'MM')

I get the following error msg when I test it.

 

Expirables 2A.PNG

My Renewal Date column is a date only column so i am not sure if that could be influencing this.

Hi @feilda 

Please put single quotes on Renewal_0020_Date right and try again

v-LilyW-msft_0-1622076863652.png

Best Regards

I already have single quotes on the expression where you are  pointing to.  Sorry i mistyped it when i entered into this message system. I have the following: formatDateTime(items('Apply_to_each')?['Renewal_0020_Date'],'MM').  I get the error message when i run it with this.

 

 

Hi @feilda 

Could you share the column name screenshot of your library with me?

Please make sure your column name ‘Renewal_0020_Date’ is internal name.

Every column in SharePoint has 2 names, internal name assigned by SharePoint and display name defined by you.

About how to find it internal name, you can refer to the screenshots as below:

1、Select Settings , and then select Library settings.

v-LilyW-msft_1-1622453116587.png

2、select the column

v-LilyW-msft_2-1622453116591.png

3、Then can find the internal name is ‘RenewalDate’ of Renewal Date.

v-LilyW-msft_3-1622453116594.png

Best Regards

Lily

the front end name is Renewal Date

the backend name is Renewal_x0020_Date

I have tried it using the front end name and then because it did not work i tried it with the back end name, but it did not make a difference.

Helpful resources

Announcements
 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 (2,440)