Updates for lookup columns?

[soaway]

Is it possible to update a lookup column?

Tried this:
UPDATE Tablename
set lookupColumn = 'GUID value from Lookup table'

Says "Updated" but it is not saved.
Sorry, I'm very new to SQL4Cds...
Victor

[MarkMpn]

Hi Victor, that certainly should work. I've just tested this:

UPDATE account
SET    primarycontactid = '3c2580da-7ccc-e911-a813-000d3a7ed5a2'
WHERE  accountid = 'ae0bdb02-1bac-eb11-8236-000d3ad5c780'
       AND primarycontactid IS NULL;

and I got the message

1 Account updated

I could then run:

SELECT primarycontactid
FROM   account
WHERE  accountid = 'ae0bdb02-1bac-eb11-8236-000d3ad5c780'

and see the expected new primarycontactid value.

When you get the message saying "Updated", does it show the expected number of records that have been updated?

Some lookup columns also require some more information to indicate the type of record you're linking to. For example Customer columns that can be account or contact records, or Owner columns that could be systemuser or team records. To update those columns you also need to set the corresponding ___type column, e.g.

UPDATE contact
SET    ownerid     = 'b7e68613-74b8-4c05-9ff4-8c3c418feb1a',
       owneridtype = 'systemuser'
WHERE  contactid = '3c2580da-7ccc-e911-a813-000d3a7ed5a2'

[soaway]

Hi Mark, thanks for checking.

I'm updating custom entity with lookup value also from custom entity.

update soaway_product set soaway_SupplierLU = '99c4d6bd-1b77-ec11-8942-000d3a840a7', soaway_us_des = '1234' where soaway_Name = 'Ali001'

I'm updating a lookup column and string column. I've tried to include both columns and leaving only one. In every case I see "1 Product updated". If string column included it is updated, lookup column never updated. If both included only string updated. There are no any errors or warnings. I can update the lookup column via UI.

[MarkMpn]

Can I check a couple of details please?

1. What version number of SQL 4 CDS are you using?
2. Are you running it in XrmToolBox or SSMS?
3. If you're using it in XrmToolBox, in the SQL 4 CDS Settings dialog do you have the "Use TDS Endpoint where possible" option ticked?

[soaway]

What version number of SQL 4 CDS are you using?
VS> 5.4.0

Are you running it in XrmToolBox or SSMS?

XMLToolbox

If you're using it in XrmToolBox, in the SQL 4 CDS Settings dialog do you have the "Use TDS Endpoint where possible" option ticked?
VS> No

[MarkMpn]

I've still been unable to replicate this - I've created my own custom entities and I can update the lookup columns for the relationships between them using this same syntax.

Is it possible there's any sort of customization (plugins, workflows, Power Automate etc.) that could be interfering with the update or undoing it afterwards?

If all that looks OK, are you able to send me your solution so I can test this in my environment?

[soaway]

Thank you Mark for following up. It's a plain setup with few custom entities with relationships. Where can I send a link with the solution?

[MarkMpn]

You can email it to sql4cds@markcarrington.dev