Answered

How to retrieve a column value from a sharepoint file based on date criteria

(0) Share

Report

Posted on by AP-03101741-0

Hi,

I am trying to figure out how to pull out a column of data from a sharepoint site file based on the current date and a date range.

I have the following in the sharepoint file(I can remove the header if it makes the solution simpler and a excel file)

Iteration Start End

1 12/6/2023 12/19/2023

2 12/20/2023 01/02/2024

3 01/03/2024 01/16/2024

4 01/17/2024 01/30/2024

5 01/31/2024 02/13/2024

I have the flow running every Thursday and need to get the current date and return the Iteration it falls in.

For example if today is 12/28/2023, it should return the value 2, since current date >= Start and current date<= End.

All responses (6)

Answers (2)

Verified answer

AP-03101741-0 on at

Like (0)

Report

Re: How to retrieve a column value from a sharepoint file based on date criteria

This is the solution.
v-yetonggu-msft on at

Like (0)

Report

Re: How to retrieve a column value from a sharepoint file based on date criteria

Hi @PowerTony ，
Did I solve your first problem? If you solve it, please mark my answer as a solution. Your newly raised questions need to be opened another post.

Best Regards,
Sunshine Gu
AP-03101741-0 on at

Like (0)

Report

Re: How to retrieve a column value from a sharepoint file based on date criteria

Thanks, the quotes fixed that issue to initialize the array and was able to pull the row from the array where the Iteration = 1, based on Start.

My question now is how to I get the value 1 from the Iteration field from the following and store in a variable to be used in a future step ?:

Now, I want to change that filter to pull the record where the CurrentDate is >= Start and CurrentDate is < = End, I assume I don't need the advanced editor for this ?
Verified answer

v-yetonggu-msft on at

Like (0)

Report
Re: How to retrieve a column value from a sharepoint file based on date criteria

Hi @PowerTony ,
Your date is missing double quotes:
I did a test for your reference.
In my scenario:
[ { "Iteration": "1", "Start":"12/06/2023", "End":"12/19/2023" }, { "Iteration": "2", "Start":"12/20/2023", "End":"01/02/2024" } ]
@And(greaterOrEquals(utcNow('yyyy-MM-dd'), formatDateTime(items('Apply_to_each')?['Start'], 'yyyy-MM-dd')),lessOrEquals(utcNow('yyyy-MM-dd'), formatDateTime(items('Apply_to_each')?['End'], 'yyyy-MM-dd')))
length(body('Filter_array')) items('Apply_to_each')?['Iteration']
Best Regards,
Sunshine Gu
AP-03101741-0 on at

Like (0)

Report

Re: How to retrieve a column value from a sharepoint file based on date criteria

Part of the question is how to read through a sharepoint file for different rows and compare their values to the current date.

(BTW, I am open to not using a sharepoint file, but creating an 3 dimensional array and using that to compare against, but would need help on creating it and how to compare the items in the array to the current date and pulling out the value I need and storing that in a new variable).

I started this, but wasn't sure the next steps to check against the current date and pull out the value, so I put this step in first and got an error:

"The variable 'Iteration' of type 'Array' cannot be initialized or updated with value of type 'String'. The variable 'Iteration' only supports values of types 'Array'.

Here is the raw input:
[
{
"Iteration": "1",
"Start": 12/06/2023,
"End":12/19/2023
},
{
"Iteration": "2",
"Start":12/20/2023,
"End":01/02/2024
}
]
Matthy79 4,178 Super User 2024 Season 1 on at

Like (0)

Report

Re: How to retrieve a column value from a sharepoint file based on date criteria

Hello @PowerTony

You already answered your question yourself.

current date (utcnow) >= (greater than or equal) Start and current date (utcnow) <= (less than or equal) End

Use it during get items as a filter.

And you will have to format utcnow to only have the date part or use something like “startOfDay” or “beginOfDay” or whatever Microsoft named it. 😉