AnsweredAssumed Answered

CAML In Query Office 365 List Action - Count Distinct Rows

Question asked by jasonelia on Jan 22, 2015
Latest reply on May 9, 2017 by tposzytek

Hello -

 

I am trying to use Query Office 365 List action and am having a hard time with returning distinct rows.  My list contains multiple columns, but I want to return column 1 and column 2 where distinct.

 

As an example the list contains data such as this. 

 

Column 1
Column 2
Site 1Red
Site 2Red
Site 1Blue
Site 1Red

 

When I run the CAML query I get 3 records.  I want to consolidate this to only see 2 distinct records.

 

Sample ListCAML
Column 1
Column 2
Site 1Red
Site 1Blue
Site 1Red

<View>

    <Query>

        <Where>

            <Eq>

                <FieldRef Name="Service Site"/>

                <Value Type="Text">Site 1</Value>

            </Eq>

        </Where>

    </Query>

    <RowLimit>10000</RowLimit>

</View>

 

I would like to see the following returned, thinking i could use GroupBy to achieve that.  But when I manually type in the CAML it strips out the GroupBy.

 

Sample ListCAML
Column 1
Column 2
Site 1Red
Site 1Blue

<View>

    <Query>

        <Where>

           <GroupBy collapse="TRUE">

                <FieldRef Name="Service Site" />

           </GroupBy>

            <Eq>

                <FieldRef Name="Service Site"/>

                <Value Type="Text">Site 1</Value>

            </Eq>

        </Where>

    </Query>

    <RowLimit>10000</RowLimit>

</View>

 

Is there another preferred method for achieving this?  Or can some provide guidance as to how to accomplish this?

 

Thank you.

Outcomes