CAML Query and Conditions

6 years ago
25 August 2017
9 replies
16 views

andreapasutti
94 replies

Hi.

I have a workflow that routes financial documents and I need to query an approver's list based on whether the minimum amount each approver is responsible for is less than or equal to the amount of the request. For example:

Role Person Minimum Amount

Regional Director Bob Smith 0.00

Vice President Jane Doe 500,000.00

The Regional Director will approve all requests that come through because the minimum amount they are required to approve is $0.00. However, only requests that are 500,000 or more will go to the Vice President. If a request is $499,999.00, it will not go to the VP because it is less than, but not equal to $500,000, the amount the VP is responsible for approving.

This works just fine, only now the team wants to throw in a condition:

Role Person Minimum Amount Terms > 60 Days

Regional Director Bob Smith 0.00 No

Vice President Jane Doe 500,000.00 Yes

The VP will only receive requests that are $500k or more AND if the terms are > 60 days. If a request is for $500k or more and the terms are not > 60 days, it will not route to the VP.

(The terms are a Yes/No checkbox on the request and in the approver's list, so if terms = terms, it will find the approver.)

Query before:

Minimum Amount <= Amount Requested

Query now:

Minimum Amount <= Amount Requested

Amount Requested >= $500,000 AND Terms = Yes

I'm not sure how to write my CAML query. This does not work:

<Or>

<Geq>

<FieldRef Name="Range" />

<Value Type="Number">{ItemProperty:Total_x0020_Sales_x0020_Amount}</Value>

</Geq>

<Eq>

<FieldRef Name="Term_x0020__x003e_60_x0020_Days_" />

<Value Type="Boolean">Yes</Value>

</Eq>

<Or>

<Leq>

<FieldRef Name="Range" />

<Value Type="Number">{ItemProperty:Total_x0020_Sales_x0020_Amount}</Value>

</Leq>

</Or>

</And>

I get this error message:

The execution returned an unexpected error.
Cannot complete this action.

Please try again.

I know that OR statements in CAML get tricky. I want all requests to go through all approvers on the list if the amount requested is less than or equal to the minimum amount each approver is responsible for, but I only want requests to route to the VP if the amount requested is greater than $500,000.00 and if the terms are checked "Yes".

Any suggestions?

9 replies

1. Get all items from 'financial documents' list using 'Query List' action and store 'ID' field in 'Coolection variable. (don't apply filter conditions in query list action)

2. For each 'list item id' in collection variable get 'Minimum Amount' and 'Terms > 60Days' values.

3. Then use 'Set a condition' or 'Run If' actions to perform filter conditions (perform comparison).

Userlevel 5

+14

MegaJerk
Scholar
820 replies
6 years ago
29 August 2017

Assuming that I read everything correctly, and you'd like to only route things to the VP when they:

1. Are Greater Than or Equal To $500,000.00

2. Terms = Yes

Then it's a simple matter targeting those exact conditions, and then targeting the opposite of those conditions (for your Regional Director).

Here is a sample list:

Looking over this, there is only one item (Transaction - 03) which would meet the conditions that would fulfill the requirements for the VP to handle it, as described in the rules above.

To query that in a CAML Query, you could use the following...

<Query>
  <Lists>
    <List Title="Main List Test" />
  </Lists>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <Where>
   <And>
    <Geq>
      <FieldRef Name="Amount" />
      <Value Type="Text">500000</Value>
    </Geq>
    <Eq>
      <FieldRef Name="Terms" />
      <Value Type="Boolean">Yes</Value>
    </Eq>
    </And>
  </Where>
</Query>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

(it should be noted that I am using hard-coded values for the threshold value of $500,000.00 rather than a variable)

The results will be our single expected ID!

On the other hand. Now that we know how to get our VP cases. We need a query that will grab everything else. Those conditions could be described as follows.

1. Terms = No

2. Amount < 500,000 AND Terms = Yes

Because we know that the only time that we'll ever send something the VP's way is when the Terms have been set to "Yes", we know that if the they are set to "No", no matter the Amount of money involved, they will not be routed to the VP.

Additionally, because we know that the VP doesn't handle anything that has an amount below $500,000, we should also grab any item that has a value under that, even if the Term has been set to "Yes"!

Following that logic results in the following CAML Query:

<Query>
  <Lists>
    <List Title="Main List Test" />
  </Lists>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <Where>
    <Or>
      <Eq>
        <FieldRef Name="Terms" />
        <Value Type="Boolean">No</Value>
      </Eq>
      <And>
        <Lt>
          <FieldRef Name="Amount" />
          <Value Type="Text">500000</Value>
        </Lt>
        <Eq>
          <FieldRef Name="Terms" />
          <Value Type="Boolean">Yes</Value>
        </Eq>
      </And>
    </Or>
  </Where>
</Query>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Which will give us the following results from our list:

Note that the logical operators in a CAML Query can only compare (2) expressions. Using colors it can become more obvious as to what that means.

