Inserting master+detail

[GOMEZEL_DINO]

Hello!

I have a similar situation to the situation, described here:

How do I use master/detail when a new record is being inserted in master?

I am using two TXDataWebDataSet, a master/detail relationship, when I am inserting a new record in the master the table ID does not exist yet, when doing the relationship below the ID is reset and an error occurs saying that there is no ID zero ( 0). How do I use master/detail when a new record is being inserted in master? TJSObject(wdsDetail.CurrentData)['PEOPLE@xdata.ref'] := Format('PEOPLE(%d)', [wdsMasterID.AsInteger]);

I read carefully the post, but I can't make it work

wdsOdvoz - master
wdsOdvozPodrobno - detail

In short, I open the master dataset and set $id=1, then I open the detail and fill it, also adding ref to each row. Upon saving I transfer the JSON to the master and save just the master.

Here is what I do:

I open the master (FOdvozId = 0 for new entries, >0 for editing an existing entry):

  wdsOdvoz.Close;
  wdsOdvoz.QueryString := '$filter=Id eq '+FOdvozId.ToString+' and Lokacija/Aktivna ne 0&$orderby=Id desc&$top=1&$expand=Podrobno/Posoda,Lokacija/Stranka,Vozilo/TipVozila';
  wdsOdvoz.Load;

After that, I fill the master data:

procedure TfrmOdvoz.wdsOdvozAfterOpen(DataSet: TDataSet);
begin
  wdsOdvoz.Edit;
  TJSObject(wdsOdvoz.CurrentData)['$id'] := 1;
  wdsOdvoz.FieldByName('Podrobno').Value := TJSArray.New;
  ...
  wdsOdvoz.Post;

  wdsOdvozPodrobno.Close;
  wdsOdvozPodrobno.SetJsonData(wdsOdvoz.FieldByName('Podrobno').Value);
  wdsOdvozPodrobno.Open;
end;

After that I fill the detail data:

It's al displayed correctly, but when I click "Save" on the GUI, IO get an 500 internal server error. Here's the code for saving:

  if wdsOdvoz.State in dsEditModes then
    wdsOdvoz.Post;

  if wdsOdvozPodrobno.State in dsEditModes then
    wdsOdvozPodrobno.Post;

  wdsOdvoz.Edit;
  wdsOdvoz.FieldByName('Podrobno').Value := TJSArray.New(TJSJSON.parseObject(TJSJSON.stringify(TJSObject(wdsOdvozPodrobno.CurrentData))));
  wdsOdvoz.Post;
  wdsOdvoz.ApplyUpdates;

My question is - what I'm doing wrong? I changed the code many times, but no success. I'm stuck and any suggestion would be appreciated.

[wlandgraf (Wagner Landgraf)]

@GOMEZEL_DINO

After that, I fill the master data:

That code in TfrmOdvoz.wdsOdvozAfterOpen is strange. Why are you editing an existing record? Note that when you retrieve data from the server, by default the $id property is already set in master and detail references it. So there is no need to change it (only for new records).

Always check the JSON being retrieved from and sent to the server in the browser developer tool to have an idea of the internal JS structure that is in the dataset.

