Get Items from a SharePoint List

(0) Share

Report

Posted on by Community Power Pla...

I have a Custom SharePoint list called 'Event Form' with mulitple fields.

I would like to create an automatic Flow that emails a set of records and some of their fields.

I am okay with the reccurance and the Email aspect, but I seem to be tripping up on the Get Items tab:

I don't want to retrieve all entries, just those that have been created within the last 7 days.

I would also like to order them by date on that particular record.

Any help would be greatly appreciated and I can provide further info if needed.

Categories:

Building flows

I have the same question (0)

All responses (11)

Answers (1)

jhall 636 on at

Like (1)

Report

This post is a decent starting reference for filtering by date:

https://powerusers.microsoft.com/t5/Building-Flows/FLOW-Get-Items-Filter-SharePoint-list-on-Date/td-p/85238

This one also addresses subtracting days:

https://powerusers.microsoft.com/t5/Building-Flows/expiry-date-should-equal-minus-30-days-formula-todaysdate-30/td-p/60296

Is that what is tripping you up?

To ensure you're getting what you want you can do a variable step and create a String variable and assign it the value of the date you're looking for similar to: @{formatDateTime(addDays(utcNow(),-7),'yyyy-MM-dd')}

Then when you do the GetItems make it similar to: TestDate gt '@{variables('SevenDaysAgo')}'
This will test if the value in the column TestDate is > than 7 days ago.

One thing you might miss is the single quotes around the date value.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thank you @jhall

I have followed your methods, but I appear to be getting the following message. Any idea why this may be?

The expression "21-01-2019" is not valid.
clientRequestId: 3567a8f0-28e1-4cdb-9cd7-1955b96a5a65
serviceRequestId: ab6cba9e-f07a-8000-a4e8-aa38908b7ec4

Was this reply helpful? Yes No
jhall 636 on at

Like (0)

Report
Two likely issues.

I'm guessing that you did not include the SharePoint column that you are querying against. In my example I was using a column I created in my SharePoint list called TestDate.
Remember the quotes around the date value. Even if you have this in a variable, it has to be included inside quotes for Flow to recognize it as a Date.

Again, see my example I did:
You need to include in your own Filter Query:
The name of the SharePoint column you are querying against (TestDate in my example)
The operation you are using to compare the two values (e.g. gt, eq, lt, etc.)
The string variable (SevenDaysAgo in my example), inline calculated value, or raw string (e.g. '21-01-2019') of the date inside single quotes (I'm not 100% certain if double quotes will work - just haven't tested).
I also realize that you're potentially dealing w/ a date value that is localized for your region. You have yours configured as dd-MM-yyyy (i.e. day-month-year). From a style and universality perspective, I generally try to do things as yyyy-MM-dd as this helps w/ an array of things per good habits across platforms.

But now I'm just getting picky. 😉

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@jhallOkay, I realise I may be a little out of my depth but im persevering.

The name of the column in my SP list is General On-Sale

I'm also confused about your point 2. The operation you are using to compare the two values (e.g. gt, eq, lt, etc.)
What do those abbreviations mean?

And yes im from the UK so using localised formatting.

Many Thanks for your help.

Was this reply helpful? Yes No
Verified answer

jhall 636 on at

Like (0)

Report
No worries. This gets complex and the UI within Flow makes it seem easy, but then blows up at the weirdest times. I've got a number of rants and workarounds for PowerApps/Flow that I try to write up solutions on one of my blogs so I won't forget the weird thing I ran into that one time. It just takes time to adapt to some of the quirks of this platform. Particularly when it seems to work so poorly w/ SharePoint at times.

You're running into ANOTHER problem SharePoint issue, which is spaces in your column name. Fix this by replacing any spaces in the column name with _x0020_. So in your Filter Query field change General On-Sale to General_x0020_On-Sale.

See a further discussion here: https://powerusers.microsoft.com/t5/Building-Flows/OData-syntax-for-sharepoint-column-with-a-space-Get-Items-action/td-p/147705

The abbreviations are what Flow uses for comparisons:
gt = >
lt = <
eq = = (I find this line particularly funny - something might be wrong with me)
The syntax here is highly annoying for what is supposed to be a 4GL automation environment. For the life of me, I don't understand how they truly believe a Business Analyst w/ limited to no real-world programming experience would be capable of working within Flow for anything beyond the basics.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@jhall haha yes i know the feeling!

Unfortunately, still didnt work.

Value:
formatDateTime(addDays(utcNow(),-7),'dd-MM-yyyy')

The date in my "General Onsale" column in my list is todays date.

Error received = The expression "General_x0020_On_x002d_Sale gt 22-01-2019" is not valid.
clientRequestId: 87a9ce8e-71aa-45f0-846a-ad490bcd1875
serviceRequestId: a0b5ba9e-0042-8000-a4e8-acedddfd585f

I will keep persevering, thanks again for your help,

Yours : frustrated budding Programmer 🙂

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

@jhall It was those godamn quotes! They must have jumped out when I edited it previously.

Although now it is timing out:

"message": "String was not recognized as a valid DateTime.\r\nclientRequestId: 3d1d1e52-97c0-40fa-806c-b37fed97421d\r\nserviceRequestId: 86b8ba9e-f063-8000-ac3a-0196ccfdb0f6"

*puts head on table*

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

And this was due to my localised date formatting!

Was this reply helpful? Yes No
jhall 636 on at

Like (1)

Report

Make sure you put that date in quotes! 😄

In your post you have:
Once you put some quotes around the date you've included it will work. Look closely at mine as I have a single quote before and after my SevenDaysAgo variable:

Fix that and you're golden.

##EDIT: And of course now I see you caught that. You're now officially a programmer. 😄

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@jhall Haha almost! Now I have the task of making the output look user friendly but I have all the information I need so I'm happy with that. Thanks again for all your help today! 🙂

Was this reply helpful? Yes No