Multiple complex Filter using dropdowns and blank columns

[marty1984]

Hi,

Hoping someone can assist with a problem I am having with a formula and filtering gallery results.

I have a lost property register within an app which works across multiple contracts. The data used in the app is stored in dataverse across multiple tables, as the app has other functions.

The main landing screen of the app allows the user to select their contract, and then all subsequent galleries, choices etc need to be managed from the first selection made in the dropdown, which is called ddContracts.

I currently have the Lost Properties gallery filtered to display the items relating to that contract, and it works displaying all items from that contract

Search(
Filter(
'Lost Properties',
'Contract ID'.'Contract Name' = ddContracts.Selected.Result
),
TextSearchBox5.Text,
"crXXX_description",
"crXXX_foundby"
)

I would like the gallery to also be able to be filtered by the result of a column in the table 'Lost Properties' which is called 'Claimed Time Stamp' (which is a date/time column). Ultimately, if 'Claimed Time Stamp' is empty, then the item should be visible and if there is an entry for 'Claimed Time Stamp', then the entry should be filtered out as the item is no longer 'active' as a lost property item. 

I have tried with the Is Blank() but I am getting confused with the syntax due to the Search & Filter already being applied to ensure only the correct contract is on display.

Also, the text search box is currently only able to search by the columns "crXXX_description and "crXXX_foundby" to filter the results. I would like to be able to use other attributes (e.g. Site Location and Found at) but these are Look-Up columns in the 'Lost Properties' table and I get the delegation warning, even though I have increased the records setting, and there would not be over 2000 records. 

Any help would be greatly appreciated. 

[StalinPonnusamy]

Hi @marty1984 

Please try this

Search(
Filter(
'Lost Properties',
'Contract ID'.'Contract Name' = ddContracts.Selected.Result
&& IsBlank('Claimed Time Stamp))
),
TextSearchBox5.Text,
"crXXX_description",
"crXXX_foundby"
)

Thanks,
Stalin - Learn To Illuminate

[marty1984]

Thanks @StalinPonnusamy 

I think I just confused myself in attempting to apply this. One minor edit in the proposed solution

Search(
Filter(
'Lost Properties',
'Contract ID'.'Contract Name' = ddContracts.Selected.Result
&& IsBlank('Claimed Time Stamp')
),
TextSearchBox5.Text,
"crXXX_description",
"crXXX_foundby"
)

 - I removed one close parenthesis after ISBlank('Claimed Time Stamp') and added a ' in its place and the formula worked perfectly.  

[StalinPonnusamy · Accepted solution]

Hi @marty1984 

That's Correct. Good to hear. We are using notepad when responding and sometimes leads to these kinds of issues. Generally, I used to write comments and Indent as well like

Search(
     Filter(
        'Lost Properties',
         'Contract ID'.'Contract Name' = ddContracts.Selected.Result
         && IsBlank('Claimed Time Stamp')
            ), //Filter
  TextSearchBox5.Text,
  "crXXX_description",
  "crXXX_foundby"
) //Search

Please let us know if anything needs on your post. We can help with this.

Please mark the post as Solved If I have answered your question.
Please give it a Thumbs Up if you find the suggestion helpful

Thanks,
Stalin - Learn To Illuminate