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>"
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. 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 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. Feb 9, 2021 at 10:11

2 Answers 2

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

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