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. – Ganesh Sanap Feb 9 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 at 9:42
  • 1
    <And> element can have only two child elements (two conditions). so, you have to change the query accordingly – Ganesh Sanap Feb 9 at 10:03
  • The query contains one and max two child elements inside <and> tag. – priya Feb 9 at 10:10
  • Check my answer below. – Ganesh Sanap Feb 9 at 10:11
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 at 10:19
  • What is the data type of ReplacementUserID?? – Ganesh Sanap Feb 9 at 10:37
  • 1
    Hi, placing <IsNull></IsNull > after <Geq></Geq> worked for me. Thanks – priya Feb 10 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 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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