Dynamically Sort Columns By Month - Current Month First

[kpcollier]

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!

 

 

 

[futurist]

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!

 

 

[futurist]

Hi @kpcollier,

are you planning to use a tabular datapage for this one, or perhaps a pivot report?

[kpcollier]

@futurist This is insanely cool, many thanks for sharing your solution. 

I was planning on using a tabular report, but I might be able to switch it over to a pivot table. I'm going to give this a shot. Thanks again.

[kpcollier]

The script works perfectly on a tabular report as well. 

[kpcollier]

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

[kpcollier]

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);
  }
});