<Where>
   <Or>
      <Eq>
         <FieldRef Name="Terms" />
         <Value Type="Boolean">No</Value>
      </Eq>
      <And>
         <Lt>
            <FieldRef Name="Amount" />
            <Value Type="Text">500000</Value>
         </Lt>
         <Eq>
            <FieldRef Name="Terms" />
            <Value Type="Boolean">Yes</Value>
         </Eq>
      </And>
   </Or>
</Where>

OR( Terms = "No" , AND ( Amount < 500,000, Terms = "Yes" ) )

I hope that this helps you in your future querying!

Hi! Thank you so much. I am trying to get this to work, but I'm not successful. If the terms are "No", give me this result. If the terms are "Yes", give me this result AND that result if the amount is greater than 500,000. It seems to be pulling everything. I'm not sure I'm understanding the CAML correctly. This is my full query:

<Query>

<Lists>

</Lists>

</ViewFields>

<Where>

<And>

<Or>

<Eq>

<Value Type="Lookup">{ItemProperty:Platform}</Value>

</Eq>

</IsNull>

</Or>

<Eq>

<Value Type="Lookup">{ItemProperty:Global_x0020_Business_x0020_Unit0}</Value>

</Eq>

</And>

<Eq>

<Value Type="Lookup">{ItemProperty:Region}</Value>

</Eq>

</And>

<Eq>

<Value Type="Lookup">{ItemProperty:Country}</Value>

</Eq>

</And>

<Eq>

<Value Type="Lookup">{ItemProperty:Location}</Value>

</Eq>

</And>

<Value Type="MultiChoice">{ItemProperty:Terms_x0020_Request_x0020_Type}</Value>

</Contains>

</And>

<Gt>

<Value Type="Number">{WorkflowVariable:doaStep_num}</Value>

</Gt>

</And>

<Or>

<Eq>

<FieldRef Name="Term_x0020__x003e_60_x0020_Days_" />

<Value Type="Boolean">No</Value>

</Eq>

<And>

<Lt>

<FieldRef Name="Range" />

<Value Type="Text">500000</Value>

</Lt>

<Eq>

<FieldRef Name="Term_x0020__x003e_60_x0020_Days_" />

<Value Type="Boolean">Yes</Value>

</Eq>

</And>

</Or>

</Where>

</OrderBy>

</Query>

Like I said, it pulls back everything. Should I be replacing some of the hard coded information with my variables?

Userlevel 5

+14

That's because you have not nested the <Or> block (colored Orange) correctly within your <And> block, and it is being ignored. So all you're doing is pulling the results generated by everything ABOVE the last <Or> block, which is giving your bad results.

I believe this should work.

<Query>
  <Lists>
    <List ID="{LISTLISTLISTLISTLIST}" />
  </Lists>
  <ViewFields>
    <FieldRef Name="ID" />
    <FieldRef Name="Title" />
    <FieldRef Name="Person" />
    <FieldRef Name="Step" />
    <FieldRef Name="StepType" />
    <FieldRef Name="GBUorCorporate" />
  </ViewFields>
  <Where>
    <And>
      <Eq>
        <FieldRef Name="GBU" />
        <Value Type="Lookup">{ItemProperty:Global_x0020_Business_x0020_Unit0}</Value>
      </Eq>
      <And>
        <Eq>
          <FieldRef Name="Region" />
          <Value Type="Lookup">{ItemProperty:Region}</Value>
        </Eq>
        <And>
          <Eq>
            <FieldRef Name="Country" />
            <Value Type="Lookup">{ItemProperty:Country}</Value>
          </Eq>
          <And>
            <Contains>
              <FieldRef Name="Active_x0020_for_x0020_Request_x" />
              <Value Type="MultiChoice">{ItemProperty:Terms_x0020_Request_x0020_Type}</Value>
            </Contains>
            <And>
              <Eq>
                <FieldRef Name="Location" />
                <Value Type="Lookup">{ItemProperty:Location}</Value>
              </Eq>
              <And>
                <Gt>
                  <FieldRef Name="Step" />
                  <Value Type="Number">{WorkflowVariable:doaStep_num}</Value>
                </Gt>
                <And>
                  <Or>
                    <Eq>
                      <FieldRef Name="Platform" />
                      <Value Type="Lookup">{ItemProperty:Platform}</Value>
                    </Eq>
                    <IsNull>
                      <FieldRef Name="Platform" />
                    </IsNull>
                  </Or>
                  <Or>
                    <Eq>
                      <FieldRef Name="Term_x0020__x003e_60_x0020_Days_" />
                      <Value Type="Boolean">No</Value>
                    </Eq>
                    <And>
                      <Lt>
                        <FieldRef Name="Range" />
                        <Value Type="Text">500000</Value>
                      </Lt>
                      <Eq>
                        <FieldRef Name="Term_x0020__x003e_60_x0020_Days_" />
                        <Value Type="Boolean">Yes</Value>
                      </Eq>
                    </And>
                  </Or>
                </And>
              </And>
            </And>
          </And>
        </And>
      </And>
    </And>
  </Where>
  <OrderBy>
    <FieldRef Name="Step" Ascending="true" />
  </OrderBy>
  <ViewAttributes Scope="Recursive" />
