0

In the below query after adding date condition the query returns error - Cannot complete this action. Please try again..

    "<View><Query><Where><And><Eq><FieldRef Name='Company' /><Value Type='Text'>" + CompanyName + "</Value></Eq>" +
    "<And><Eq><FieldRef Name='License' /><Value Type='Text'>" + License + "</Value></Eq>" +
    "<And><Eq><FieldRef Name='Active' /><Value Type='Boolean'>0</Value></Eq> <IsNull><FieldRef Name='ReplacementUserID'/></IsNull >" +
    "<And><Geq><FieldRef Name='LicenseExpiryDate'/><Value IncludeTimeValue='FALSE' Type='DateTime'><Now /></Value></Geq>" +
    "</And></And></And></And></Where></Query><RowLimit>100</RowLimit></View>"
Improve this question
5
  • 1
    I think you should add one more <And> before <IsNull><FieldRef Name='ReplacementUserID'/> or add closing </And> after that condition (as per your requirements/conditions). You are doing it wrong here.
    – Ganesh Sanap - MVP
    Feb 9, 2021 at 9:20
  • Hi @GaneshSanap, when i try that i get the following error - One or more field types are not installed properly. Go to the list settings page to delete these fields. The query was working fine before i added the 4th(last) <and></and> tag, ie. for date comparision.
    – Priya
    Feb 9, 2021 at 9:42
  • 1
    <And> element can have only two child elements (two conditions). so, you have to change the query accordingly
    – Ganesh Sanap - MVP
    Feb 9, 2021 at 10:03
  • The query contains one and max two child elements inside <and> tag.
    – Priya
    Feb 9, 2021 at 10:10
  • Check my answer below.
    – Ganesh Sanap - MVP
    Feb 9, 2021 at 10:11

2 Answers 2

Reset to default
1

<And> element can have only two child elements (two conditions). Your query should be in below format:

<View>
    <Query>
        <Where>
            <And>
                <Eq><FieldRef Name='Company' /><Value Type='Text'>CompanyName</Value></Eq>
                <And>
                    <Eq><FieldRef Name='License' /><Value Type='Text'>License</Value></Eq>
                    <And>
                        <Eq><FieldRef Name='Active' /><Value Type='Boolean'>0</Value></Eq>
                        <And>
                            <IsNull><FieldRef Name='ReplacementUserID'/></IsNull >
                            <Geq><FieldRef Name='LicenseExpiryDate'/><Value IncludeTimeValue='FALSE' Type='DateTime'><Now /></Value></Geq>
                        </And>
                    </And>
                </And>
            </And>
        </Where>
    </Query>
    <RowLimit>100</RowLimit>
</View>

Add variables, + and " accordingly.

Documentation: CAML Query schema

Improve this answer
3
  • Same error - Cannot complete this action. Please try again. I have added variables, + and " accordingly
    – Priya
    Feb 9, 2021 at 10:19
  • What is the data type of ReplacementUserID??
    – Ganesh Sanap - MVP
    Feb 9, 2021 at 10:37
  • 1
    Hi, placing <IsNull></IsNull > after <Geq></Geq> worked for me. Thanks
    – Priya
    Feb 10, 2021 at 10:25
1

Change your caml query to the following, check if it works for you:

<Query>
    <Where>
        <And>
            <And>
                <And>
                    <Eq><FieldRef Name='Company' /><Value Type='Text'>" + CompanyName + "</Value></Eq>
                    <Eq><FieldRef Name='License' /><Value Type='Text'>" + License + "</Value></Eq>" +
                "</And>
                <Eq><FieldRef Name='Active' /><Value Type='Boolean'>0</Value></Eq> <IsNull><FieldRef Name='ReplacementUserID'/></IsNull >" +
            "</And>
            <Geq><FieldRef Name='LicenseExpiryDate'/><Value IncludeTimeValue='FALSE' Type='DateTime'><Now /></Value></Geq>" +
        "</And>             
    </Where>
</Query>
Improve this answer
1
  • Hi @Michael Han_MSFT, The above query returns all data from the list without applying the conditions. Not sure why.
    – Priya
    Feb 9, 2021 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.