Jump to content
  • 0

Dynamically Sort Columns By Month - Current Month First


kpcollier
 Share

Question

I am trying to convert a report that we use on Excel over to Caspio. We already store all of the necessary information, however I am quite stuck on figuring out how to create the layout for the report. 

As you can see in the image below, the first part of the report is selecting a Start date. This usually only gets changed once a month, when a new month rolls over. I believe we could exclude this and just use a timestamp, but it's useful to understand the workflow. Notice how the 'Start Date' is March, and the yellow columns (Mar-Feb) start with March and proceed to list the rest of the months after. I need this functionality, where the first column of the months is always the current month. If the Start Date was changed to April 1st, then April would be the first column and March would be the last column.

Another problem is that these 12 month fields are actually Number fields, not Date/Time fields. The idea is that our project managers will put a number into each of these Month columns.

I also will need to store the data that is being entered below those columns to create a gantt chart. 

Any ideas? TIA!

 

sortcolumns.thumb.PNG.7cdd9634b26ce7962c6f9533e9aebfcc.PNG

 

 

Link to comment
Share on other sites

6 answers to this question

Recommended Posts

  • 0

Hi @kpcollier,

I tried doing this in a Pivot table and I added some script to dynamically sort the columns by month. Note that I separated the search form and the results datapage, mainly because I need to be able to get the month and year separately, because I need them in two separate purposes. The month is for us to determine which month should come first and have the older months to come at the end of december. The year is for us to filter records by that year.

Here is the script I used on the results page:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>


<script>

jQuery.moveColumn = function (table, from, to) {
    var rows = jQuery('tr', table);
    var cols;
    rows.each(function() {
        cols = jQuery(this).children('th, td');
        cols.eq(from).detach().insertBefore(cols.eq(to));
    });
}

var tbl = jQuery('table');

if( [@Month]  > 1 ){

let i =  [@Month] - 1 ;
while (i > 0) {
  jQuery.moveColumn(tbl, 1, 13);
  i--;
}

}


</script>

 

[@Month] is the external parameter from the search form (I also filtered records by the Date criteria in a yearly basis using the Year parameter from the search form)

Here's the search form for the sample I made: https://c1hch576.caspio.com/dp/db26a0007b5146b526f84033b4bf (use 2022 for the Year field because my test data are in that year)

Note that since this is a Pivot table, columns for each month will appear only if there is a record for each month. If not, the script would not work as expected. I haven't tried this for a Tabular Report DataPage, so if thats what you need, let me know so I can see what I can do.

 

Thanks!

 

 

Link to comment
Share on other sites

  • 0

@futurist I apologize for asking for more! But, any idea how I could make this work for Grid Edit as well? I know it changes the format of the table... I don't think there are any <th> elements in Grid Edit, it looks like its a bunch of <td>'s with divs for the labels. I've tried messing with your script a bit, but I'm really pretty novice with jquery. 

Link to comment
Share on other sites

  • 0

I think I was able to get this to work, somewhat. I threw it in a click event for the Grid Edit button and changed 'cols' to 'td, div'. Also, I needed to add a state variable to make sure it was only running when opening grid edit. 

var state = false;
$(".cbResultSetGridEditActionLink").on('click', function(event){

  console.log("initlog " + state);
  if (state == false){
    state = true;
    console.log("firstlog " + state);
    setTimeout(function() {
    jQuery.moveColumn2 = function (table, from, to) {
      var rows = jQuery('tr', table);
      var cols;
      rows.each(function() {
          cols = jQuery(this).children('td, div');
          cols.eq(from).detach().insertBefore(cols.eq(to));
      });
  }

  var tbl = jQuery('table');
  
  if( [@Month]  > 1 ){

  let i =  [@Month] - 1 ;
  while (i > 0) {
    jQuery.moveColumn2(tbl, 8, 20);
    i--;
   }
  }
  }, 2000)
  } else if (state == true){
    state = false;
    console.log("seclog" + state);
  }
});

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...