</Query>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Userlevel 5

+14

Did my solution work for you?

Yes, thank you! I got it. CAML is tricky. I will mark it as the correct answer.

Hi, nmarples.

The team just started testing this workflow after all this time and the query does not work. It still seems to be ignoring the terms.

All approvals will route based on GBU, Platform, Region, Country, and Location.

If the terms are "no", it should go to the first approver regardless of dollar amount, and get their approval.

If the terms are "yes" or "no", the workflow should go to the second approver (the terms selection in the approver's list is "ALL"), who will receive all requests regardless of dollar amount or terms. According to their current approval setup, the workflow should stop after the second approver.

The 3rd step of the approval will be driven by dollar amount and terms. Only if the terms are "yes" will it advance and find approvers based on their approval threshold.

I have revised the query as follows, but it still isn't working. What am I doing wrong? This is making me crazy.

<Query>

<Lists>

</Lists>

</ViewFields>

<Where>

<And>

<Or>

<Eq>

</Eq>

<Eq>

<Value Type="Choice">{WorkflowVariable:terms_text}</Value>

</Eq>

</Or>

<Or>

<Eq>

<Value Type="Lookup">{ItemProperty:Platform}</Value>

</Eq>

</IsNull>

</Or>

</And>

<Eq>

<Value Type="Lookup">{ItemProperty:Global_x0020_Business_x0020_Unit}</Value>

</Eq>

</And>

<Eq>

<Value Type="Lookup">{ItemProperty:Region}</Value>

</Eq>

</And>

<Eq>

<Value Type="Lookup">{ItemProperty:Country}</Value>

</Eq>

</And>

<Eq>

<Value Type="Lookup">{ItemProperty:Location}</Value>

</Eq>

</And>

<Value Type="MultiChoice">{ItemProperty:Terms_x0020_Request_x0020_Type}</Value>

</Contains>

</And>

<Gt>

<Value Type="Number">{WorkflowVariable:doaStep_num}</Value>

</Gt>

</And>

<Leq>

<Value Type="Number">{ItemProperty:Total_x0020_Sales_x0020_Amount}</Value>

</Leq>

</And>

</Where>

</OrderBy>

</Query>

Userlevel 5

+14

MegaJerk
Scholar
820 replies
6 years ago
12 December 2017

It often helps to address these types of bugs by using the Run Now option provided to you by the Query List Action, and testing chunks of your CAML Query to make sure that it's working. Even if it's not returning the results that you'd like, or even expect, it's a great way to know that something should work.

While I can't test your CAML Query on my own, the first and most obvious problem that I see is that you have referenced the same Field twice, but given two different Value Types respectively:

<Or>
  <Eq>
    <FieldRef Name="Term_x0020__x003e_60_x0020_Days_" />
    <Value Type="Lookup">All</Value>
  </Eq>
  <Eq>
    <FieldRef Name="Term_x0020__x003e_60_x0020_Days_" />
    <Value Type="Choice">{WorkflowVariable:terms_text}</Value>
  </Eq>
</Or>‍‍‍‍‍‍‍‍‍‍

"Term_x0020__x003e_60_x0020_Days_" which, according to the CAML Query I created above was a Boolean (meaning that it was a Yes/No Column) is referenced here in one condition as a Lookup, and in the other as a Choice value.

Did this field actually change from a Boolean to a different type? It is unlikely to be the case unless you deleted it and recreated it afterwards (which would leave the internal SharePoint column as being the same), and even then, it would still need to be one or the other.

Correct that type mistake, and then take each block of conditions and test them before putting them back together into a large ball of logic.
----------------

Another thing that was curious in your question are the following conditions:

If the terms are "no", it should go to the first approver regardless of dollar amount, get their approval only, and end.

If the terms are "yes" or "no", the workflow should go to the second approver, who will receive all requests regardless of dollar amount or terms.

These do not make sense. According to your first statement, if the Terms are set to "No" then after the First Approver, everything STOPS.

Your next line goes on to contradict that by saying that there is a Second Approver when the Terms are set to "No".

Which is it?

Depending on your needs, you might have to make TWO or more queries to properly sort which items are meeting which criteria. If you're trying to test against multiple criteria, there is no way to separate them using just a single query!!!!!

----------------------

I hope that this helps you to get back in going towards a good direction.

Hi, .

I have clarified my question. I did type that wrong.

I will take a look at the Terms in the code. We didn't see that we've chosen 2 different options.

I was afraid we might not be able to do this with one query, but I'm not giving up yet. Let me tweak my code and get back to you.

CAML Query and Conditions

9 replies

Reply

View our Community Site Map

Reply

View our Community Site Map

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded