cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Martin_W
Helper III
Helper III

Filtering a table using a lot of queries!

Hi,

 

I have a table called Assets, containing items of equipment.

 

Each item is subject to certain annual inspections - there are six different inspections that are needed.

 

The owner of the equipment specifies which contractor they want to carry out each of these six inspections. They may select the same contractor for each inspection, or they might use six different contractors. That's up to them.

 

The Contractors also have access to the app, so that they can fill in the report reference of the inspection and enter the expiry date of the inspection.

 

I only want each contractor to be able to see items of equipment where they are named (by the user) as the contractor for at least one of the inspection.

 

I tried using a Filter statement in the DataTable Items property (for for the purposes of simplicity I'm just going to refer to TestCondition1 through TestCondition6)

 

Filter(AssetTable, TestCondition1, TestCondition2, TestCondition3, TestCondition4, TestCondition5, TestCondition6)

However, the whole section of code was underlined in blue with a message about having exceeded the maximum number of entities. I had to delete the code and actually redefine the DataTable object to get rid of this error.

 

So, if I can't use Filter with six logical tests, how can I do this? I did wonder if i might run two three-item Filters and somehow join the table back up again but this would give me possible problems with the integrity of the data.

 

Any suggestions please?

 

thanks

 

Martin

 

1 ACCEPTED SOLUTION

Accepted Solutions
facupola
Frequent Visitor

Hi @Martin_W. Did you try concatenating the condition statements?. Using Or / And

 

Example:

Filter(Accounts, 'Industry' = ComboBox3.Selected.Industry||IsBlank(ComboBox3.Selected.Industry), 'Relationship Type' = ComboBox2.Selected.'Relationship Type'|| IsBlank(ComboBox2.Selected.'Relationship Type'), 'Preferred Method of Contact' = ComboBox1.Selected.'Preferred Method of Contact'|| IsBlank(ComboBox1.Selected.'Preferred Method of Contact'))

 

If you have the option to make some filters on the data model side first, it will be the best. There are different ways, or maybe you can use PowerAutomate.

 

Hope it helps.

 

View solution in original post

2 REPLIES 2
facupola
Frequent Visitor

Hi @Martin_W. Did you try concatenating the condition statements?. Using Or / And

 

Example:

Filter(Accounts, 'Industry' = ComboBox3.Selected.Industry||IsBlank(ComboBox3.Selected.Industry), 'Relationship Type' = ComboBox2.Selected.'Relationship Type'|| IsBlank(ComboBox2.Selected.'Relationship Type'), 'Preferred Method of Contact' = ComboBox1.Selected.'Preferred Method of Contact'|| IsBlank(ComboBox1.Selected.'Preferred Method of Contact'))

 

If you have the option to make some filters on the data model side first, it will be the best. There are different ways, or maybe you can use PowerAutomate.

 

Hope it helps.

 

Martin_W
Helper III
Helper III

Hi,

 

Thanks for the swift reply!

 

I think I have it working now. I have used "exactin" instead of "=", used an Or between each test, and that seems to be working!

Helpful resources

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