Como filtrar una columna donde hay valores de dos y tres cifras

Como filtrar una columna donde hay valores de dos y tres cifras

Driver1964Driver1964 Posts: 10Questions: 2Answers: 0

Buenas tardes.
Tengo una tabla con valores en milímetros en la columna B (20 mm. 50 mm. 100.)
Cuando intento filtrar por valor de menor a mayor o viceversa no me lo hace adecuadamente.
Lo único que puedo hacer para que los filtre bien es poner un cero a la izquierda de las cifras, pero no me gusta como queda estéticamente (020 mm. 050 mm. 100 mm.)
Como se podría solucionar

This question has an accepted answers - jump to answer

Answers

  • Driver1964Driver1964 Posts: 10Questions: 2Answers: 0

    Este es un ejemplo de mi tabla.https://live.datatables.net/meqadive/1/edit

  • rf1234rf1234 Posts: 2,944Questions: 87Answers: 416
    edited October 2

    Here is some code that should help you:
    $.fn.dataTable.ext.type.detect.unshift is used to detect columns with formatted numbers - like your mm column. You would need to specify what the additional content is. In your case it is "mm", but you could also use this with currencies, e.g. "EUR". Just add the extra content to the "addContent" array.

    $.extend( $.fn.dataTable.ext.type.order makes sure the additional content of formatted numbers gets stripped off. This will also work with formatted Spanish numbers such as 1.000.000,00 EUR for 1M Euros - if you add "EUR" to the array above.

    The code must be executed BEFORE data table initialization.

    var addContent = [ 'mm.' ];
    
    //auto detection of fields with formatted numbers
    $.fn.dataTable.ext.type.detect.unshift( function ( data ) {
        if (typeof data !== 'undefined') {
            if ( data != null )  {
                var i=0;
                while ( addContent[i] ) {
                    if ( isNaN(data) ) {  //search may only be performed on non-numeric fields
                        if ( data.search( addContent [i] ) > -1 )   {
                            return 'formatted-num';
                        }
                    } else { //for pure numbers we want the same sorting as well!!
                        return 'formatted-num';
                    }
                    i++;
                }
                if ( data === '') {
                    return 'formatted-num';
                }     
            }
        }
        return null;
    } );
    
    //sorting of formatted numbers in Spanish format
    $.extend( $.fn.dataTable.ext.type.order, { 
        "formatted-num-pre": function ( a ) {
            a = a.toString().replace( /[\.]/g, "" );
            a = a.toString().replace( /[\,]/g, "." );
            a = a.toString().replace( /[^\d.-]/g, "" );
            a = parseFloat(a);
            if ( ! isNaN(a) ) {
                return a;
            } else {
    //14 digit negative number to make sure empty cells always stay at the bottom / top
                return -99999999999999;
            }
        },
        "formatted-num-asc": function ( a, b ) {
                return a - b;
        },
        "formatted-num-desc": function ( a, b ) {
                return b - a;
        }
    } );
    

    This is a working example:
    https://live.datatables.net/tocedido/1/edit

  • allanallan Posts: 63,205Questions: 1Answers: 10,415 Site admin

    Nice one - thanks @rf1234, as always!

    One thing I'd like to point out is that as of v2 the preferred way to interact with types is the DataTable.types() method - that includes sorting and type detection. The manual has more details. The old ways still work, and the new API is basically a layer on top of them, but the new way is better documented and typed.

    Allan

  • Driver1964Driver1964 Posts: 10Questions: 2Answers: 0

    Thank you so much @rf1234

  • Driver1964Driver1964 Posts: 10Questions: 2Answers: 0

    @rf1234
    He colocado tu código en distintos lugares de mi código html y no logro que funcione.
    Te agradecería que me indicaras donde he de colocar el código exactamente en el ejemplo que he subido.

    Muchísimas gracias

  • rf1234rf1234 Posts: 2,944Questions: 87Answers: 416
    edited October 2 Answer ✓

    Te agradecería que me indicaras donde he de colocar el código exactamente en el ejemplo que he subido.

    Well it is in there already. I posted the working example above.
    But here it is again:
    https://live.datatables.net/tocedido/1/edit

    It is on the JavaScript page, separate from the HTML. The code needs be executed before
    $('#table_id').DataTable();

  • Driver1964Driver1964 Posts: 10Questions: 2Answers: 0

    @rf1234

    Ahora sí que ha funcionado.
    Muchísimas gracias de nuevo

  • rf1234rf1234 Posts: 2,944Questions: 87Answers: 416
    edited October 2

    De nada!

    Now I filled out your "precio" column with a price in Spanish number formatting for prices. That works too if the key word "EUR" is included in the column text.

    You could also use this without the key word "EUR" or any other currency, but then you run the risk that other columns that are not to be detected as formatted numbers (e.g. because they are dates in a format like DD.MM.YYYY), are indeed detected as the former.

    https://live.datatables.net/borexagi/1/edit

  • Driver1964Driver1964 Posts: 10Questions: 2Answers: 0

    Ok. I understand.

    Thanksssss

Sign In or Register to comment.