Hi Thien Phan‌ ,

This is the expected behavior.

Your query contains the AND logic which means that if all the conditions are true then only it will return the value.

So to overcome this, you can add the set a condition action which will check if the Contract Number is Empty or Not.

If its Empty, filter it based on 2 columns (Document Type and Modality).

If its Not Empty, filter it based on 3 columns  (Document Type , Modality and Contract Number)

Regards,

Saud Khatri

Hi Saud,

Thanks for your reply. Actually, I also thought about your advice already. Unfortunately, as you can see in my Document Search list, I have up to 5 attributes, so it seems to be so difficult to do that as I want users to be flexible to use that search list. We can set up 5 filters according to 5 attributes, but they can enter values into few filters of them or all of them, it so flexible.

Regards!

then you need to check two conditions for each column (attribute):

column is empty/null OR column == VALUE

and that needs to done for each of 3 columns.

such a set of  conditions is executable by query list action itself. you just need to properly configure them.

since the overall condition is at the end quite complex, it's easier to type it manually into CAML Editor instead of bother yourself clicking it up with CAML Builder.

the CAML query might look like follows

<Query>
  <Lists>
    <List ID="{EE937E09-7C85-4B44-A20B-2D0855582374}" />
  </Lists>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <Where>
    <And>
      <Or>
        <Or>
          <IsNotNull>
            <FieldRef Name='colA' />
          </IsNotNull>
          <IsNotNull>
            <FieldRef Name='colB' />
          </IsNotNull>
        </Or>
        <IsNotNull>
          <FieldRef Name='colC' />
        </IsNotNull>
      </Or>
      <And>
        <And>
          <Or>
            <IsNull>
              <FieldRef Name='colA' />
            </IsNull>
            <Eq>
              <FieldRef Name='colA' />
              <Value Type='Text'>A1</Value>
            </Eq>
          </Or>
          <Or>
            <IsNull>
              <FieldRef Name='colB' />
            </IsNull>
            <Eq>
              <FieldRef Name='colB' />
              <Value Type='Text'>B1</Value>
            </Eq>
          </Or>
        </And>
        <Or>
          <IsNull>
            <FieldRef Name='colC' />
          </IsNull>
          <Eq>
            <FieldRef Name='colB' />
            <Value Type='Text'>C5</Value>
          </Eq>
        </Or>
      </And>
    </And>
  </Where>
</Query>

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

for my sample list like

it gives following result

so it return all the items where colA == 'A1' or is empty, AND colB == 'B1' or is empty, AND colC == 'C5' or is empty.

at the beginning of the query I added condition that it doesn't return items where all 3 columns are empty.

so there are items ID=1 and ID=6 that fit to the query

‌ ‌ empty or value‌ ‌ optional fields‌ empty fields‌ null‌ is null‌ is not null‌ ‌

Hi Marina

Thank you so much. It works

Thien