Convert linked field type

[minbar]

Hi guys,

I have created a new entity called Assets then created a One-to-Many linked field with the Cases entity so that I can record a customers asset with a case, this works great but since having started using this feature I have realised that it would be better to be able to link multiple assets to a case. I'm assuming that for this to work the link relationship needs to be a Many-to-Many so does anyone know of a way where I can convert my existing link or remove and recreate it so that it will still display the data already entered? I have tried removing the One-to-Many link and creating a Many-to-Many with the same name in the hope that as the names are the same it would pull the existing data from the database and allow the addition of multiple assets to a case going forward. That didn't work unfortunately.

So if anyone has any ideas how I can do this it would be very much appreciated.

[tanya]

If I understand you well, you can run sql

INSERT INTO `asset_case`(`asset_id`, `case_id`) SELECT `asset_id`, `id` FROM `case` WHERE `deleted`=0 AND `asset_id` IS NOT NULL

It will work, if asset_case hasn't duplicates in rows, which a query wants to add

[minbar]

Hi tanya, thats brilliant it worked perfectly! I just needed to modify the SQL slightly as my table names are slightly different thats all.

Thank you very much!