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.

View solution in original post

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

View solution in original post

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
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 296 members 4,878 guests
Please welcome our newest community members: