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

Use of the Filter Function

I am using three 3 text boxes as the basis of my formula to refine my search in a form for a record. I get a delegation warning basically saying that it will not filter down.

The Filter command look like:

 

Filter('[dbo].[TSheet]', Tjobno=Value(TextInput23.Text),tdate=Today(),EmployeeId_1=Value(TextInput27.Text))

 

The attachment is a screen capture of the Form. The top 3 text boxes are the 3 items captured elsewhere in my app that I use to filter the data down to the required record. However nothing is returned due to the warning.

Lookup only returns one record, the first it finds (which is correct). However my technician is on day 3 or 4 of the same job number hence using Filter. The Filter command does not seem to work as documented where each condition is treated as an AND. Just using one condition in the filter does not work either.

 

All help is gratefully received.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Use of the Filter Function

Hi @PaulB1,

Could you please share a bit more about the tdate column in your SQL table? Is it a Date type column?

Are the Tjobno column and EmployeeId_1 column both Number type column in your SQL table?

Based on the screenshot and formula that you provided, I think there is something wrong with the second filter condition (tdate=Today()) within your Filter formula.

The date value format the Today() function returned is 'mm/dd/yyyy', but the date value format your tdate column returned is 'yyyy-mm-dd'.

I have made a test on my side, please modify your formula as below:

Filter(
'[dbo].[TSheet]',
Tjobno = Value(TextInput23.Text),
Text(tdate, "mm/dd/yyyy") = Text(Today(), "mm/dd/yyyy"), /* <-- Compare tdate value with current date in same date format */
EmployeeId_1 = Value(TextInput27.Text)
)

More details about the Text function, please check the following article:

Text function

 

Above formula may cause a Delegation issue, if you want to get rid of this Delegation warning issue, please take a try with the following workaround (if the amount of your SQL table records is not more than 2000):

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

ClearCollect(SQLTableCollection, '[dbo].[TSheet]')

Set the Items property of the Data Table to following:

Filter(
SQLTableCollection,
Tjobno = Value(TextInput23.Text),
Text(tdate, "mm/dd/yyyy") = Text(Today(), "mm/dd/yyyy"), /* <-- Compare tdate value with current date in same date format */
EmployeeId_1 = Value(TextInput27.Text)
)

 

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.
4 REPLIES 4
Super User
Super User

Re: Use of the Filter Function

A form cannot use a table as as an Item property even a table with one record unless it is explicitly restricted to one record with the First() or Last() function. If you put a datatable on the screen, you could use your filter on it, then choose a record for your form from there as the item property of the form.
PaulB1
Level: Powered On

Re: Use of the Filter Function

OK so I added the table to the screen and try to filter the records. The filter should return one record as per the file labeled Capture5.png.

However the app throws up a warning. Then any field I want to access using Lookup of course will not work.

I am not sure that Filter is using the conditions as an AND.

Community Support Team
Community Support Team

Re: Use of the Filter Function

Hi @PaulB1,

Could you please share a bit more about the tdate column in your SQL table? Is it a Date type column?

Are the Tjobno column and EmployeeId_1 column both Number type column in your SQL table?

Based on the screenshot and formula that you provided, I think there is something wrong with the second filter condition (tdate=Today()) within your Filter formula.

The date value format the Today() function returned is 'mm/dd/yyyy', but the date value format your tdate column returned is 'yyyy-mm-dd'.

I have made a test on my side, please modify your formula as below:

Filter(
'[dbo].[TSheet]',
Tjobno = Value(TextInput23.Text),
Text(tdate, "mm/dd/yyyy") = Text(Today(), "mm/dd/yyyy"), /* <-- Compare tdate value with current date in same date format */
EmployeeId_1 = Value(TextInput27.Text)
)

More details about the Text function, please check the following article:

Text function

 

Above formula may cause a Delegation issue, if you want to get rid of this Delegation warning issue, please take a try with the following workaround (if the amount of your SQL table records is not more than 2000):

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

ClearCollect(SQLTableCollection, '[dbo].[TSheet]')

Set the Items property of the Data Table to following:

Filter(
SQLTableCollection,
Tjobno = Value(TextInput23.Text),
Text(tdate, "mm/dd/yyyy") = Text(Today(), "mm/dd/yyyy"), /* <-- Compare tdate value with current date in same date format */
EmployeeId_1 = Value(TextInput27.Text)
)

 

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.
Administrator
Administrator

Re: Use of the Filter Function

Thanks for posting in the community @PaulB1 - can you review the latest reply and advise if it answers your question?

 

@TopShelf-MSFT

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 180 members 5,228 guests
Please welcome our newest community members: