How to update an entity with an association

BIZ TMS Aurelius
1

I have an association set up where the associated entity may be linked to a number of entities. The main entity, an event is assigned to a location. The location can be associated to multiple entities - the link is on locationid. What I want to be able to do is to change the locationid on a event (e.g., switch it's location), but when I change the locationid, instead of just changing the locationid on the event, it tries to change the locationid in the location table that the event was linked to.

For example, event A is linked to locationid 100. I want to change the locationid to 200. When I make this change, it tries to change the master locationid to 200, instead of just changing the events locationid. The generated entity does not directly expose the locationid, so you need to update it via the attached Tlocation.locationid. I think this is what is causing the problem. I can remove the association and update the locationid on the event record and just add the locationid field, but I want to be able to include the location data.
Thanks.

type
TEvent = class;
TLocation = class;

[Entity]
[Table('Events')]
[Id('FEventid', TIdGenerator.IdentityOrSequence)]
TEvent = class
private
[Column('EventID', [TColumnProp.Required, TColumnProp.NoInsert, TColumnProp.NoUpdate])]
FEventid: Int64;

[Column('EventName', [TColumnProp.Required], 150)]
FEventname: string;

[Association([], CascadeTypeAll - [TCascadeType.Remove])]
[JoinColumn('LocationID', [], 'LocationID')]
FLocationid: TLocation;

public
property Eventid: Int64 read FEventid write FEventid;
property Eventname: string read FEventname write FEventname;
property Locationid: TLocation read FLocationid write FLocationid;
end;

[Entity]
[Table('Locations')]
[Id('FLocationid', TIdGenerator.IdentityOrSequence)]
TLocation = class
private
[Column('LocationID', [TColumnProp.Required, TColumnProp.NoInsert, TColumnProp.NoUpdate])]
FLocationid: Int64;

[Column('LocationName', [], 150)]
FLocationname: Nullable<string>;

[Column('Address1', [], 100)]
FAddress1: Nullable<string>;

[Column('Address2', [], 100)]
FAddress2: Nullable<string>;

[Column('Address3', [], 100)]
FAddress3: Nullable<string>;

[Column('City', [], 100)]
FCity: Nullable<string>;

[Column('StateCD', [], 10)]
FStatecd: Nullable<string>;

[Column('Region', [], 100)]
FRegion: Nullable<string>;

[Column('PostalCD', [], 20)]
FPostalcd: Nullable<string>;

[Column('CountryCD', [], 5)]
FCountrycd: Nullable<string>;

[Column('LocationPhone', [], 50)]
FLocationphone: Nullable<string>;

[Column('URLWWW', [], 250)]
FUrlwww: Nullable<string>;

public
property Locationid: Int64 read FLocationid write FLocationid;
property Locationname: Nullable read FLocationname write FLocationname;
property Address1: Nullable read FAddress1 write FAddress1;
property Address2: Nullable read FAddress2 write FAddress2;
property Address3: Nullable read FAddress3 write FAddress3;
property City: Nullable read FCity write FCity;
property Statecd: Nullable read FStatecd write FStatecd;
property Region: Nullable read FRegion write FRegion;
property Postalcd: Nullable read FPostalcd write FPostalcd;
property Countrycd: Nullable read FCountrycd write FCountrycd;
property Locationphone: Nullable read FLocationphone write FLocationphone;
property Urlwww: Nullable read FUrlwww write FUrlwww;
end;

2

Hi Vinal,

You did not show how you are changing the Event's Location. Usually this would work fine:

  SomeEvent := Manager.Find<TEvent> {...}
  NewLocation := Manager.Find<TLocation> {...}
  SomeEvent.Location := NewLocation;
  Manager.Flush; {or Manager.Flush(SomeEvent);

Regards,

1 Like
3

This blog post might help as well: TMS Software | Blog.

4

Thanks for responding. I was updating via the Swagger interface using Patch, e.g.,
{
"Locationtext": "Trying to update"
"LocationId": {
"Locationid": 2
}
}
Any other field would work, but not locationcd (it would try to change the value in location table instead of events). When Aurilius generates the entities, it does not include the field in the main entity so i tried to make the update in the JSON object for the associated entity. I then updated the event object to add the locationid field and this worked fine if I removed the association - but I wanted the ability to pull in the location details. So I added the association back and renamed it to LocationDetail. However, if I tried to update the record, it gives a SQL/ODBC error indicating that the locationid can't be in the update statement twice.
HOWEVER - i figured this out. I changed the column relationship in the JoinColumn to NoInsert,NoUpdate and then the ODBC/SQL error message went away. I think this must be a very common scenario for all foreign keys, for example countrycd, languagecd,etc for a person record, that is you need the ability to update the field directly, but you also want the ability to pull in the details (e.g., countryname).
Here is the update I made.

[Column('LocationText', [], 500)]
FLocationtext: Nullable;
[Association([], CascadeTypeAll - [TCascadeType.Remove])]
[JoinColumn('LocationID', [TColumnProp.NoInsert,TColumnProp.NoUpdate])]
FLocationDetail: TLocation;

5

Ok so you're using XData. I'm not sure about swagger UI but to update a association field using XData you should use the xdata.ref anottation:

{
   "LocationId@xdata.ref": "Location(2)"
}

More information here: TMS Aurelius CRUD Endpoints | TMS XData documentation

HTH,

1 Like
6

You can send associated objects with PATCH: patch is not applied at the other levels - #5 by Studart_Francy