Inline editing 4.2 appearing as 4.1999998

ShaneBrennan

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

colin

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

ShaneBrennan

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

allan

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

ShaneBrennan

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":[]}

allan

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

MadMax76

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

colin

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

Colin

MadMax76

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

colin

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

Colin

MadMax76

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

colin

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

Colin

MadMax76

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

allan

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

MadMax76

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

allan

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

MadMax76

here you go:

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

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

Max

allan

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

MadMax76

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

Thanks
max

allan

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

MadMax76

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

allan

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

MadMax76

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

allan

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

MadMax76

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

Thanks!
Max

allan

Phew - we got there in the end .

Allan