AnsweredAssumed Answered

CAML Query and Conditions

Question asked by andreapasutti on Aug 25, 2017
Latest reply on Sep 13, 2017 by andreapasutti

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

OR

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>

      </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?

Outcomes