Updat a record using nested alternate keys

[SELB]

Description:

Entity Account Alternate Key has two parameters:

1. 'fullname' Type String
2. 'primarycontact' type lookup column from the Contact entity

Entity Contact alternate key has three parameters:

1. keyfield1 type string
2. keyfield2 type string
3. keyfield3 Type Boolean

when I want to update a specific record from the Contact entity:

PATCH: weburl/contacts(keyfeld1 = ‘ABCD’, keyfeld2 = ‘EFGH’, keyfeld3 = true)

This works perfectly

But when I want to update a record from accounts I have a problem I tried the following:

PATCH: weburl/accounts(fullname=’John Kennedy’, _primarycontact_value = ‘/contacts(keyfeld1 = ‘ABCD’, keyfeld2 = ‘EFGH’, keyfeld3 = true)’)

I get a syntax error. Maby havd anyone an Idea with this retrieval.

Note: I don’t want to use the GUID.

PATCH: weburl/accounts(fullname=’John Kennedy’, _primarycontact_value ='GUID')

I Know that is works.

Thanks

[ivan_apps]

I don’t think you can nest it that way. I would imagine the Odata query is still thinking anything inside the single quotes after _primarycontact_value is a string, not a subquery. This would throw a syntax error.

Not sure of the context of where you are firing the request from, but I would simply execute a retrieve with your alternate keys first, then grab the GUID after and enter it in your second query. I know you said you don’t want to use the GUID, but some context as to “why” would help frame the need as these retrievals are pretty easy and quick using JavaScript, Power Automate, or C#.

You can also play around with Dataverse Rest Builder tool in XrmToolbox. It’ll generate the proper queries for you so you don’t get a syntax error.

[GuidoPreite]

Will be useful to know in which language you are doing these requests, but let's consider you are doing plain REST requests (with Postman for example), the PATCH you are doing is not correct.

PATCH is a POST expecting the fields to be inside the body and for the specific field you mentioned, the correct way to set it is:

primarycontactid@odata.bind  

I didn't test with alternate keys (if I have time I can test it next days) but sure is not _primarycontact_value, first because the syntax _lookupfield_value is used for retrieval, and second _primarycontact_value is not correct (at least should be _primarycontactid_value. I know you may wrote by hand and not copied it, but if you post code that doesn't work, that should be precise as possible (paying attention to hide things like prefix for example).

please try again  with a correct PATCH as POST and the correct field name

[SELB]

Hello Ivan and Guido,
First of all, thank you for the feedbacks.
I plan to pull the data from an external system into Dataverse, update it and delete it if necessary. I use Postman Rest HTTP calls. I'm not particularly interested in the Account and Contact entities, but in general about Header and Lines tables. If the Lines alternative key is based on two fields: name + Header_name(Lookup).
However, I don't know how to update child table (Line) because key has a lookup field. According to MS documentation: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/use-alternate-key-reference-rec...
I need to use “_lookupfield_value”, but in the example given I should enter the GUID of the header as the value.
My question is very simple:
How can I update the lines only with an alternative key without a GUID?

Hier is an Example:

Thanks

[GuidoPreite · Accepted solution]

your screenshot is clear, your initial message (due to the use of account and contact was confusing).

I honestly don't know if alternate keys of lookup are supported when the lookup is part of the main alternate key.

According to docs you linked they are not, but worth a try (with the right retrieval syntax, you can use my tool Dataverse REST Builder to get the retrieve, it also can export to Postman collection).

However I believe (but didn't test) that the update works with the alternate key, so in your case is better to define a different alternate key for the lines that don't involve a lookup but you should be able to set the lookup to define the parent-child relationship with the alternate key.

[SELB]

Hi Guido,

I tried with a second key and @OData.bind. Unfortunately that didn't quite work.
I just took position in the second key. When I update the record it works but I can no longer create new records that have the same value in Position apart from which HeaderID it belongs to. In my case, duplicate data record means (same position AND same header ID).
I think I need to add a new field that takes over the value of the header ID immediately when the data record is created. I have to use this new field to replace the lookup field in the alternate key.

Thanks

[SELB · Accepted solution]

Hi, 

As discussed, I created three new fields in the entity lines corresponding to the three fields in the alternative key of the header. Additionally, I created a process flow that fills in these fields from the header table when a data record is created or updated.

In the Lines entity I created a second alternate keys. one for avoid creating duplicates and the second to
update the lines.

Thanks.