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

Datatable not displaying data filtered using text value

Hello Community,

 

This is my source Table (Sheet1):

REF_DATEREF_VAL
201601022300
201601022300

 

On the receiving DataTable I use this filter:

Filter(Sheet1,REF_DATE="20160102")

I also tried: Filter(Sheet1,REF_FECHA=20180430)

 

However, I get no Data, please see attached image.

 

Hope someone can help.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Ok, working on it, it is a combination of things, now both max(DatePicker1_1. and min(DatePicker1) dates are returned.  The problem now is to have, the VENTAS column sum all values, instead of just showing them.

 

v2 DataTable from PoweApps.png

 

Filter(Filter(Sheet1,DEPTO=Text(TitleDepto)),

DateValue(FECHA,"[$-en-US]dd/mm/yyyy") >= DateValue(Text(DatePicker1.SelectedDate),"dd/mm/yyyy") && DateValue(FECHA,"[$-en-US]dd/mm/yyyy") <= DateValue(Text(DatePicker1_1.SelectedDate),"dd/mm/yyyy"))

 

It is a learning curve, hopefully the Format function will be on a upcomming release, so as to avoid mixing various functions to return a value.

 

Thanks Again Nick

 

View solution in original post

4 REPLIES 4
Highlighted
Helper I
Helper I

Is your filter function in the Items property of the Gallery you are using to display these records or somewhere else?

What is the type of REF_DATE ?

 

Have a look at the scenario below

 

Filter( IceCream, "chocolate" in Lower( Flavor ) )Returns records where the word "chocolate" appears in the Flavorname, independent of uppercase or lowercase letters.
Highlighted
Community Support
Community Support

Hi @soloposmenu_PA,

Could you please share a bit more about the REF_DATE column in your source Table (Sheet1)?

Have you formatted the REF_DATE column value as a Date value in your source Table?

I have made a test on my side, and don't have the issue that you mentioned. The screenshot as below:1.JPG

Set the Items property of the Data Table to following:

Filter('20181228_case12', Text(DateRequired) = "12/28/2018")

If you have formatted your REF_DATE column value as a Date value in your source Table already, please take a try to set the Items property of your Data Table control to following:

Filter(Sheet1, Text(REF_DATE) = "20160102")

Or

Filter(Sheet1, "20160102" in REF_DATE)

You could also consider take a try to save your Sheet1 data source into a Collection, then take a try with above formulas, check if the issue is solved. Please take a try with the following formula:

Set the OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, Sheet1)

Set the Items property of your Data Table control to following:

Filter(RecordsCollection, Text(REF_DATE) = "20160102")

Or

Filter(RecordsCollection, "20160102" in REF_DATE) 

 

If the issue still exists, please consider take a try to re-create a new connection to your source table (Sheet1) from your app, then try above formulas I provided, check if the issue is solved.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Thank you for your help. REF_FECHA is a string value yyyymmdd. FECHA is a date value dd/mm/yyyy.

 

I was able to get the FECHA working with the = operator, like this:

Filter(Filter(Sheet1,DEPTO = Text(TitleDepto)),Text(FECHA) = Text(DatePicker1.SelectedDate,"[$-en-US]dd/mm/yyyy"))

 

However, when I use the >= operator, I get the error of "Invalid argument type. Expecting a number value"

Filter(Filter(Sheet1,DEPTO = Text(TitleDepto)),Text(FECHA) >= Text(DatePicker1.SelectedDate,"[$-en-US]dd/mm/yyyy"))

 

Please see bellow. https://powerusers.microsoft.com/t5/media/gallerypage/image-id/47352iA5613D0B4377530D

 

Highlighted

Ok, working on it, it is a combination of things, now both max(DatePicker1_1. and min(DatePicker1) dates are returned.  The problem now is to have, the VENTAS column sum all values, instead of just showing them.

 

v2 DataTable from PoweApps.png

 

Filter(Filter(Sheet1,DEPTO=Text(TitleDepto)),

DateValue(FECHA,"[$-en-US]dd/mm/yyyy") >= DateValue(Text(DatePicker1.SelectedDate),"dd/mm/yyyy") && DateValue(FECHA,"[$-en-US]dd/mm/yyyy") <= DateValue(Text(DatePicker1_1.SelectedDate),"dd/mm/yyyy"))

 

It is a learning curve, hopefully the Format function will be on a upcomming release, so as to avoid mixing various functions to return a value.

 

Thanks Again Nick

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

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