cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Nkilgor
Level: Powered On

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

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

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

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

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

Nkilgor
Level: Powered On

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

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

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

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

Nkilgor
Level: Powered On

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

Filter(Tracker, IsToday(Date))

 

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

Nkilgor
Level: Powered On

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

The search function is still putting up a fight!

 

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

 

is still throwing errors

 

Anonymous
Not applicable

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

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?

Nkilgor
Level: Powered On

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

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

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

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

Nkilgor
Level: Powered On

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

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
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (6,256)