Inline editing 4.2 appearing as 4.1999998

Inline editing 4.2 appearing as 4.1999998

ShaneBrennanShaneBrennan Posts: 49Questions: 18Answers: 1

Using datatable and Editor (in line) to allow a user to enter sample results.

Numbers such as 4.2 are being stores correctly in MY MSSQL DB but being displayed with a floating point error (I assume). Anywas I've got found the initial display by using ColumnDefs on the Datatable:

        columnDefs: [
          {
              render: $.fn.dataTable.render.number(',', '.', 1),
              targets: [3,4,5,6,7,8]
           }
        ],

which is great, however when I click in the 4.2 Aspergillus in the 2nd row I get:

Is there something similar to the columnDef for the datatable I can use? or is there another solution to get it to be 4.2 instead?

Thank you inadvance for any help given

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Can you post the JSON that's returned from the server for that record, please. I'd be interested to see if that has 4.2 or the incorrect number.

    Colin

  • ShaneBrennanShaneBrennan Posts: 49Questions: 18Answers: 1

    Hi Colin... your right...The JSON is presenting the wrong value:

    MSSQL is showing the correct values:

    just need the editor to round it up to 1 dp

  • allanallan Posts: 61,433Questions: 1Answers: 10,048 Site admin

    On the client-side Editor doesn't have a formatter for modifying values as they go into a field for editing. It always operates on the raw value.

    So the solution here would be to understand why you are getting 4.19999... in the JSON data in the first place. I would have assumed it was being stored as an IEEE754 number with its limited precision (which is what Javascript uses), but it is shown as a string above, so I'm not certain that is the issue.

    Perhaps you can clarify where the data is coming from and if it is 4.19999... in the data store, whatever that is.

    Allan

  • ShaneBrennanShaneBrennan Posts: 49Questions: 18Answers: 1
    edited February 2020

    basically I use PHP, this is what I use to retrieve the data from MSSQL and convert to JSON.

    use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

    $reqID = 0;
    if (isset($_GET['projectID'])) {
    $reqID = $_GET['projectID'];
    // ToCleaned($reqID);
    }

    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'tblAQIlocationSamples' )
    ->field(
    Field::inst( 'tblAQIlocationSamples.id' ),
    Field::inst( 'tblAQIlocsAndMeasures.locationNo' ),
    Field::inst( 'tblAQIlocsAndMeasures.locationName' ),
    Field::inst( 'tblAQIlocationSamples.sampleNo' ),
    Field::inst( 'tblAQIlocationSamples.bacteriaCFU' ),
    Field::inst( 'tblAQIlocationSamples.aspergillusCFU' ),
    Field::inst( 'tblAQIlocationSamples.totalMouldCFU' ),
    Field::inst( 'tblAQIlocationSamples.yeastCFU' ),
    Field::inst( 'tblAQIlocationSamples.penicilliumCFU' )
    )
    ->where('tblAQIlocationSamples.projectID',$reqID)
    ->where('tblAQIlocationSamples.sampleType','Airstrip')
    ->leftjoin( 'tblAQIlocsAndMeasures', 'tblAQIlocsAndMeasures.id', '=', 'tblAQIlocationSamples.AQIlocationID' )
    ->process( $_POST )
    ->json();

    and this is the JSON response:

    {"data":[{"DT_RowId":"row_14","tblAQIlocationSamples":{"id":"14","sampleNo":"1","bacteriaCFU":"5.0","aspergillusCFU":"4.0","totalMouldCFU":"26.0","yeastCFU":"5.0","penicilliumCFU":"3.0"},"tblAQIlocsAndMeasures":{"locationNo":"01","locationName":"Outside"}},{"DT_RowId":"row_17","tblAQIlocationSamples":{"id":"17","sampleNo":"4","bacteriaCFU":"1.0","aspergillusCFU":"6.0","totalMouldCFU":"2.0","yeastCFU":"5.0","penicilliumCFU":"3.0"},"tblAQIlocsAndMeasures":{"locationNo":"02","locationName":"Dining Room no 2"}}],"options":[],"files":[]}

  • allanallan Posts: 61,433Questions: 1Answers: 10,048 Site admin

    Thanks. In the JSON response there is "aspergillusCFU":"4.0" and "aspergillusCFU":"6.0" which I presume are okay?

    But from your comment before when it is 4.2 there is an issue - and the JSON return is actually 4.19999...? Which is odd since those numbers are actually strings - there shouldn't be any rounding.

    Thanks,
    Allan

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    Hi ShaneBrennan,
    I have the same problem, did you find a solution? If I look at the data via odbc, I do get the normal data.

    Thanks
    Max

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    @MadMax76 it would be worth following the same diagnostic steps as Allan suggested, and letting us know the results.

    Colin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    Hi Colin,

    server is ms-sql 2012, there the numbers are shown:

    in datatables:

    I have to work on the sorting too....

    field definition:

            Field::inst( 'V_Rechnungsheader.Netto' )
                ->validator( 'Validate::numeric', array(
                    'decimal'=> ',',
                    'message'=> 'Bitte eine Zahl eingeben!'
                ))
                ->setFormatter( 'Format::fromDecimalChar', ',' ),
            Field::inst( 'V_Rechnungsheader.Brutto' )
                ->validator( 'Validate::numeric', array(
                    'decimal'=> ',',
                    'message'=> 'Bitte eine Zahl eingeben!'))
                ->setFormatter( 'Format::fromDecimalChar', ',' ),
    

    in the editor-definition I tried to work with a render, which did not change anything:

    { label: "Netto", name: "V_Rechnungsheader.Netto", render: $.fn.dataTable.render.number( '.', ',', 2, '' ) },
    { label: "Brutto", name: "V_Rechnungsheader.Brutto", render: $.fn.dataTable.render.number( '.', ',', 2, '' ) },
    

    json-response:

    {
        "data": [
            {
                "DT_RowId": "row_13305a2ded930_20210302_09.14.44.009_305a2ded0",
                "V_Rechnungsheader": {
                    "SUB_ID": "0",
                    "INP_ID": "13",
                    "Stapelname": "930_20210302_09.14.44.009_",
                    "bezahltKonto": null,
                    "Re_Empfaenger": "930",
                    "LiefenantenNo": null,
                    "Netto": "35.109999999999999",
                    "Brutto": "42.130000000000003",
                    "Text_Rechnungskopf": "Einfache Überprüfung lt . FAG Paragraph 21\r\neinschließlich sowie",
                    "Rechnungsdatum": "2021-02-23",
                    "Rechnungsnummer": "21000326",
                    "Nettotag": "2021-03-02",
                    "Skontotag": null,
                    "Skontoproz": null
                },
                "V_Zahlkonten": {
                    "KontoBez": null
                }
            }
        ],
    

    I also tried to access the data via ms-access and odbc, there the numbers come correct.

    I hope that info helps.
    Max

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Thanks for that, that is helpful. Do the numbers appear long in the Ajax as well? Or are they 2dp there too?

    Colin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    sorry but what do you mean with "in the ajax"?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    The data as it's sent from the server - you'll see it in the browser's network tab in the developer tools.

    Colin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    If that is the same as the Json-Response: yes, there the numbers are "wrong" already

  • allanallan Posts: 61,433Questions: 1Answers: 10,048 Site admin

    Two more bits of information that might help track this down please:

    • What about the data that is being sent to the server? You'll see that in your browser's network inspector as well - in the "Request" tab in Firefox, and "Headers" in Chrome.
    • Can you add ->debug(true) immediately before the ->process(...) can please? Then show me the JSON response from the server when you create a new row.

    It's almost certainly an IEEE 754 number issue, we just need to track down where the number modification is happening. It isn't fromDecimalChar as that is just doing a string replace, so if there is any other code you have which is doing number calculation on the fields, could you let us see that too please?

    Thanks,
    Allan

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    I updated numbers to 35,11 and 42,13, here is the json

    {
        "data": [
            {
                "DT_RowId": "row_13305a2ded930_20210302_09.14.44.009_305a2ded0",
                "V_Rechnungsheader": {
                    "SUB_ID": "0",
                    "INP_ID": "13",
                    "Stapelname": "930_20210302_09.14.44.009_",
                    "bezahltKonto": "0",
                    "Re_Empfaenger": "930",
                    "LiefenantenNo": "310002",
                    "Netto": "35.109999999999999",
                    "Brutto": "42.130000000000003",
                    "Text_Rechnungskopf": "Einfache Überprüfung lt . FAG Paragraph 21\r\neinschließlich sowie",
                    "Rechnungsdatum": "2021-02-23",
                    "Rechnungsnummer": "21000326",
                    "Nettotag": "2021-03-02",
                    "Skontotag": "1900-01-01 00:00:00.000",
                    "Skontoproz": "0.0"
                },
                "V_Zahlkonten": {
                    "KontoBez": null
                }
            }
        ],
        "debug": [
            {
                "query": "SELECT  [V_Rechnungsheader].[INP_ID] as 'V_Rechnungsheader.INP_ID' FROM  [V_Rechnungsheader] WHERE [V_Rechnungsheader].[INP_ID] = :where_0 AND  [V_Rechnungsheader].[Stapelname] = :where_1 AND  [V_Rechnungsheader].[SUB_ID] = :where_2 ",
                "bindings": [
                    {
                        "name": ":where_0",
                        "value": "13",
                        "type": null
                    },
                    {
                        "name": ":where_1",
                        "value": "930_20210302_09.14.44.009_",
                        "type": null
                    },
                    {
                        "name": ":where_2",
                        "value": "0",
                        "type": null
                    }
                ]
            },
            {
                "query": "UPDATE  [V_Rechnungsheader] SET  [bezahltKonto] = :bezahltKonto, [LiefenantenNo] = :LiefenantenNo, [Netto] = :Netto, [Brutto] = :Brutto, [Rechnungsdatum] = :Rechnungsdatum, [Rechnungsnummer] = :Rechnungsnummer, [Nettotag] = :Nettotag, [Skontotag] = :Skontotag, [Skontoproz] = :Skontoproz WHERE [V_Rechnungsheader].[INP_ID] = :where_0 AND  [V_Rechnungsheader].[Stapelname] = :where_1 AND  [V_Rechnungsheader].[SUB_ID] = :where_2 ",
                "bindings": [
                    {
                        "name": ":bezahltKonto",
                        "value": "",
                        "type": null
                    },
                    {
                        "name": ":LiefenantenNo",
                        "value": "310002",
                        "type": null
                    },
                    {
                        "name": ":Netto",
                        "value": "35.109999999999999",
                        "type": null
                    },
                    {
                        "name": ":Brutto",
                        "value": "42.130000000000003",
                        "type": null
                    },
                    {
                        "name": ":Rechnungsdatum",
                        "value": "2021-02-23",
                        "type": null
                    },
                    {
                        "name": ":Rechnungsnummer",
                        "value": "21000326",
                        "type": null
                    },
                    {
                        "name": ":Nettotag",
                        "value": "2021-03-02",
                        "type": null
                    },
                    {
                        "name": ":Skontotag",
                        "value": "",
                        "type": null
                    },
                    {
                        "name": ":Skontoproz",
                        "value": "",
                        "type": null
                    },
                    {
                        "name": ":where_0",
                        "value": "13",
                        "type": null
                    },
                    {
                        "name": ":where_1",
                        "value": "930_20210302_09.14.44.009_",
                        "type": null
                    },
                    {
                        "name": ":where_2",
                        "value": "0",
                        "type": null
                    }
                ]
            },
            {
                "query": "SELECT  [V_Rechnungsheader].[INP_ID] as 'V_Rechnungsheader.INP_ID', [V_Rechnungsheader].[Stapelname] as 'V_Rechnungsheader.Stapelname', [V_Rechnungsheader].[SUB_ID] as 'V_Rechnungsheader.SUB_ID', [V_Rechnungsheader].[bezahltKonto] as 'V_Rechnungsheader.bezahltKonto', [V_Zahlkonten].[KontoBez] as 'V_Zahlkonten.KontoBez', [V_Rechnungsheader].[Re_Empfaenger] as 'V_Rechnungsheader.Re_Empfaenger', [V_Rechnungsheader].[LiefenantenNo] as 'V_Rechnungsheader.LiefenantenNo', [V_Rechnungsheader].[Netto] as 'V_Rechnungsheader.Netto', [V_Rechnungsheader].[Brutto] as 'V_Rechnungsheader.Brutto', [V_Rechnungsheader].[Text_Rechnungskopf] as 'V_Rechnungsheader.Text_Rechnungskopf', [V_Rechnungsheader].[Rechnungsdatum] as 'V_Rechnungsheader.Rechnungsdatum', [V_Rechnungsheader].[Rechnungsnummer] as 'V_Rechnungsheader.Rechnungsnummer', [V_Rechnungsheader].[Nettotag] as 'V_Rechnungsheader.Nettotag', [V_Rechnungsheader].[Skontotag] as 'V_Rechnungsheader.Skontotag', [V_Rechnungsheader].[Skontoproz] as 'V_Rechnungsheader.Skontoproz' FROM  [V_Rechnungsheader] LEFT JOIN [V_Zahlkonten] ON [KontoNr] = [V_Rechnungsheader].[bezahltKonto] WHERE [SUB_ID] = :where_0 AND  [INP_ID] = :where_1 AND  [Stapelname] = :where_2 AND  [V_Rechnungsheader].[INP_ID] = :where_3 AND  [V_Rechnungsheader].[Stapelname] = :where_4 AND  [V_Rechnungsheader].[SUB_ID] = :where_5 ",
                "bindings": [
                    {
                        "name": ":where_0",
                        "value": 0,
                        "type": null
                    },
                    {
                        "name": ":where_1",
                        "value": "13",
                        "type": null
                    },
                    {
                        "name": ":where_2",
                        "value": "930_20210302_09.14.44.009_",
                        "type": null
                    },
                    {
                        "name": ":where_3",
                        "value": "13",
                        "type": null
                    },
                    {
                        "name": ":where_4",
                        "value": "930_20210302_09.14.44.009_",
                        "type": null
                    },
                    {
                        "name": ":where_5",
                        "value": "0",
                        "type": null
                    }
                ]
            }
        ]
    }
    

    if this does not help i set up a test page for you

    Max

  • allanallan Posts: 61,433Questions: 1Answers: 10,048 Site admin

    Hi Max,

    That confirms that the issue is not coming from the SQL database - the rounding error is happening somewhere before that. I'm not yet clear if it is on the Javascript or PHP side through. Can you show me the parameters being submitted to the server please? Or, yes, if you can setup a test page, that would be ideal.

    Allan

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    here you go:

    http://freigabe.kontura.at/test/allan.html

    NEW does not work - no idea why - but update does produce the error.

    Max

  • allanallan Posts: 61,433Questions: 1Answers: 10,048 Site admin

    Hi Max,

    I'm getting a lot of 404 errors when loading that page and it doesn't do very much I'm afraid - for example, DataTables isn't loading:

    http://freigabe.kontura.at/DataTables/DataTables/js/jquery.dataTables.min.js”. allan.html:71:1 .

    Allan

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    sorry, due to another building lot I had an update of my datatable-download.
    Not it works, although it isnt really beuatiful...

    Thanks
    max

  • allanallan Posts: 61,433Questions: 1Answers: 10,048 Site admin

    Sorry! I'm still getting an error: $.fn.dataTable is undefined on the page.

    Your:

    <script src="https://cdn.datatables.net/plug-ins/1.10.21/dataRender/datetime.js" charset="utf8"></script>
    

    needs to be loaded after DataTables core (since it is trying to attach a function to the DataTables core objects).

    Allan

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    I got all eroors away... Problem persists...

  • allanallan Posts: 61,433Questions: 1Answers: 10,048 Site admin

    Thanks! That confirms that it isn't a client-side issue:

    Can you show me an unabbreviated copy of V_Rechnungsheader_test.php please?

    Allan

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    here you go:

    <?php
    // DataTables PHP library
    include( "../lib/DataTables.php" );
    
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    
    $selectedFirma = "105";
    
    Editor::inst( $db, 'V_Rechnungsheader', ['INP_ID', 'Stapelname', 'SUB_ID'] )
        ->fields(
            Field::inst( 'V_Rechnungsheader.SUB_ID' ), //// primary key
            Field::inst( 'V_Rechnungsheader.INP_ID' ), //// join parameter
            Field::inst( 'V_Rechnungsheader.Stapelname' ), //// join parameter
            Field::inst( 'V_Rechnungsheader.Re_Empfaenger' ),
            Field::inst( 'V_Rechnungsheader.LiefenantenNo' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Kreditor muss festgelegt sein!' )) ),
            Field::inst( 'V_Rechnungsheader.Netto' )
                ->validator( 'Validate::numeric', array(
                    'decimal'=> ',',
                    'message'=> 'Bitte eine Zahl eingeben!'
                ))
                ->setFormatter( 'Format::fromDecimalChar', ',' ),
            Field::inst( 'V_Rechnungsheader.Brutto' )
                ->validator( 'Validate::numeric', array(
                    'decimal'=> ',',
                    'message'=> 'Bitte eine Zahl eingeben!'))
                ->setFormatter( 'Format::fromDecimalChar', ',' ),
            Field::inst( 'V_Rechnungsheader.Text_Rechnungskopf' ),
            Field::inst( 'V_Rechnungsheader.Rechnungsdatum' )
                ->getFormatter( Format::dateSqlToFormat('Y-m-d'))
                ->setFormatter( Format::dateFormatToSql('Y-m-d'))
                ->validator( Validate::dateFormat( 'Y-m-d' ),
                    ValidateOptions::inst()
                        ->message( 'Fälligkeitsdatum muss festgelegt und ein Datum sein' ) ),
            Field::inst( 'V_Rechnungsheader.Rechnungsnummer' ),
            Field::inst( 'V_Rechnungsheader.Nettotag' )
                ->getFormatter( Format::dateSqlToFormat('Y-m-d'))
                ->setFormatter( Format::dateFormatToSql('Y-m-d'))
                 ->validator( Validate::dateFormat( 'Y-m-d' ),
                      ValidateOptions::inst()
                    ->message( 'Fälligkeitsdatum muss festgelegt und ein Datum sein' ) ),
            Field::inst( 'V_Rechnungsheader.Skontotag' )
                ->getFormatter( Format::dateSqlToFormat('Y-m-d'))
                ->setFormatter( Format::dateFormatToSql('Y-m-d'))
                 ->validator( Validate::dateFormat( 'Y-m-d' ),
                          ValidateOptions::inst() ),
              Field::inst( 'V_Rechnungsheader.Skontoproz' )
                  ->validator( 'Validate::numeric', array(
                      'decimal'=> ',',
                      'message'=> 'Bitte Zahl ohne %-Zeichen eingeben!'))
                  ->setFormatter( 'Format::fromDecimalChar', ',' )
        )
    
        ->where('SUB_ID', 0)
        ->where('V_Rechnungsheader.Re_Empfaenger', $selectedFirma)
        ->debug(true)
        ->process( $_POST )
        ->json();
    
    
    
  • allanallan Posts: 61,433Questions: 1Answers: 10,048 Site admin

    Excellent - thank you! Reading over that, trying to reproduce it here and also working with your example page it doesn't look like it is in the PHP either - this is what is being written to the database:

    So my theory flips to the data type being used in the database. Are you using a float or double? What is the schema for the database? If it isn't a decimal you are using, then that is what I would recommend changing the data type to. It is slower for calculations, but it also means that your numbers won't stored using an approximate value.

    The MySQL page on floats contains useful information on this.

    Sorry this one is taking me a little while to fully diagnose. Hopefully we are nearly there!

    Allan

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    The software used doesn't offer decimal as option, but I changed it on the server directly and this did the trick!

    Thanks!
    Max

  • allanallan Posts: 61,433Questions: 1Answers: 10,048 Site admin

    Phew - we got there in the end :).

    Allan

This discussion has been closed.