Answered

Date Filter using Start and Expiration dates

(0) Share

Report

Posted on by Community Power Pla...

Hi all,

I've scanned through the threads but am unable to find a solution for my current problem. I'm very new to powerapps so feel it's probably an easy one.

I'm creating an app for my company (travel) to sort discount codes by continent / city and date. I've managed to get it to filter by continent and city and am happy with it, however I'm struggling to set up an appropriate date filter.

I've used the datepicker control so users can select a specific date to search from. The aim is to get it to filter the data list to find offers that are within date. I have two columns in the table (StartDate and ExpirationDate) and have tried to device a way of filtering it so the chosen date is >= StartDate and < ExpirationDate, however it errors saying 'Expecting Text or number'

Looking through the threads I've come ot the conclusion there can be a problem with tranlsation of date/time data and I can see the potential in this as the datepicker looks to format date as dd/mm/yyyy where as the dates in the table (pulling through from a Sharepoint List) are formatted mm/dd/yyyy.

I've played around with a few formula's and this is the closest i've got that makes sense to me, but the error still pops up:

Filter('Basic List', DateValue(Text(Start),"en") >= DateValue(Text(DatePicker1.SelectedDate)),"en" && DateValue(Text(Expiration_1),"en-gb") < DateValue(Text(DatePicker1.SelectedDate),"en"))

Date filter for Discountapp.PNG

Hopefully the above screenshot will give a clearer idea as i want to filter the data to only show what is within date.

Any ideas what i could try?

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Community Power Pla... on at

Like (0)

Report
Alright, I have a development on this. I've managed to come up with a formula that appears to work. This is based on reformatting the date of the Datepicker by going to Format and entering anew "mm/dd/yyyy" to match it up with the way SharePoint lists is putting it through. This has given me:

Filter('Test Data for powerapps',(DatePicker1.SelectedDate >= Start) && (DatePicker1.SelectedDate < Expiration))
This seems to have worked. The next thing I need to do is add this together with two other conditions (A name and a drop down menu). Specifically:

Filter('Test Data for powerapps', TextInput1.Text in Name1)
Filter('Test Data for powerapps',Price = Dropdown1.Selected.Value)
Filter('Test Data for powerapps',(DatePicker1.SelectedDate >= Start) && (DatePicker1.SelectedDate < Expiration))
Any ideas of the best way to combine these three?

Was this reply helpful? Yes No
Verified answer

v-monli-msft on at

Like (1)

Report
Hi @Anonymous ,

Since the syntax for Filter function is:

Filter( Table, Formula1 [, Formula2, ... ] )

Table - Required. Table to search.

Formula(s) - Required. The formula by which each record of the table is evaluated. The function returns all records that result in true. You can reference columns within the table. If you supply more than one formula, the results of all formulas are combined with the And function.

You could simply put these conditions together like this:

Filter('Test Data for powerapps', TextInput1.Text in Name1,Price = Dropdown1.Selected.Value,(DatePicker1.SelectedDate >= Start) && (DatePicker1.SelectedDate < Expiration))

Regards,

Mona

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

Like (0)

Report

Hi @v-monli-msft,

Thanks for that, I did a bit of work on it with one of the MS support team and managed to come up with the below:

Filter(Table1, (DatePicker1.SelectedDate >= StartDate) && (DatePicker1.SelectedDate < ExpirationDate))

We also reformatted the datepicker so it shows mm/dd/yyyy to match up with the date info.

Best regards,

Was this reply helpful? Yes No
Dawidvh 1,346 on at

Like (1)

Report

Hi @Anonymous

I think the best solution for this would be an integer column in SQL that converts the date to a number. Queries to date columns in SP are not delegatable (nor is calculated columns in SP), so if you have to use SharePoint for the data, the other option would be to use Flow to fetch the data and return it to PowerApps. Flow can use ODATA queries to get to the correct data and then possibly manipulate or join the data with other services before returning it to PowerApps. I love this approach and it has come in extremely handy in the past. Some might say that using a flow for retrieving data might feel 'expensive' but it does allow for many different functional and security options going forward.

See below demo what can be achieved with this. Please see below and let us know if you have any questions or suggestions:
https://youtu.be/m_j_xN7-LjU

Twitter: @dawidvh
YouTube: https://www.youtube.com/user/dawidvanheerden
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.

Was this reply helpful? Yes No