What is the 500 error you get (you can check in the response of the request? And does it happen when inserting or editing a record?

[GOMEZEL_DINO]

About the $id - it is present even if the returned dataset is empty? For the new entries the query returns an empty dataset (because I search for ID=0, that is not present in the DB). I've done the query so it is the same for new entries and for editing of existing entries (the latter is not yet in use).

The edit is possibly a mistake, you're right.

About the error 500, here's what I see in the console:

[GOMEZEL_DINO]

Mostly the application will insert new master and detail records, editing is only used exceptionally (just to repair operator user errors).

[GOMEZEL_DINO]

Update: I added append instead of edit, but the result is the same - the error is on applyupdates.

[wlandgraf (Wagner Landgraf)]

@GOMEZEL_DINO

About the $id - it is present even if the returned dataset is empty?

It's present in existing records. If there are no records, then of course there is no $id.

@GOMEZEL_DINO

About the error 500, here's what I see in the console:

You need to see the JSON response in the network tab.

[GOMEZEL_DINO]

I learned something new

Never used the network tab, here's the error

{
    "error": {
        "code": "ReferencedObjectNotFound",
        "message": "Referenced object not found. JSON object with $id = 1 does not exist."
    }
}

[GOMEZEL_DINO]

and here's the object sent (note: $id is on the end):

{
    "Id": null,
    "CasOdvoza": "2024-03-25T14:42:51.913",
    "PodpisStranke": null,
    "Opombe": null,
    "Vozilo": {
        "Id": 2,
        "RegistrskaSt": "KP12345",
        "Aktivno": false,
        "Letnik": 2020,
        "PrvaRegistracija": "2020-01-01",
        "Znamka": "IVECO",
        "Tip": "KAMJONCIN",
        "MocKW": 120,
        "Prostornina": 3000,
        "StSasije": "12345678901234567890",
        "MasaVozila": 4000,
        "MasaNajvecja": 5500,
        "PrtljaznikVolM3": 6,
        "TipVozila": {
            "Id": 1,
            "Opis": "Tovornjak"
        },
        "VrstaOdvoza": {
            "Id": 2,
            "Opis": "Kontejnerski",
            "Params@xdata.proxy": "TipOdvoza(2)/Params",
            "Kolicina": 0,
            "Volumen": 1,
            "Teza": 1
        },
        "Odvozi@xdata.proxy": "Vozilo(2)/Odvozi"
    },
    "Vozilo.TipVozila": null,
    "Vozilo.TipVozila.Id": null,
    "Vozilo.TipVozila.Opis": null,
    "Vozilo.Vrstaodvoza": null,
    "Vozilo.Vrstaodvoza.Id": null,
    "Vozilo.Vrstaodvoza.Opis": null,
    "Vozilo.Vrstaodvoza.Params": null,
    "Vozilo.Odvozi": null,
    "Vozilo.Id": null,
    "Vozilo.RegistrskaSt": null,
    "Vozilo.Aktivno": null,
    "Vozilo.Letnik": null,
    "Vozilo.PrvaRegistracija": null,
    "Vozilo.Znamka": null,
    "Vozilo.Tip": null,
    "Vozilo.MocKW": null,
    "Vozilo.Prostornina": null,
    "Vozilo.StSasije": null,
    "Vozilo.MasaVozila": null,
    "Vozilo.MasaNajvecja": null,
    "Vozilo.PrtljaznikVolM3": null,
    "Vozilo.Vrstaodvoza.Kolicina": null,
    "Vozilo.Vrstaodvoza.Volumen": null,
    "Vozilo.Vrstaodvoza.Teza": null,
    "Lokacija": {
        "Id": 5,
        "Aktivna": true,
        "Naziv": "Sečovlje 35",
        "Opombe": "test",
        "Lat": 45.4764137126401,
        "Lon": 13.6193223529175,
        "AktivnaOd": "2024-02-28",
        "AktivnaDo": "2024-09-01",
        "Stranka": {
            "Id": 4,
            "Aktivna": true,
            "Naziv": "Igor Jerebica",
            "Naslov": "SEČOVLJE 35",
            "Posta": "6333",
            "Kraj": "SEČOVLJE",
            "ZunanjaOznaka": null,
            "DavSt": null,
            "Tel": null,
            "Opombe": null,
            "Lokacije@xdata.proxy": "Stranka(4)/Lokacije"
        },
        "Odvozi@xdata.proxy": "Lokacija(5)/Odvozi",
        "Posode@xdata.proxy": "Lokacija(5)/Posode"
    },
    "Lokacija.Stranka": null,
    "Lokacija.Stranka.Id": null,
    "Lokacija.Stranka.Aktivna": null,
    "Lokacija.Stranka.Naziv": null,
    "Lokacija.Stranka.ZunanjaOznaka": null,
    "Lokacija.Stranka.DavSt": null,
    "Lokacija.Stranka.Opombe": null,
    "Lokacija.Stranka.Lokacije": null,
    "Lokacija.Odvozi": null,
    "Lokacija.Id": null,
    "Lokacija.Aktivna": null,
    "Lokacija.PrevzemnoMesto": null,
    "Lokacija.StOtoka": null,
    "Lokacija.Opombe": null,
    "Lokacija.Lat": null,
    "Lokacija.Lon": null,
    "Lokacija.AktivnaOd": null,
    "Lokacija.AktivnaDo": null,
    "Lokacija.Stranka.Pot": null,
    "Voznik": {
        "Id": 20,
        "UporabniskoIme": "markom",
        "Ime": "Marko",
        "Priimek": "MILUTINOVIĆ",
        "Aktiven": true,
        "Vloga@xdata.proxy": "Uporabnik(20)/Vloga"
    },
    "Voznik.Id": null,
    "Voznik.UporabniskoIme": null,
    "Voznik.Geslo": null,
    "Voznik.PIN": null,
    "Voznik.Ime": null,
    "Voznik.Priimek": null,
    "Voznik.Aktiven": null,
    "Podrobno": [
        {
            "Odvoz": {
                "$ref": 1
            },
            "Odvoz.Vozilo": null,
            "Odvoz.Lokacija": null,
            "Odvoz.Voznik": null,
            "Odvoz.Podrobno": null,
            "Odvoz.Id": null,
            "Odvoz.CasOdvoza": null,
            "Odvoz.PodpisStranke": null,
            "Odvoz.Opombe": null,
            "Posoda": {
                "Id": 13,
                "Opis": "BIO 120",
                "VolumenPosode": 120,
                "TezaPosode": 120,
                "VnosTeze": false,
                "Frakcija@xdata.proxy": "Posoda(13)/Frakcija"
            },
            "Posoda.Frakcija": null,
            "Posoda.Id": null,
            "Posoda.Opis": null,
            "Posoda.VolumenPosode": null,
            "Posoda.TezaPosode": null,
            "Posoda.VnosTeze": null,
            "Id": null,
            "Kolicina": 1,
            "Teza": 0,
            "Volumen": 0,
            "Pranje": null
        }
    ],
    "$id": 1
}

[wlandgraf (Wagner Landgraf)]

The error message indicates that the reason of the error is the JSON being sent doesn't have the $id property. And indeed, the JSON doesn't have $id in the main object.

So your code that tries to set the $id is not working well, and that's exactly the part I mentioned is wrong (the Edit/Post construction).

[GOMEZEL_DINO]

isn't on the end of the JSON?

[wlandgraf (Wagner Landgraf)]

You are right, indeed. But it needs to be at the beginning of the JSON.

But note that, as explained in the topic you referred to, if the record/object exists in the server, you don't necessarily need to add the $ref property, you can just add the reference format (suffixed with @xdata.ref) because the record already has an id value (not $id, do not mix!).

In the case of new records, the example also suggest to set the $id property in the OnNewRecord event, as then it will be the first property to be set.

[GOMEZEL_DINO]

Thank you, I will try to implement all you've written and report back for other users :)

[GOMEZEL_DINO]

Hello!

I added this code (and emoved the $id from the other part of the code):

procedure TfrmOdvoz.wdsOdvozNewRecord(DataSet: TDataSet);
begin
  TJSObject(wdsOdvoz.CurrentData)['$id'] := 1;
end;

But the $id is still on the end of the JSON - how do I set it on the front?

about the @xdata.ref - thank for the tip, but there will be 99% of inserting new data - editing will be just for other users.

[wlandgraf (Wagner Landgraf)]

Can you please send a minimal project reproducing the issue?

[GOMEZEL_DINO]

Sure, how do I deliver it?

[GOMEZEL_DINO]

Hello!

Sent to support mail

[wlandgraf (Wagner Landgraf)]

@GOMEZEL_DINO

Sure, how do I deliver it?

You can send a private message to me via this Support Center.

[GOMEZEL_DINO]

For documentation purposes - one possible solution (thank you Wagner!) is to add $id as a field in the dataset (see pic below).

then add code to the OnNewRecord event

procedure TfrmOdvoz.wdsOdvozNewRecord(DataSet: TDataSet);
begin
  Dataset.FieldByName('$id').Value := 1;
end;

[GOMEZEL_DINO]

Also for saving the detail rows I used this and it worked

  arrayPodrobno := TJSArray.New; 

  wdsOdvozPodrobno.First;
  while not wdsOdvozPodrobno.Eof do begin
    itm := TJSObject(wdsOdvozPodrobno.CurrentData);
    arrayPodrobno.Push(itm);
    wdsOdvozPodrobno.Next;
  end;

  wdsOdvoz.Edit;
  wdsOdvoz.FieldByName('Podrobno').Value := arrayPodrobno; 
  wdsOdvoz.Post;

  wdsOdvoz.ApplyUpdates;