Dataverse API Patch Lookup Column with Postman

[dfricker]

We are working on building out our integrations with dataverse and are having difficulty determining the syntax for updating a lookup column on another table, with an alternate key. We are loading our user information into the contact table and have a lookup to our department table (cr9cd_tu_department), which also has an alternate key on it (cr9cd_departmentid). I have read through other posts and tried countless combinations, but I am still stuck. Should the bind be to the lookup table? I have tried this with both the alternate key and the GUID. 

    {
        "cr9cd_dept_it_lookup_cr9cd_tu_department@OData.bind":"/cr9cd_tu_departments(cr9cd_departmentid = '19410')"
        }

    {
        "cr9cd_dept_it_lookup_cr9cd_tu_department@OData.bind":"/cr9cd_tu_departments(93af7394-05d8-ee11-9079-6045bd04ad21)"
        }

When I perform a GET on a successful row, I also see that the field is formatted as _cr9cd_tu_deptid_lookup_value with the GUID being in there, instead of our alternate key. If I try below, I get the error: 

"CRM do not support direct update of Entity Reference properties, Use Navigation properties instead."

    {
        "_cr9cd_tu_deptid_lookup_value":"93af7394-05d8-ee11-9079-6045bd04ad21"
        }​

[GuidoPreite · Accepted solution]

Hi,

you can use my tool Dataverse REST Builder to create the correct PATCH request, after you can export from my tool to Postman.

My tool only has the GUID reference to update a lookup, however you can also create a "Retrieve Single" request to get the record and copy that syntax to the lookup and see if it works

[dfricker]

This was super helpful, I was able to get this to patch properly. For any users that still need help with this, I found the following syntax worked:

{"[SCHEMA NAME OF SOURCE FIELD]@odata.bind": "/[PLURAL ENTITY NAME OF TARGET FIELD]([LOGICAL FIELD NAME] = 'VALUE')"}

[GuidoPreite]

Hi @dfricker, is not the schema name of source field, is (as the error told you before) the navigation property. That is stored inside the metadata (and my tool uses it), it can be the same as the schema name but not always.