How to retrieve related record details in GET REST Method?

Hi everyone.

I am making the following query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
{
    "filter": [
        {
            "$and": [
                {
                    "send_to_dimensions_c" : 1
                },
                {
                    "a_project_code" : {"$is_null": "a_project_code"}
                }
            ]
            
        }
    ],
    "fields": [
        "id",
        "name",
        "database_c",
        "team_id",
        "account_id",
        "account_name",
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

{
    "filter": [
        {
            "$and": [
                {
                    "send_to_dimensions_c" : 1
                },
                {
                    "a_project_code" : {"$is_null": "a_project_code"}
                }
            ]
            
        }
    ],
    "fields": [
        "id",
        "name",
        "database_c",
        "team_id",
        "account_id",
        "account_name",
        "amount",
        "opportunity_type",
        "sales_stage",
        {"name":"accounts","fields":["id","name","database_c"],"order_by":"date_closed:desc"}
    ],
    "max_num":"100",
    "offset": "0"
}

Against the URL:

URL/.../filter

I want the grab the field "database_c" from the linked account (Many to One relationship so only one), but whenever I query like the above it just returns:

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
"accounts": {
                "name": "Supplier Test 2",
                "id": "8199432e-4ed6-11ed-9c81-00163ef40625",
                "_acl": {
                    "fields": {
                        "business_center_name": {
                            "create": "no",
                            "write": "no",
                            "license": "no"
                        },
                        "business_center_id": {
                            "create": "no",
                            "write": "no",
                            "license": "no"
                        }
                    },
                    "_hash": "e376f7492abb281a71d6a0860441877a"
                }
            },
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

"accounts": {
                "name": "Supplier Test 2",
                "id": "8199432e-4ed6-11ed-9c81-00163ef40625",
                "_acl": {
                    "fields": {
                        "business_center_name": {
                            "create": "no",
                            "write": "no",
                            "license": "no"
                        },
                        "business_center_id": {
                            "create": "no",
                            "write": "no",
                            "license": "no"
                        }
                    },
                    "_hash": "e376f7492abb281a71d6a0860441877a"
                }
            },

But doesn't add the field I've referenced. Has anyone been able to do this? I'd rather not do a second call to the account ID to get the data if possible.

Thanks,

Daniel

0 John Hawkins over 2 years ago

Daniel Hill

It is a while since I did any calls that returned specific related fields but I believe (and so does the official documentation) that it is possible to do what you are attempting.

However, in your snippet you seem to have done a direct copy-and-paste from the docs and simply changed the field names required. You have left in the "order_by" from the original that references a field "date_closed" that is not on Accounts. I think the original was an example from Opportunities.

Can you take out the "order_by" part (or change it to a field that is on the Account object) and see what that returns?

Thanks,

JH.
0 Daniel Hill over 2 years ago in reply to John Hawkins

Hi John. Thanks for the response, updating it to be:

    "fields": [

        "id",

        "name",

        "send_to_dimensions_c",

        "dimensions_database_c",

        "team_id",

        "account_id",

        "account_name",

        "amount",

        "opportunity_type",

        "sales_stage",

        {"name":"accounts","fields":["id","name","phone_office"]}

    ],

Still returns the same :(
0 Robert Thomas over 2 years ago in reply to Daniel Hill

Daniel,

You removed the "database_c" field from your field list, isn't that the field you wanted?
0 John Hawkins over 2 years ago in reply to Daniel Hill

Daniel,

I see what I have missed here. You are asking for it to return a collection list (hence the "order_by" in the docs example) but "accounts" is not a collection on the Opportunity record. It is a single record. "account_id" and "account_name" are fields (albeit pseudo-fields) in the Opportunity vardefs. You have these referenced in the fields list above.

In the original docs example, it was calling Accounts and then asking for linked "opportunities" and giving a field list. "opportunities" is a collection on Accounts - it is a One-to-Many from Accounts to Opportunities.

What you need to do is either make a subsequent call to the Account having retrieved the account_id value or, if you really want to be able to do it in one go, add a new vardef specifying the field(s) you will want into the Opportunities vardefs. You need to add in your "database_c" field in the same way as the "account_name" is added in the core. Of course you need to do this in the Extensions vardefs and not in the core but you should be able to see how it is put there.

All-in-all it is probably far less work, and much more future proof, to simply add a further call to get the Account fields you want having got the id from the first call.

Thanks,

JH.