Create dataset in empty joined table

Create dataset in empty joined table

titechnotitechno Posts: 16Questions: 5Answers: 0

Hi,
I would like to know (since I haven't found any information on this topic) if it is possible that the editor creates a new dataset in a joined table where there are no datasets yet that match the join conditions?

Example:

Table A

Customer_ID | Customer | Address | More customer Information

Table B

Customer_ID | Revenue | Year

I dont realy want to create all the data entries in the second table if there was no revenue for a given year but still want to be able to edit all customers from Table A in an Editor.

Thanks for the info in advance

Answers

  • colincolin Posts: 14,452Questions: 1Answers: 2,462

    You can do nested editing, as shown in this example. When the datatable data type is used, you can create and amend those values in the linked table. Is that what you're looking for?

    Colin

  • titechnotitechno Posts: 16Questions: 5Answers: 0
    edited December 2021

    Hi,
    I am afraid not realy since I just want to edit the column "revenue" with inline editing in Table B and dont want a whole new editor window for editing.

  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    To confirm my understanding, you want to inline edit the value in a joined data base row, which is shown to the end user as part of the host row?

    Yes it is possible, but you must also include a hidden field in the form which contains the primary key value (or whatever value you are performing the join on - I presume that is the primary key) - use hidden to do that.

    Since you are inline editing you will also need to submit the whole row of data to make sure that hidden field gets included:

            editor.inline( this, {
                submit: 'allIfChanged'
            } );
    

    Allan

  • titechnotitechno Posts: 16Questions: 5Answers: 0
    edited December 2021

    So I finaly came around to try the things you suggested. I am desperate at this point because I can't find out what the problem seems to be. The join seems to work, no problem but the value wont be written to the datatable without any error message.
    I currently use this implementation (3 Tables joined on each other):

    C# API Call:

            [Route("/lieferanten/lieferantenReportingApi")]
            [HttpGet]
            [HttpPost]
            public IActionResult LieferantenReportingApi()
            {
                var dbType = Environment.GetEnvironmentVariable("DBTYPE");
                var dbConnection = Environment.GetEnvironmentVariable("DBCONNECTION");
                using (var db = new Database(dbType, dbConnection))
                {
                    var response = new Editor(db, "Autohaus")
                        .Model<AutohausJoinModel>("Autohaus")
                        .Model<UmsatzLieferantenJoinModel>("UmsatzLieferanten")
                        .Model<MonatsUmsatzLieferantenJoinModel>("MonatsUmsatzLieferanten")
                        .Field(new Field("Autohaus.Id"))
                        .Field(new Field("Autohaus.Fabrikat"))
                        .Field(new Field("Autohaus.GesellschafterNummer")
                            .Validator(Validation.NotEmpty())
                            .Validator(Validation.MaxLen(3))
                            .Validator(Validation.Numeric())
                        )
                        .Field(new Field("Autohaus.AutohausNummer")
                            .Validator(Validation.NotEmpty())
                            .Validator(Validation.MaxLen(3))
                            .Validator(Validation.Numeric())
                        )
                        .Field(new Field("Autohaus.Name"))
                        .Field(new Field("Autohaus.Plz"))
                        .Field(new Field("Autohaus.Ort"))
                        .Field(new Field("Autohaus.Strasse"))
                        .Field(new Field("Autohaus.hinweis"))
                        .Field(new Field("UmsatzLieferanten.Umsatz"))
                        .Field(new Field("UmsatzLieferanten.Id"))
                        .Field(new Field("UmsatzLieferanten.jahr").SetValue("2020"))
                        .Field(new Field("UmsatzLieferanten.LiefNr").SetValue("1022"))
                        .Field(new Field("UmsatzLieferanten.AhNr"))
                        .Field(new Field("MonatsUmsatzLieferanten.Id"))
                        .Field(new Field("MonatsUmsatzLieferanten.Umsatz"))
                        .Field(new Field("MonatsUmsatzLieferanten.LiefNr"))
                        .Field(new Field("MonatsUmsatzLieferanten.AhNr"))
                        .Field(new Field("MonatsUmsatzLieferanten.Jahr"))
                        // TODO Lieferanten aus User Variable
                        .LeftJoin("MonatsUmsatzLieferanten", "Autohaus.GesellschafterNummer = MonatsUmsatzLieferanten.GesNr and Autohaus.AutohausNummer = MonatsUmsatzLieferanten.AhNr and MonatsUmsatzLieferanten.LiefNr = '1022' and Autohaus.Jahr = MonatsUmsatzLieferanten.Jahr")
                        .LeftJoin("UmsatzLieferanten", "Autohaus.GesellschafterNummer = UmsatzLieferanten.GesNr and Autohaus.AutohausNummer = UmsatzLieferanten.AhNr and UmsatzLieferanten.LiefNr = '1022' and Autohaus.Jahr = UmsatzLieferanten.Jahr")
                        //TODO Jahr aus globaler Variable
                        .Where("Autohaus.Jahr", 2020)
                        .TryCatch(false)
                        .Process(Request)
                        .Data();
    
                    return Json(response);
                }
            }
    

    JavaScript:

    var editor;
    
    $(document).ready(function () {
        loadDataTable();
    });
    
    
    function loadDataTable() {
        editor = new $.fn.dataTable.Editor({
            ajax: "/lieferanten/lieferantenReportingApi",
            table: "#DT_load",
            fields: [{
                label: "Fabrikat:",
                name: "Autohaus.Fabrikat"
            },{
                label: "Ges. Nr.:",
                name: "Autohaus.GesellschafterNummer"
            }, {
                label: "Ah. Nummer:",
                name: "Autohaus.AutohausNummer"
            }, {
                label: "Name:",
                name: "Autohaus.Name"
            }, {
                label: "Plz:",
                name: "Autohaus.Plz"
            }, {
                label: "Autohaus.Ort:",
                name: "Autohaus.Ort"
            }, {
                label: "Autohaus.Straße:",
                name: "Autohaus.Strasse"
            }, {
                label: "Monatsumsatz:",
                name: "MonatsUmsatzLieferanten.Umsatz"
            }, {
                label: "Umsatz:",
                name: "UmsatzLieferanten.Umsatz"
            },{
                label: "Hinweis:",
                name: "Autohaus.hinweis"
            }, {
                label: "MonatsUmsatzLieferantenId:",
                name: "MonatsUmsatzLieferanten.Id"
            }, {
                label: "UmsatzLieferantenId:",
                name: "UmsatzLieferanten.Id"
            }, {
                label: "MonatsUmsatzLieferantenAhNr:",
                name: "MonatsUmsatzLieferanten.AhNr"
            }, {
                label: "UmsatzLieferantenAhNr:",
                name: "UmsatzLieferanten.AhNr"
            }
            ]
        });
    
        var table = $('#DT_load').DataTable({
            dom: "Brtip",
            ajax: "/lieferanten/lieferantenReportingApi",
            order: [[1, 'asc']],
            iDisplayLength:10,
            columns: [
                {
                    data: "Autohaus.Id",
                    visible: false
                },
                {
                    data: "MonatsUmsatzLieferanten.Id",
                    visible: false
                },
                {
                    data: "MonatsUmsatzLieferanten.AhNr",
                    visible: false
                },
                {
                    data: "MonatsUmsatzLieferanten.Jahr",
                    visible: false
                },
                {
                    data: "MonatsUmsatzLieferanten.LiefNr",
                    visible: false
                },
                {
                    data: "UmsatzLieferanten.Id",
                    visible: false
                },
                {
                    data: "UmsatzLieferanten.AhNr",
                    visible: false
                },
                {
                    data: "UmsatzLieferanten.Jahr",
                    visible: false
                },
                {
                    data: "UmsatzLieferanten.LiefNr",
                    visible: false
                },
                {
                    data: "Autohaus.Fabrikat",
                    width: "1%"
                },
                {
                    data: "Autohaus.GesellschafterNummer",
                    width: "1%"
                },
                {
                    data: "Autohaus.AutohausNummer",
                    width: "1%" },
                {
                    data: "Autohaus.Name",
                    width: "20%"
                },
                {
                    data: "Autohaus.Plz"
                },
                {
                    data: "Autohaus.Ort"
                },
                {
                    data: "Autohaus.Strasse",
                    width: "20%"
                },
                {
                    data: "MonatsUmsatzLieferanten.Umsatz",
                    render: $.fn.dataTable.render.number('.', ',', 2, '', ' €')
                },
                {
                    data: "UmsatzLieferanten.Umsatz",
                    render: $.fn.dataTable.render.number('.', ',', 2, '', ' €'),
                    orderable: false
                },
                {
                    data: "Autohaus.hinweis",
                    width: "20%"
                }
            ],
            keys: {
                columns: ':nth-last-child(2)',
                editor: editor,
                submit: 'allIfChanged'
            },
            initComplete: function () {
                // Apply the search
                this.api().columns().every(function () {
                    var that = this;
    
                    $('input', this.footer()).on('keyup change clear', function () {
                        if (that.search() !== this.value) {
                            that
                                .search(this.value)
                                .draw();
                        }
                    });
                });
            }
        });
    
        $('#DT_load tfoot th').each(function () {
            var title = $(this).text();
            $(this).html('<input type="text" placeholder="Suche ' + title + '" />');
        });
    }
    

    And the HTML Part:

    <br />
    <style>
        tfoot input {
            width: 100%;
            padding: 3px;
            box-sizing: border-box;
        }
    </style>
    <div class="container row p-0 m-0">
        <div class="col-6">
            <h2 class="text-info text-black-50">Lieferanten Reporting</h2>
        </div>
        <div class="col-12 border p-3">
            <table id="DT_load" class="display" style="width:100%; max-height:80%">
                <thead>
                    <tr>
                        <th style="visibility:hidden">AHID</th>
                        <th style="visibility:hidden">MUID</th>
                        <th style="visibility:hidden">MUAH</th>
                        <th style="visibility:hidden">MUJAHR</th>
                        <th style="visibility:hidden">MULIEFNR</th>
                        <th style="visibility:hidden">UID</th>
                        <th style="visibility:hidden">UAH</th>
                        <th style="visibility:hidden">UJAHR</th>
                        <th style="visibility:hidden">ULIEFNR</th>
                        <th>Marke</th>
                        <th>Ges. Nr.</th>
                        <th>Ah. Nr.</th>
                        <th>Name</th>
                        <th>Plz</th>
                        <th>Ort</th>
                        <th>Straße</th>
                        <th>Bekannter Umsatz</th>
                        <th>Umsatz</th>
                        <th>Hinweis</th>
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th style="visibility:hidden">AHID</th>
                        <th style="visibility:hidden">MUID</th>
                        <th style="visibility:hidden">MUAH</th>
                        <th style="visibility:hidden">MUJAHR</th>
                        <th style="visibility:hidden">MULIEFNR</th>
                        <th style="visibility:hidden">UID</th>
                        <th style="visibility:hidden">UAH</th>
                        <th style="visibility:hidden">UJAHR</th>
                        <th style="visibility:hidden">ULIEFNR</th>
                        <th>Marke</th>
                        <th>Ges. Nummer</th>
                        <th>Ah. Nummer</th>
                        <th>Name</th>
                        <th>Plz</th>
                        <th>Ort</th>
                        <th>Straße</th>
                        <th>Monatsumsatz</th>
                        <th>Umsatz</th>
                        <th>Hinweis</th>
                    </tr>
                </tfoot>
            </table>
        </div>
    </div>
    
    @section Scripts{
        <script src="~/js/Lieferanten/LieferantenReporting.js"></script>
    }
    

    Currently I can't even put any value in the "Umsatz" field (Sorry for the german in the code but it shouldn't matter)

  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    The submit: 'allIfChanged' option inside keys isn't going to do anything. Could you instead add:

    formOptions: {
      inline: {
        submit: 'allIfChanged'
      }
    }
    

    to your Editor initialisation please?

    Then when you submit a change, in the Ajax inspector in your browser you should see the full form's data being submitted in the request parameters.

    Thanks,
    Allan

  • titechnotitechno Posts: 16Questions: 5Answers: 0

    It seems like the data is missing from the post data "Umsatz" is null allthough I inserted a value.

    Any idea what would cause this?

    data: [{DT_RowId: "row_1517",…}]
    0: {DT_RowId: "row_1517",…}
    Autohaus: {Id: null, Fabrikat: "Mehrmarken", GesellschafterNummer: "001", AutohausNummer: "000",…}
    AutohausNummer: "000"
    Fabrikat: "Mehrmarken"
    GesellschafterNummer: "001"
    Id: null
    Name: "RR Team GmbH"
    Ort: "Laubach"
    Plz: "35321"
    Strasse: "Schottenerstra&amp;#223;e 42"
    autohausNummer: null
    fabrikat: null
    gesellschafterNummer: null
    hinweis: ""
    id: 1517
    jahr: "2020"
    name: null
    ort: null
    plz: null
    strasse: null
    DT_RowId: "row_1517"
    MonatsUmsatzLieferanten: {Id: null, Umsatz: null, LiefNr: null, AhNr: null, Jahr: null, GesNr: null, umsatz: null}
    AhNr: null
    GesNr: null
    Id: null
    Jahr: null
    LiefNr: null
    Umsatz: null
    umsatz: null
    UmsatzLieferanten: {Umsatz: null, Id: 2137, jahr: "2020", LiefNr: "1022", AhNr: "000", GesNr: "001", user_id: null,…}
    AhNr: "000"
    GesNr: "001"
    Id: 2137
    Jahr: null
    LiefNr: "1022"
    Umsatz: null
    jahr: "2020"
    user_id: null
    
  • titechnotitechno Posts: 16Questions: 5Answers: 0

    Nvm. I missed a Join Column in the Datatable.
    Sorry for the trouble....

Sign In or Register to comment.