cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nkilgor
Frequent Visitor

Filter Gallery by Date from Sharepoint List, and Search by Lookup field

I have managed to get this to work with my sharepoint list but, this will be used as a daily tracker and the gallery needs only to display items that are for the current day.

SortByColumns(Filter(Tracker, StartsWith("Coach",TextSearchBox1.Text)), "Date", If(SortDescending1, Ascending, Descending))

 

i.e. 

Date              3/6/2017 - Date value from date picker

Manager       Person    - selected from lookup of another sharepoint list

Coach           Person    - selected from lookup of another sharepoint list

Sales            Text entry

 

I need the end user to be able to see only items created "Today" and  search for entries that match the Manager coloum.

 

Thank you,

Nate K

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Ok, first make sure our original filter query for IsToday() works against a normal text column;

 

Search(Filter(Tracker, IsToday(Date)), TextSearchBox1.Text, "TEXTCOLUMN")

 

If that works, let's mark that as the solution to the original question, then we can solve for the people column question.

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

Hi there,

 

Logically you should just be able to filter by IsToday() - which is handy because it strips complicated DateTimeStamps into a simple logical test - "is the day of this date the same as the day of today's date?"

 

If you want to Search then I would assume you want the user to type something in, so you'd tie the Search function to a TextBox Input.  

 

...and I'm not sure why you would want to sort by date if you've already filtered the list to only show today's items...?

 

Let's break it down - We check to see if the date is today with;

 

IsToday("Date")

Using this, we can then Filter our dataset by rows that have dates that match today's date

 

Filter(Tracker, IsToday("Date"))

  

Now we have a filtered dataset, we can wrap the Search function around it;

  

Search(Filter(Tracker, IsToday("Date")), TextSearchBox.Text, "Manager")

 

Where TextSearchBox is a textbox input you've added to the screen for user search input.

Lastly, if you still want to sort it, you can wrap the search in the SortByColumns function - although as I said, sorting by date at this point is probably pointless, unless you want to order by time of day. So...

 

SortByColumns(Search(Filter(Tracker, IsToday("Date")), TextSearchBox.Text, "Manager"), "Date", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))

 

 

Hope this helps 🙂

 

R

Thank you for the fast response, your assumtion was correct I do not need the sort once the filter works.

 

Filter(Tracker,IsToday(DateValue("Date")))

 

This just returns the Delegation warning and shows a blank gallery

 

 

Anonymous
Not applicable

Hah, you were too quick for me 🙂

I edited my response - DateValue was confusing the issue as my Dates were strings and I realised yours wouldn't be Man Embarassed

Filter(Tracker, IsToday(Date))

 

WORKS Smiley Very Happy I have literally spent 12 hours trying to understand this syntax!!!

The search function is still putting up a fight!

 

Search(Filter(Tracker, IsToday(Date)), TextSearchBox1.Text, "Manager")

 

is still throwing errors

 

Anonymous
Not applicable

Any particular kind?

 

Search probably won't work on a people/group column type natively - #justsaying 🙂

Try changing the Search to point at any other text column just to make sure. 

 

If you are using a SharePoint Person/Group column type, what you're pulling through is a complex object - not a string.  You would need to pull out a text value from the object to search against - i.e. pull out the FullName value of the People object and search against that - otherwise you're applying a string function to an object instead of a string.  Different conversation.

 

Other than that, while you might get a delegation warning, it's only relevant if you have more than 500rows of data being returned against which the filter is being applied.  If it is relevant, you may want to consider delegating the filter (not sure if that's working for SharePoint lists yet though!  Something for me to play with tonight :))  - also a different conversation 🙂

 

Can you say exactly what the errors are?

Yes the Manager column is a lookup from another list nested in Sharepoint,

I have Default set for

ThisItem.Manager

Value1=Value

Value2=@odata.type

Anonymous
Not applicable

Ok, first make sure our original filter query for IsToday() works against a normal text column;

 

Search(Filter(Tracker, IsToday(Date)), TextSearchBox1.Text, "TEXTCOLUMN")

 

If that works, let's mark that as the solution to the original question, then we can solve for the people column question.

its 3am here im calling it a night but I will update in the morning

 

ok so I just  attempted

Search(Filter(Tracker,IsToday(Date)), TextSearchBox1.Text,"AAL")

returns error "The function 'Search' has some invalid arguments."

 

I believe this may be a result of no actual TEXT in my sharepoint list

only lookup for Manager and Coach

then Date

and 2 decimal number columns

and one currency column

 

 

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,103)