0

In my custom list i have columns month and year. Both are string column. This is my query to get data

 Query = @"   <Where><And><Eq><FieldRef Name='Status' /><Value Type='Boolean'>1</Value></Eq>
                                         <And><IsNotNull><FieldRef Name='month' /></IsNotNull>
                                         <IsNotNull><FieldRef Name='
                                 Year' /></IsNotNull>
                                         </And>
                                         </And>
                                     </Where>
                                     <OrderBy>

                                       <FieldRef Name='Year' Ascending='False' />
                                     </OrderBy>"

If i store data in list as

Month----------------Year

March----------------2016

April----------------2016

October----------------2016

Then the above query gives me below output

Month----------------Year

October----------------2016

March----------------2016

April----------------2016

It should be April before and then march. How can i solve this?

3 Answers 3

2

You aren't really going to be able to in that fashion with a string sort. You will need to make them 10 - October, 03 - March, 04 - April for them to sort correctly or introduce an ordering column as an alternate way to sort the values.

Month----------------Year

03 - March----------------2016

04 - April----------------2016

10 - October----------------2016

You are also missing a sort in your CAML, you'd want it to be:

<OrderBy>
   <FieldRef Name='Year' Ascending='False' />
   <FieldRef Name='Month' Ascending='True' />
</OrderBy>
7
  • how can i make them as 10-october 03 runtime? Oct 1, 2015 at 12:17
  • by the way i just use month and year Oct 1, 2015 at 12:17
  • my both columns are strings. So you mean i create calculated column based on these two columns? But still how can i do it with calcualted column? Oct 1, 2015 at 12:21
  • No, I mean physically change the month values to 03 - March or create a new sort order column that you give correct sort order for. Oct 1, 2015 at 12:22
  • Sorry bit unsure still. You mean user will select 03-March from dropdown? Oct 1, 2015 at 12:24
0

If you are allowed to create a column in list, then create a calculated column with formula as

=IF([Month]="March", 03,IF([Month]="April",04))

this will generate the number for the corresponding order, later on, you can use this column to sort.

If you want to make it work on a form for a field to display the options in sort, below should work. Create a HTML control in ur form as below

<select id="ControlID" name="Month">
  <option value="03">March</option>
  <option value="04">April</option>
  <option value="05">May</option>
  <option value="10">October</option>
</select>

You can keep the sequence of options as you want in above control,

And keep your actual control hidden from form. Use Jquery/Javascript. On change event of the new control, pick its value and set in your original control.

11
  • how can i use this code to create list column Oct 1, 2015 at 13:21
  • You create a Single line of text column. suppose 'X' using your code which should not be a problem. Just to handle the problem of displaying it in sequence/order on form, I have provided solution.
    – Gaurravs
    Oct 1, 2015 at 13:27
  • Looks like you have not understood question or i am not able to explain Oct 1, 2015 at 13:30
  • If you are using custom form and the requirement is just to display Month/Years in order, and required to store it in any pattern then, my solution should be enough to fulfill your need
    – Gaurravs
    Oct 1, 2015 at 13:30
  • Hope you are talking about sharepoint list? See user goes in list and he select month and year. I wish to sort those columns. Oct 1, 2015 at 13:32
0

All credits to Christophe

http://blog.pathtosharepoint.com/2013/10/31/trick-or-treat-group-items-by-month/

Basic concept is to use a Calculated Column and pad the string with spaces (which your browser ignores)

 =REPT( " " , 13-MONTH(Modified) )  &  TEXT( Modified , "mmmm" )

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.