Create dataset in empty joined table

titechno

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

colin

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

titechno

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.

allan

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

titechno

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)

allan

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

titechno

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ß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

titechno

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