Date formatting is strange in editor

Date formatting is strange in editor

sanderksanderk Posts: 26Questions: 4Answers: 1

I have two datatable editors. Both need to return dates in Dutch format, edit etc.

This is the C# code for one table:

[HttpGet]
        [HttpPost]
        public ActionResult LeningenTable([FromForm]string GebouwID = "-1")
        {

            using (var db = new Database("azure", _configuration.GetConnectionString("BBGroepDataContext")))
            {
                var editor = new Editor(db, "Leningen", "Id")
                    .Model<LeningViewModel>()
                    .Field(new Field("Leningverstrekker")
                        .Validator(Validation.NotEmpty(new ValidationOpts { Message = "De naam van de leningverstrekker is verplicht." }))
                     )
                    .Field(new Field("StartDatum")
                        .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een geldige datum in." }))
                        .GetFormatter(Format.DateSqlToFormat("dd/MM/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("dd/MM/yyyy"))
                     )
                    .Field(new Field("EindDatum")
                        .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een geldige datum in." }))
                        .GetFormatter(Format.DateSqlToFormat("dd/MM/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("dd/MM/yyyy"))
                     )
                    .Field(new Field("Hoofdsom")
                       .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een getal in." }))
                        .Validator(Validation.Numeric("nl-NL", new ValidationOpts { Message = "Voer een getal in." }))
                     )
                    .Field(new Field("Aflossingspercentage")
                         .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een getal in tussen de 0 en 100." }))
                        .Validator(Validation.MinMaxNum(0, 100, new ValidationOpts { Message = "Voer een getal in tussen de 0 en 100." }))
                     )
                    .Field(new Field("Voorwaarden")
                        .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een getal in." }))
                     )
                    .Field(new Field("GebouwId")
                        .SetValue(GebouwID)
                     )
                    .Where("gebouwId", GebouwID);

                editor.PostCreate += (sender, e) => _LogChange(db, "Leningen", "create", e.Id, e.Values);
                editor.PostEdit += (sender, e) => _LogChange(db, "Leningen", "edit", e.Id, e.Values);
                editor.PostRemove += (sender, e) => _LogChange(db, "Leningen", "remove", e.Id, e.Values);

                var response = editor.Process(Request).Data();

                return Json(response);
            }
        }

When I look at the response I see this for the dates:

StartDatum: {6-6-2020 00:00:00}

However. The datatable in the client view shows: 2020-06-06T00:00:00

I also have this code in the same controller:

```
[HttpGet]
[HttpPost]
public ActionResult HuurdersTable([FromForm]string GebouwID = "-1")
{

        using (var db = new Database("azure", _configuration.GetConnectionString("BBGroepDataContext")))
        {
            var editor = new Editor(db, "Huurders", "id")
                .Model<HuurderViewModel>("Huurders")
                .Field(new Field("NaamHuurder")
                    .Validator(Validation.NotEmpty(new ValidationOpts { Message = "De naam van de huurder is verplicht." }))
                 )
                .Field(new Field("Huisnummer")
                    .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Het huisnummer is verplicht." }))
                 )
                .Field(new Field("HuurcontractHuurder")
                    .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Het Huurcontract is verplicht." }))
                 )
                .Field(new Field("StartDatumContract")
                    .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een geldige datum in." }))
                    .GetFormatter(Format.DateSqlToFormat("dd/MM/yyyy"))
                    .SetFormatter(Format.DateFormatToSql("dd/MM/yyyy"))
                 )
                .Field(new Field("EindDatumContract")
                    .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een geldige datum in." }))
                    .GetFormatter(Format.DateSqlToFormat("dd/MM/yyyy"))
                    .SetFormatter(Format.DateFormatToSql("dd/MM/yyyy"))
                 )
                .Field(new Field("Opzegtermijn")
                   .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een getal in." }))
                    .Validator(Validation.Numeric("nl-NL", new ValidationOpts { Message = "Voer een getail in." }))
                 )
                .Field(new Field("AantalM2VVO")
                     .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een getal in." }))
                    .Validator(Validation.Numeric("nl-NL", new ValidationOpts { Message = "Voer een getail in." }))
                 )
                .Field(new Field("AantalM2BVO")
                     .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een getal in." }))
                    .Validator(Validation.Numeric("nl-NL", new ValidationOpts { Message = "Voer een getail in." }))
                 )
                .Field(new Field("GebouwId")
                    .SetValue(GebouwID)
                 )
                .Where("GebouwId", GebouwID)
                .MJoin(new MJoin("HuurderExtraEigenschappen")
                    .Link("Huurders.id", "HuurderExtraEigenschappen.HuurderId")
                    .Field(new Field("id"))
                )
                ;

            editor.PostCreate += (sender, e) => _LogChange(db, "Huurders", "create", e.Id, e.Values);
            editor.PostEdit += (sender, e) => _LogChange(db, "Huurders", "edit", e.Id, e.Values);
            editor.PostRemove += (sender, e) => _LogChange(db, "Huurders", "remove", e.Id, e.Values);

            var response = editor.Process(Request).Data();

            return Json(response);
        }
    }

```
For this code the response shows: StartDatumContract: "02-06-2020"

And on the client side everything is fine.

Please help.

This question has an accepted answers - jump to answer

Answers

  • sanderksanderk Posts: 26Questions: 4Answers: 1

    UPDATE: It got stranger.

    I found out that it doesn't work because i didn't specify the table name

    .Model<LeningViewModel>() (Line 9)

    However. I also have code where results of a datatable are based on a selected row of another datatable. If I specify the tablename in the .Model, the date is correct, but the parent child function breaks.

    Also, when I have fields in this form (Look at Winstuitkeringen.Uitkeringsdatum):

    .Field(new Field("Winstuitkeringen.Uitkeringsdatum")
                            .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een geldige datum in." }))
                            .GetFormatter(Format.DateSqlToFormat("dd/MM/yyyy"))
                            .SetFormatter(Format.DateFormatToSql("dd/MM/yyyy"))
    

    The date is displayed incorrect

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Hi,

    Can you show me the JSON that is returned from the server when the table is initially loaded please? The browser’s Network Inspector will be able to show you that.

    Thanks,
    Allan

  • sanderksanderk Posts: 26Questions: 4Answers: 1

    This is without the table name:

    {draw: 1, data: [{DT_RowId: "row_2", naamparticipant: "sander22", aandeelparticipant: 1,…},…],…}
    cancelled: []
    data: [{DT_RowId: "row_2", naamparticipant: "sander22", aandeelparticipant: 1,…},…]
    0: {DT_RowId: "row_2", naamparticipant: "sander22", aandeelparticipant: 1,…}
    DT_RowId: "row_2"
    GebouwId: 2
    Winstuitkeringen: [{id: "2"}, {id: "3"}]
    aandeelparticipant: 1
    einddatum: "1900-01-01T00:00:00"
    id: 2
    ingangsdatum: "2020-06-02T00:00:00"
    naamparticipant: "sander22"
    1: {DT_RowId: "row_6", naamparticipant: "test", aandeelparticipant: 10,…}
    2: {DT_RowId: "row_1", naamparticipant: "Sander", aandeelparticipant: 60,…}
    debug: null
    draw: 1
    error: null
    fieldErrors: []
    files: {}
    id: null
    meta: {}
    options: {}
    recordsFiltered: 3
    recordsTotal: 3
    searchPanes: {options: {}}
    upload: {id: null}
    

    This is if I specify the table name

    {draw: 1,…}
    cancelled: []
    data: [{DT_RowId: "row_8", NaamHuurder: "Sander", Huisnummer: "10", HuurcontractHuurder: "Commercieel",…},…]
    0: {DT_RowId: "row_8", NaamHuurder: "Sander", Huisnummer: "10", HuurcontractHuurder: "Commercieel",…}
    AantalM2BVO: "2,00"
    AantalM2VVO: "2,00"
    DT_RowId: "row_8"
    EindDatumContract: "02-06-2020"
    GebouwId: "2"
    Huisnummer: "10"
    HuurcontractHuurder: "Commercieel"
    HuurderExtraEigenschappen: [{id: "3"}]
    Huurders: {id: 8, NaamHuurder: "Sander", Huisnummer: "10", HuurcontractHuurder: "Commercieel",…}
    NaamHuurder: "Sander"
    Opzegtermijn: "1"
    StartDatumContract: "02-06-2020"
    1: {DT_RowId: "row_9", NaamHuurder: "Test huurder", Huisnummer: "3", HuurcontractHuurder: "23",…}
    debug: null
    draw: 1
    error: null
    fieldErrors: []
    files: {}
    id: null
    meta: {}
    options: {}
    recordsFiltered: 2
    recordsTotal: 2
    searchPanes: {options: {}}
    upload: {id: null}
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Hi,

    I'm not seeing Uitkeringsdatum in the JSON above I'm afraid. Are you able to show the entire JSON string, rather than just part of it (some of it is cut off with ellipsis).

    the parent child function breaks.

    Regarding this point, I'd probably need a link to a page showing that issue to be able to help I think. Are there any Javascript errors shown?

    Thanks,
    Allan

  • sanderksanderk Posts: 26Questions: 4Answers: 1

    This first screenshot is what I get when i use the following code in my controller:
    .Model<ParticipantViewModel>()
    The parent child function works, but the dates are wrong.

    If I change this into:

    .Model<ParticipantViewModel>("Participanten")

    You get the screenshot below with the right dates, but the parent child function doesn't work anymore.

    This is the JSON from the table at the bottom in the first screenshot.

    There are no javascript errors when the parent child function breaks.

    {draw: 8, data: [{DT_RowId: "row_2",…}, {DT_RowId: "row_3",…}], recordsTotal: 2, recordsFiltered: 2,…}
    cancelled: []
    data: [{DT_RowId: "row_2",…}, {DT_RowId: "row_3",…}]
    0: {DT_RowId: "row_2",…}
    DT_RowId: "row_2"
    Participanten: {id: 2, naamparticipant: "sander22", aandeelparticipant: 1, ingangsdatum: "2020-06-02T00:00:00",…}
    GebouwId: 2
    aandeelparticipant: 1
    einddatum: "1900-01-01T00:00:00"
    id: 2
    ingangsdatum: "2020-06-02T00:00:00"
    naamparticipant: "sander22"
    Winstuitkeringen: {Uitkeringsdatum: "2020-06-04T00:00:00", Bedrag: 534, Participantid: 2}
    Bedrag: 534
    Participantid: 2
    Uitkeringsdatum: "2020-06-04T00:00:00"
    1: {DT_RowId: "row_3",…}
    DT_RowId: "row_3"
    Participanten: {id: 2, naamparticipant: "sander22", aandeelparticipant: 1, ingangsdatum: "2020-06-02T00:00:00",…}
    GebouwId: 2
    aandeelparticipant: 1
    einddatum: "1900-01-01T00:00:00"
    id: 2
    ingangsdatum: "2020-06-02T00:00:00"
    naamparticipant: "sander22"
    Winstuitkeringen: {Uitkeringsdatum: "2020-06-05T00:00:00", Bedrag: 3453, Participantid: 2}
    Bedrag: 3453
    Participantid: 2
    Uitkeringsdatum: "2020-06-05T00:00:00"
    debug: null
    draw: 8
    error: null
    fieldErrors: []
    files: {}
    id: null
    meta: {}
    options: {,…}
    Winstuitkeringen.Participantid: [{value: 1, label: "Sander"}, {value: 2, label: "sander22"}, {value: 6, label: "test"}]
    0: {value: 1, label: "Sander"}
    label: "Sander"
    value: 1
    1: {value: 2, label: "sander22"}
    label: "sander22"
    value: 2
    2: {value: 6, label: "test"}
    label: "test"
    value: 6
    recordsFiltered: 2
    recordsTotal: 2
    searchPanes: {options: {}}
    options: {}
    upload: {id: null}
    id: null
    
    
  • sanderksanderk Posts: 26Questions: 4Answers: 1

    It looks like this function in the JavaScript code doesn't pass any value when I set the table name in the controller

    ajax: {
                        url: "/Gebouwen/WinstuitkeringenTable",
                        type: "POST",
                        data: function (d) {
                            var selected = parTable.row({ selected: true });
                            if (selected.any()) {
                                d.Participantid = selected.data().id;
                            }
                        }
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    My guess is that with the table name added on the server-side you also need to add it on the client-side - for example:

    d.Participantid = selected.data().id;
    

    might need to become:

    d.Participantid = selected.data().Participanten.id;
    

    If it isn't that, can you show me your Javascript code please?

    Allan

  • sanderksanderk Posts: 26Questions: 4Answers: 1

    Ok, so this solved part of the problem. Thanks for that. The problem still remaining is as follows:


    In this screenshot we have a table with a lookup table

    The Javascript code is:

    var editorInvestering;
    
    $(document).ready(function () {
        editorInvestering = new $.fn.dataTable.Editor({
            ajax: {
                url: "/Gebouwen/InvesteringenTable",
                type: "POST",
                data: function (d) {
                    d.GebouwID = currentGebouwID
                }
            },
            table: "#investeringenTable",
            fields: [{
                label: "Onderdeel:",
                name: "Investeringen.InvesteringsOnderdeelId",
                type: "select",
                placeholder: "Selecteer een onderdeel"
            }, {
                label: "Datum:",
                name: "Investeringen.Datum",
                type: "datetime",
                format: 'DD-MM-YYYY'
            }, {
                label: "Investeringsbedrag:",
                name: "Investeringen.Investeringsbedrag"
            }, {
                label: "Groot onderhoud:",
                name: "Investeringen.GrootOnderhoud"
            }, {
                label: "Opmerkingen:",
                name: "Investeringen.Opmerkingen",
                type: "textarea"
            }
            ],
            i18n: {
                create: {
                    button: "Nieuw",
                    title: "Nieuwe invoer",
                    submit: "Opslaan"
                },
                edit: {
                    button: "Bewerken",
                    title: "Bewerk record",
                    submit: "Opslaan"
                },
                remove: {
                    button: "Verwijder",
                    title: "Verwijder",
                    submit: "Verwijder",
                    confirm: {
                        _: "Weet u zeker dat u %d records wilt verwijderen?",
                        1: "Weet u zeker dat u dit record wilt verwijderen?"
                    }
                },
                error: {
                    system: "Er is een systeemfout opgetreden. Neem aub contact op met de ontwikkelaar."
                },
                datetime: {
                    previous: 'Vorige',
                    next: 'Volgende',
                    months: ['Januari', 'Februari', 'Maart', 'April', 'Mei', 'Juni', 'Juli', 'Augustus', 'September', 'Oktober', 'November', 'December'],
                    weekdays: ['Zo', 'Ma', 'Di', 'Wo', 'Do', 'Vr', 'Za']
                }
            }
        });
    
    
    
        $('#investeringenTable').DataTable({
            dom: "Bfrtip",
            ajax: {
                url: "/Gebouwen/InvesteringenTable",
                type: "POST",
                data: function (d) {
                    d.GebouwID = currentGebouwID
                }
            },
            serverSide: true,
            order: [[1, 'asc']],
            pageLength: 10,
            language: {
                url: '/json/datatables-dutch.json'
            },
            columns: [
                {
                    data: null,
                    defaultContent: '',
                    className: 'select-checkbox',
                    orderable: false
                },
                { data: "Investeringen.Datum" },
                { data: "InvesteringsOnderdelen.Naam" },
                {
                    data: "Investeringen.Investeringsbedrag",
                    render: $.fn.dataTable.render.number('.', ',', 2, '€')
                },
                {
                    data: "Investeringen.GrootOnderhoud",
                    render: $.fn.dataTable.render.number('.', ',', 2, '€')
                }
            ],
            keys: {
                columns: ':not(:first-child)',
                editor: editorInvestering
            },
            select: {
                style: 'os',
                selector: 'td:first-child',
                blurable: true
            },
    
            buttons: [
                { extend: "create", editor: editorInvestering },
                { extend: "edit", editor: editorInvestering },
                { extend: "remove", editor: editorInvestering }
            ]
        });
    
    });
    

    The controller code (C#) is:

    [HttpGet]
            [HttpPost]
            public ActionResult InvesteringenTable([FromForm]string GebouwID = "-1")
            {
    
                using (var db = new Database("azure", _configuration.GetConnectionString("BBGroepDataContext")))
                {
                    var editor = new Editor(db, "Investeringen", "Id")
                        .Model<InvesteringViewModel>("Investeringen")
                        .Model<OnderdeelViewModel>("InvesteringsOnderdelen")
                        .Field(new Field("Investeringen.InvesteringsOnderdeelId")
                            .Options(new Options()
                                .Table("InvesteringsOnderdelen")
                                .Value("Id")
                                .Label("Naam")
                            )
                            .Validator(Validation.DbValues(new ValidationOpts { Empty = false }))
                         )
                        .LeftJoin("InvesteringsOnderdelen", "InvesteringsOnderdelen.Id","=", "Investeringen.InvesteringsOnderdeelId")
                        .Field(new Field("Investeringen.GebouwId")
                            .SetValue(GebouwID)
                         )
                        .Field(new Field("Investeringen.Datum")
                            .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een geldige datum in." }))
                            .GetFormatter(Format.DateSqlToFormat("dd-MM-yyyy"))
                            .SetFormatter(Format.DateFormatToSql("dd-MM-yyyy"))
                         )
                        .Field(new Field("Investeringen.Investeringsbedrag")
                             .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een getal in." }))
                            .Validator(Validation.Numeric("nl-NL", new ValidationOpts { Message = "Voer een getail in." }))
                            .SetFormatter((val, data) => val.ToString().Replace(",", "."))
                            .GetFormatter((val, data) => val.ToString().Replace(".", ","))
                         )
                        .Field(new Field("Investeringen.GrootOnderhoud")
                             .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een getal in." }))
                            .Validator(Validation.Numeric("nl-NL", new ValidationOpts { Message = "Voer een getal in." }))
                            .SetFormatter((val, data) => val.ToString().Replace(",", "."))
                         )
                        .Where("Investeringen.GebouwId", GebouwID);
    
                    editor.PostCreate += (sender, e) => _LogChange(db, "Investeringen", "create", e.Id, e.Values);
                    editor.PostEdit += (sender, e) => _LogChange(db, "Investeringen", "edit", e.Id, e.Values);
                    editor.PostRemove += (sender, e) => _LogChange(db, "Investeringen", "remove", e.Id, e.Values);
    
    
                    return Json(editor.Process(Request).Data());
                }
            }
    

    As you can see the .Setformatter doesn't work.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    It looks like it is the GetFormatter that isn't working.

    Could you show me the InvesteringViewModel? Coudl you also show me the JSON response from the server for that API end point?

    Thanks,
    Allan

  • sanderksanderk Posts: 26Questions: 4Answers: 1

    This is the response:

    {draw: 1, data: [{DT_RowId: "row_1",…}], recordsTotal: 1, recordsFiltered: 1, error: null,…}
    cancelled: []
    data: [{DT_RowId: "row_1",…}]
    0: {DT_RowId: "row_1",…}
    DT_RowId: "row_1"
    Investeringen: {InvesteringsOnderdeelId: 4, GebouwId: 2, Datum: "2020-01-03T00:00:00", Investeringsbedrag: 19102,…}
    Datum: "2020-01-03T00:00:00"
    GebouwId: 2
    GrootOnderhoud: 6921
    InvesteringsOnderdeelId: 4
    Investeringsbedrag: 319102
    Opmerkingen: ""
    InvesteringsOnderdelen: {Naam: "Renovatie"}
    Naam: "Renovatie"
    debug: null
    draw: 1
    error: null
    fieldErrors: []
    files: {}
    id: null
    meta: {}
    options: {,…}
    Investeringen.InvesteringsOnderdeelId: [{value: 6, label: "CV ketel"}, {value: 3, label: "Dak"}, {value: 4, label: "Renovatie"},…]
    0: {value: 6, label: "CV ketel"}
    label: "CV ketel"
    value: 6
    1: {value: 3, label: "Dak"}
    label: "Dak"
    value: 3
    2: {value: 4, label: "Renovatie"}
    label: "Renovatie"
    value: 4
    3: {value: 5, label: "Sprinkler"}
    label: "Sprinkler"
    value: 5
    4: {value: 1, label: "Verbouwingen"}
    label: "Verbouwingen"
    value: 1
    5: {value: 2, label: "Zonnepanelen"}
    label: "Zonnepanelen"
    value: 2
    recordsFiltered: 1
    recordsTotal: 1
    searchPanes: {options: {}}
    options: {}
    upload: {id: null}
    id: null
    

    And this is the viewmodel

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    
    namespace Website.Models.ViewModels
    {
        public class InvesteringViewModel
        {
            public DateTime Datum { get; set; }
            public int InvesteringsOnderdeelId { get; set; }
            public decimal Investeringsbedrag { get; set; }
            public decimal GrootOnderhoud { get; set; }
            public string Opmerkingen { get; set; }
            public int GebouwId { get; set; }
        }
    
        public class OnderdeelViewModel
        {
            public string Naam { get; set; }
        }
    }
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Investeringen: {InvesteringsOnderdeelId: 4, GebouwId: 2, Datum: "2020-01-03T00:00:00", Investeringsbedrag: 19102,…}

    No question that the get formatter isn't working.

    public DateTime Datum { get; set; }
    

    could you change the DateTime to be just string please? I think that might be the issue.

    Thanks,
    Allan

  • sanderksanderk Posts: 26Questions: 4Answers: 1
    edited June 2020

    This works for the date, however the decimal format is still not set correctly. I aslso use the get formatter to show numbers with a comma as decimal seperator.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    You are using $.fn.dataTable.render.number('.', ',', 2, '€') on the client-side, so I would suggest removing the server-side formatter for your decimal numbers.

    Allan

  • sanderksanderk Posts: 26Questions: 4Answers: 1
    edited June 2020

    But that code is only for the table. The editor form doesn't take that code.

    The users must be able to edit the numbers in Dutch format (comma as decimal)

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    If you use a getFormatter with the correct format server side and use no formatter whatsoever client side, it works! Regardless of whether it is a Data Table or Editor. I support two languages, English (UK) and German (Germany). This concept works for numbers, dates and what have you. And Dutch numbers are the same as German numbers ...

  • sanderksanderk Posts: 26Questions: 4Answers: 1
    edited June 2020

    What getformaater do you use on the server side? My problem is that when you have fields on the server side in the form of table.property it doesn't work. I removed the client side formatter.

    This is my server side code but when i open the edior window i still get a number wih 55.10 in stead of 55,10. The table is showing the correct format.

    .Field(new Field("Kosten.Bedrag") Is the problem.

    Can you give me an example of your solution for German?

    [HttpGet]
            [HttpPost]
            public ActionResult KostenTable([FromForm]string GebouwID = "-1")
            {
    
                using (var db = new Database("azure", _configuration.GetConnectionString("BBGroepDataContext")))
                {
                    var editor = new Editor(db, "Kosten", "Id")
                        .Model<KostenViewModel>("Kosten")
                        .Model<KostensoortViewModel>("Kostensoorten")
                        .Field(new Field("Kosten.KostenSoortID")
                            .Options(new Options()
                                .Table("Kostensoorten")
                                .Value("Id")
                                .Label("NaamKostensoort")
                            )
                            .Validator(Validation.DbValues(new ValidationOpts { Empty = false, Message = "Selecteer een kostensoort" }))
                         )
                        .LeftJoin("Kostensoorten", "Kostensoorten.Id", "=", "Kosten.KostenSoortID")
                        .Field(new Field("Kosten.GebouwenId")
                            .SetValue(GebouwID)
                         )
                        .Field(new Field("Kosten.Jaar")
                            .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een jaartal in." }))
                            .Validator(Validation.Numeric("nl-NL", new ValidationOpts { Message = "Voer een jaartal in." }))
                         )
                        .Field(new Field("Kosten.Bedrag")
                            .Validator(Validation.NotEmpty(new ValidationOpts { Message = "Voer een bedrag in." }))
                            .Validator(Validation.Numeric("nl-NL", new ValidationOpts { Message = "Voer een bedrag in." }))
                            .SetFormatter((val, data) => val.ToString().Replace(",", "."))
                            .GetFormatter((val, data) => val.ToString().Replace(".", ","))
                         )
                        .Where("Kosten.GebouwenId", GebouwID);
    
                    editor.PostCreate += (sender, e) => _LogChange(db, "Kosten", "create", e.Id, e.Values);
                    editor.PostEdit += (sender, e) => _LogChange(db, "Kosten", "edit", e.Id, e.Values);
                    editor.PostRemove += (sender, e) => _LogChange(db, "Kosten", "remove", e.Id, e.Values);
    
    
                    return Json(editor.Process(Request).Data());
                }
            }
    
  • sanderksanderk Posts: 26Questions: 4Answers: 1

    If I change from decimal to string type in the model, the editor is correct but the table is wrong. I'm lost

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    Can you give me an example of your solution for German?

    Sure, but my server language is PHP. But it should be very similar for other languages. I use a custom formatter not one of the built-in ones. (That is not for a special reason just because I found it simpler ... no docs to read :smile: )

    For German:
    The getFormatter converts 1000000.99 into 1.000.000,99
    The setFormatter converts 1.000.000,99 into 1000000.99

    For English:
    The getFormatter converts 1000000.99 into 1,000,000.99
    The setFormatter converts 1,000,000.99 into 1000000.99

    I use language dependent field masking at the Editor front end to make sure the values sent from the client have the expected German or English format.

    Here are the get and set formatters for English and German:

    Field::inst( 'myField' )
        ->getFormatter( function($val, $data, $opts) {
            if ($_SESSION['lang'] === 'de') {     
                return number_format($val, 2, ',', '.');
            }
            return number_format($val, 2);
        })
        ->setFormatter( function($val, $data, $opts) {
            //explode to get rid of the 1,000 separators
            if ($_SESSION['lang'] === 'de') {     
                $numberArray = explode('.', $val);
            } else {
                $numberArray = explode(',', $val);
            }
            //implode without delimiter to join the pieces again
            $numberString = implode($numberArray);
            //replace the German decimal comma with a period
            if ($_SESSION['lang'] === 'de') {   
                $numberString = str_replace(',', '.', $numberString);
            }
            return $numberString;
        }),  
    

    Maybe you can do the setFormatter a bit more elegantly. It was one of my first PHP programming tasks a couple of years ago ...

  • sanderksanderk Posts: 26Questions: 4Answers: 1

    Thanks! I think a couple of problems are in the net library because I have to change every type in my model to string

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Heard of that before. You might find something about this by searching the forum. I noticed that Editor sends everything as strings but it didn't really cause any trouble for me using PHP with MySQL. (MySQL doesn't mind getting strings for numeric fields either - as long as they have the correct format. All inserts and updates work fine.)

This discussion has been closed.