Calculated field data not showing in Column filter dropdown

byoungman

I'm still very new to DataTables and learning as I go so that being said the latest thing that I am doing is having a calculated field show in my DataTable - it's a revenue percentage calculated based on a total revenue / annual revenue.

That's working fine and the percentages are all showing for the records but what isn't happening is that those values are not showing in the column filter dropdown. How do I get those values to show in the dropdown list - all of the other filter dropdowns are working just fine but then again those columns are straight data values from the backend database.

Thanks,
Bill

kthorngren

You will need to use cells().render() to get the rendered data. Here is an example:
https://live.datatables.net/wemobube/1/edit

Kevin

byoungman

So what the original developer implemented was this (his version didn't have calculated fields) --

html --

<table id="rftbl" datatable [dtOptions]="dtOptions">
<thead>

<tr>
<th class="column-header" style="text-align: center" *ngFor="let col of assignedRowColumns" >{{col.display}}</th>
</tr>
</thead>
<tbody>
    <tr *ngFor="let row of dataSource?.data?.taskrows" [id]="row.id">
...
</tbody>

assignedRowColumns is an array of columns for the table to use such that

assignedRowColumns = [
...
{ name: 'inputdata.revenue', display: 'Insured Revenue' },
{ name: 'assignedRow.data.annualrevenue', display: 'Annual Revenue'},
{ name: 'calcRevenueString', display: 'Revenue Percent'},
...
]

The 'calcRevenueString' is the field that I am doing my calculations on.

this.datasource is a REST endpoint call to get data that is then used to populate the table. Then taskrow data is populated with this call

this.dataSource.data.taskrows = this.flattenObj(this.dataSource.data.taskrows);

I hope this is clear.

With the above approach can I still use your suggestion?

-Bill

byoungman

Ok thank you and I'll post a question with them.

-Bill

kthorngren

I'm not sure what that code does and is Angular Datatables specific.

I made some assumptions that you were using something like this example and using columns.render to render the calculated column. This Angular Datatables example is similar to this text input search example. Do you have code like this in your dtOptions?

Post your dtOptions so we can see if you are doing something similar to the above examples.

Kevin

kthorngren

Sorry, I rephrased my response but you saw my deleted post

Post your dtOptions so we can see the Datatables config you have.

Kevin

byoungman

No problem---

    public defaultDTOptions:DataTables.Settings = { 
      pageLength:10,

      lengthMenu: [
        [5,10,100, 250, 500, 1000,-1],
        [5,10,100, 250, 500, '1000','All'],
      ],

      initComplete: function (this:any) {
        this.api().columns().every(function (this:any) {
          let column = this;

          if( $(column.header()).text().toLowerCase()=='actions')
            return;

          // Create select element
          let select = document.createElement('select');
          select.className='tablefilterdropdown';
          select.add(new Option(''));
          
          $(select).on('click',(event)=>{event.stopPropagation();});
          
          // Apply listener for user change in value
          select.addEventListener('change', function (this:any) {
            var val = select.value;

            // https://datatables.net/reference/api/column().search()
            column
              .search(val ?  val  : '', false, true,true)
              .draw();
          });

          // Add list of options
          let cd=column.data(); 

          let d:any=[]; 

          for(let i=0; i<cd.length; i++) {
            let r = ($(cd[i]).find('input').length?$(cd[i]).find('input').val() + '':$(cd[i]).text()).toUpperCase().replace(/\"/gi,''); d.push(r);

          }; 

          d=d.getUnique(); 
          d=d.sort((a:any, b:any) => a.localeCompare(b, undefined, {sensitivity: 'base'})); 

          for(let r of d) {
            select.add(new Option(r));
          }

          if(d.length){
                $(column.header()).find('.tablefilterdropdown').remove();
            $(column.header()).append(select);
            }

        });
      }
    };

kthorngren

Based on the above you aren't using columns.render for the calculated column. So the cells().render() won't help. It doesn't look like Datatables is involved in building the calculated column.

Is the calculated column generated when the HTML table is generated, ie this code?

<tbody>
    <tr *ngFor="let row of dataSource?.data?.taskrows" [id]="row.id">
...
</tbody>

If the calculated column happens before Datatables is initialized then the above initComplete code should find the data to build the select options. I would do some debugging of the loop in line 38 to see what happens when building the select options for the calculated column.

If the calculated column happens after Datatables is initialized then Datatables won't know about the updated data since it is not involved in this process. See this FAQ regarding options to update table data after initialization.

Can you post a link to your page or a test case replicating the issue so we can help debug?
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

byoungman

Thanks for the reply Kevin.

I looked at the procedure to provide a test case and my client doesn't allow any of that so I'm going to have to post my code here with context.

Table code

      <table id="rftbl" datatable [dtOptions]="dtOptions" class="display nowrap ">
        <thead>
            <tr>
                <th class="column-header" style="text-align: center" *ngFor="let col of assignedRowColumns">{{col.display}}</th>
            </tr>
        </thead>
        <tbody>
            <tr *ngFor="let row of dataSource?.data?.taskrows" [id]="row.id">
                <td class="column-data" style="width: auto;" *ngFor="let rc of assignedRowColumns">
                    <span *ngIf="rc.name=='select'">
                        <input type="checkbox" value="1" [(ngModel)]="row['select']"/>
                    </span>
                    <span *ngIf="rc.name=='actions'">
                      <button class="actionbutton" (click)="performAction(row,'details')">Details</button>
                      <button *ngIf="row.assigned == 'y' && ( row.assignment_info == 0 || row.assignment_info == null )" class="actionbutton" (click)="performAction(row,'promote')">Promote</button>
                      <button *ngIf="row.assigned == 'y' && ( row.assignment_info == 1 || row.assignment_info == 2 )" class="actionbutton" (click)="performAction(row,'demote')">Demote</button>
                    </span>
                    <span *ngIf="rc.name=='assigned'">
                        <span *ngIf="row['assignedName']">
                          <input type="hidden" [value]="row['assignedName']"/>
                          <select [(ngModel)]="row['assigned']" (change)="row.updateAssignment()" style="max-width: 150px;width: 150px;">
                            <option value="n">**Not Assigned</option>
                            <option value="y">{{row['assignedName']}}</option>
                          </select>
                        </span>
                        <span *ngIf="!row['assignedName']">
                          <input type="hidden" value="**Not Assigned"/>
                          {{row['assigned']=='n'?'**Not Assigned':row['assignedName']}}  
                        </span>
                        
                    </span>   
                    <span *ngIf="rc.name!='select' && rc.name!='actions'  && rc.name!='assigned'">
                      <input type="hidden" [value]="row[rc.name]"/>
                      {{
                          rc.name.indexOf('assignedRow.data.annualrevenue')>= 0 ? isCurrency(row[rc.name]):
                          rc.name.indexOf('inputdata.revenue') >= 0 ? isCurrency(row[rc.name]):
                          rc.name.indexOf('inputdata.limit') >= 0 ? isCurrency(row[rc.name]):
                          rc.name.indexOf('assignedRow.certainty') >= 0 ? formatPercent(row[rc.name]):
                          rc.name.indexOf('assignedRow.raw_certainty') >= 0 ? formatPercent(row[rc.name]):
                          rc.name.indexOf('calcRevenueString') >= 0 ? calculateRevenuePercent(row):
                          isDate(row[rc.name]) ? moment(row[rc.name]).format('yyyy-MM-DD HH:mm'):row[rc.name]
                      }}
                    </span> 
                </td>
            </tr>
        </tbody>
      </table>

The calculated field is triggered with this call

rc.name.indexOf('calcRevenueString') >= 0 ? calculateRevenuePercent(row):

calcRevenueString method

    calculateRevenuePercent(val: any) {

            let revenue = val['inputdata.revenue'];
            let annualRevenue = val['assignedRow.data.annualrevenue'];

            if (revenue != 0 && annualRevenue != 0) {
                this.calcRevenue = revenue / annualRevenue;

                this.percentRevenueString = this.formatPercent(this.calcRevenue);
            }
            else {
                this.percentRevenueString = '';
            }

            this.calcRevenueString = this.percentRevenueString;

            return this.calcRevenueString;
    }

I will focus my debugging in the area that you suggested.

Thanks,
Bill

byoungman

Looking at the code at line 38 I had done that yesterday and it is loading data for all of the columns except for the calculated field. So my assumption was in line with what you said about the data not being available at data table initialization so I started digging into (with much head banging) how to make that data available without much luck

kthorngren

Is there an event or soemthing you can use to delay the initialization of Datatables until the HTMl table is complete, including the calculated field?

Kevin

byoungman

I'll give that a try Kevin - thanks

byoungman

So I tried without success

setTimeout(() => {
    $("#rftbl").DataTable(that.dtOptions).draw();
}, 50000);

I just talked to the Principal Architect on this project about this issue and he decided that since the percentage data is being displayed in the column which is the business requirement and the business isn't going to be filtering on this data that they are more interested in filtering on the actual revenue numbers we're going to go with what we have as there are other items that need to be worked on that are of a higher priority.

Now that being said if the business comes back and says no they want to be able to filter on these percentages then we will revisit this.

Thank you for your help - I hate leaving this hanging like this but the architect is in control of what I work on