Please help to explain the table naming in the JSON SQL syntax.

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • stevengpn
    Junior Member
    • Nov 2018
    • 18

    Please help to explain the table naming in the JSON SQL syntax.

    Originally posted by tanya
    I show you the similar usage in EspoCRM but with Accounts and Contacts.
    but here you can set the role (title) of contact on Contact view in Accounts field, but in Account you can see the panel of related contacts with roles.

    Here you can set the role on Property view, but need this field on Contact view in Properties relationship table. That's why I ask you to check, how does implemented in Account and Contact enittyDefs.
    https://github.com/espocrm/espocrm/b...s/Contact.json
    https://github.com/espocrm/espocrm/b...ccount-role.js

    In property layouts create listForContact.json, in clientDefs relate this entity to the panel
    https://github.com/espocrm/espocrm/b...orAccount.json
    https://github.com/espocrm/espocrm/b...count.json#L50
    Tanya,
    I am following this example above and created the middle table "account_task".
    I keep getting error messages in log for what I added in Task.json in order to retrieve the value from the middle table:"account_task.attend", even though I managed to retrieve the value.

    --------Task.json (Task-Account many-many relation that I refrenced from account-contact relation)------
    "tradeshowRole": { "type": "varchar", "maxLength": 100, "notStorable": true, "select": "account_task.attend", "where": { "LIKE": { "leftJoins": ["accounts"], "sql": "accountsMiddle.attend LIKE {value}", "distinct": true }, "=": { "leftJoins": ["accounts"], "sql": "accountsMiddle.attend = {value}", "distinct": true }, ....
    ------------------above produces error below----------------
    [2019-02-11 16:48:24] Espo.ERROR: API [GET]:/:controller/action/:action, Params:Array ( [controller] => Activities [action] => listUpcoming ) , InputData: - SQLSTATE[42S22]: Column not found: 1054 Unknown column 'account_task.attend' in 'field list' [] []
    [2019-02-11 16:48:24] Espo.ERROR: Display Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'account_task.attend' in 'field list', Code: 42S22 URL: /api/v1/Activities/action/listUpcoming?entityTypeList%5B%5D=Meeting&entityTy peList%5B%5D=Call&entityTypeList%5B%5D=Task&future Days=3&maxSize=10&offset=0&sortBy=id&asc=false
    --------------------------------------------------------------------------

    Same error if I use " accountTask "

    What table name should I use?

    Thanks!
  • stevengpn
    Junior Member
    • Nov 2018
    • 18

    #2
    Would you please help. thanks again

    Comment

    Working...