Fetching a module's related Account field's Phone Number as the value for a field.

[axyl]

Based on this example of fetching a related entity's field, to populate a module's field. (https://forum.espocrm.com/forum/gene...2296#post62296)...

In the entityDef for my module, I'd like to show the related Account's phone number. But since the phone number is stored in a separate table to the Account, in the DB, I'm not exactly sure on how to structure the JSON/SQL?

A field definition like this works fine...

[COLOR=#9cdcfe]"accountName"[/COLOR][COLOR=#d4d4d4]: {[/COLOR]
[COLOR=#d4d4d4]            [/COLOR][COLOR=#9cdcfe]"type"[/COLOR][COLOR=#d4d4d4]:[/COLOR][COLOR=#ce9178]"varchar"[/COLOR][COLOR=#d4d4d4],[/COLOR]
[COLOR=#d4d4d4]            [/COLOR][COLOR=#9cdcfe]"notStorable"[/COLOR][COLOR=#d4d4d4]: [/COLOR][COLOR=#569cd6]true[/COLOR][COLOR=#d4d4d4],[/COLOR]
[COLOR=#d4d4d4]            [/COLOR][COLOR=#9cdcfe]"directUpdateDisabled"[/COLOR][COLOR=#d4d4d4]: [/COLOR][COLOR=#569cd6]true[/COLOR][COLOR=#d4d4d4],[/COLOR]
[COLOR=#d4d4d4]            [/COLOR][COLOR=#9cdcfe]"select"[/COLOR][COLOR=#d4d4d4]: {[/COLOR]
[COLOR=#d4d4d4]                [/COLOR][COLOR=#9cdcfe]"sql"[/COLOR][COLOR=#d4d4d4]:[/COLOR][COLOR=#ce9178]"linkedAccount.name"[/COLOR][COLOR=#d4d4d4],[/COLOR]
[COLOR=#d4d4d4]                [/COLOR][COLOR=#9cdcfe]"leftJoins"[/COLOR][COLOR=#d4d4d4]: [[/COLOR]
[COLOR=#d4d4d4]                    [[/COLOR]
[COLOR=#d4d4d4]                        [/COLOR][COLOR=#ce9178]"Account"[/COLOR][COLOR=#d4d4d4],[/COLOR]
[COLOR=#d4d4d4]                        [/COLOR][COLOR=#ce9178]"linkedAccount"[/COLOR][COLOR=#d4d4d4],[/COLOR]
[COLOR=#d4d4d4]                        {[/COLOR]
[COLOR=#d4d4d4]                            [/COLOR][COLOR=#9cdcfe]"opportunity.accountId:"[/COLOR][COLOR=#d4d4d4]:[/COLOR][COLOR=#ce9178]"linkedAccount.id"[/COLOR][COLOR=#d4d4d4],[/COLOR]
[COLOR=#d4d4d4]                            [/COLOR][COLOR=#9cdcfe]"linkedAccount.deleted"[/COLOR][COLOR=#d4d4d4]: [/COLOR][COLOR=#569cd6]false[/COLOR]
[COLOR=#d4d4d4]                        }[/COLOR]
[COLOR=#d4d4d4]                    ][/COLOR]
[COLOR=#d4d4d4]                ][/COLOR]

[COLOR=#d4d4d4]            }[/COLOR]

[COLOR=#d4d4d4]        },[/COLOR]

But I'm not clear on how I can reach the account's phone number, using the SQL commands.

Anyone have any hints or examples of having done this?

I don't want to use formulas as I'd like the information to be live.

Thanks.

[axyl]

Actually, I worked it out.

"accountPhone": {
    "type": "varchar",
    "notStorable": true,
    "directUpdateDisabled": true,
    "select": {
        "sql": "pn.name",
        "leftJoins": [
            [
                "Account",
                "accountLink",
                {
                    "opportunity.accountId:": "accountLink.id",
                    "accountLink.deleted": false
                }
            ],
            [
                "EntityPhoneNumber",
                "epn",
                {
                    "accountLink.id:": "epn.entity_id",
                    "epn.primary": true
                }
            ],
            [
                "PhoneNumber",
                "pn",
                {
                    "epn.phone_number_id:": "pn.id"
                }
            ]
        ]
    },
    "options": []
}