Virtual Fields

[iconberg]

Hello,

i would find it useful if we could have virtual fields were we can put a formula in it.
For example if i have an entity A, that can linked to entitiy B or C, and want to display a field from B or C.

I know this can be workaround with entity formula and an extra field when entity A (B, C) is saved, but this feels weird.

[tanya]

for using a field of related records you can create foreign field in Entity Manager.
Also you can create notStorable fields with calculation manually in entityDefs

https://forum.espocrm.com/forum/general/34663-report-filter-for-comparing-values-of-2-float-fields

[iconberg]

Can you help me with the notStorable field?
How looks the formula for this: "select": "<select field A if not empty else select field B>".

[tanya]

https://stackoverflow.com/questions/3215454/mysql-ifnull-else

[iconberg]

Thanks, for the answer, didnt know that we can use all php functions there.

[iconberg]

for using a field of related records you can create foreign field in Entity Manager.
Also you can create notStorable fields with calculation manually in entityDefs
https://forum.espocrm.com/forum/gene...2-float-fields

Hi

It does not work for type "link", no error message in espo-log, but also the displayed field does not contains anything.

"
"visitAt": {
"type": "link",
"readOnly": true,
"notStorable": true,
"select": "COALESCE(Besuchsbericht.accountName, Besuchsbericht.firma)"
},
"

Both fields have the same "type" of "link", but i just got an emtpy field back.
Any hint?

I tried such a field for two fields of type "varchar" and it works.

[tanya]

"type": "link"
link field type requires values for {fieldName}Name and {fieldName}Id fields

but weird, that it is worked.
do you really have the table Besuchsbericht with columns accountName and firma?

[iconberg]

Hi Tanja,

i checked it again,
yes, for type "varchar" it works, but the COALESCE dont work correct and delivers everytime the 2nd value. Changed it to IF,
for example "select": "IF(b = '', a, b)"
and it works correct. But changing a or b on detail view and returning to list view dont refresh the result. A reload is needed. But thats ok.

Unfortanly for links i cant get this working, i tried to change from "link" to "belongTo", but no success. Here are the fields definitions, maybe you see a thing:

Yes, the fields accountName and firma are there.
Besuchsbericht.json:

{
    "fields": {
        "name": {
            "type": "varchar",
            "required": false,
            "trim": true,
            "audited": false,
            "readOnly": false,
            "tooltip": true,
            "tooltipText": "Wird beim freilassen automatisch vergeben."
        },
        "description": {
            "type": "text"
        },
        "besuchter": {
             "type": "belongsTo",
             "readOnly": true,
             "notStorable": true,
             "select": "IF(firma = '', accountName, firma)"
        },
        "createdAt": {
            "type": "datetime",
            "readOnly": true
        },
        "modifiedAt": {
            "type": "datetime",
            "readOnly": true
        },
        "createdBy": {
            "type": "link",
            "readOnly": true,
            "view": "views/fields/user"
        },
        "modifiedBy": {
            "type": "link",
            "readOnly": true,
            "view": "views/fields/user"
        },
        "assignedUser": {
            "type": "link",
            "required": true,
            "view": "views/fields/assigned-user"
        },
        "teams": {
            "type": "linkMultiple",
            "view": "views/fields/teams"
        },
        "firma": {
            "type": "link",
            "required": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false
        },
        "besuchtam": {
            "notNull": false,
            "type": "date",
            "required": true,
            "default": "javascript: return this.dateTime.getToday();",
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "gespraechspartner": {
            "type": "varchar",
            "required": false,
            "trim": true,
            "maxLength": 50,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "akoSM": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "akounico": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "verkaufZweitplazierung": {
            "type": "enum",
            "required": false,
            "options": [
                "",
                "Ja",
                "Nein",
                "Angebot erstellen"
            ],
            "isSorted": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "abverkaufsartikelAngeboten": {
            "type": "enum",
            "required": false,
            "options": [
                "",
                "Ja, mit Erfolg",
                "Ja, ohne Erfolg"
            ],
            "default": "",
            "isSorted": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true,
            "tooltipText": ""
        },
        "abverkaufsartikelbegrndung": {
            "type": "varchar",
            "required": false,
            "trim": true,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "dateianlage": {
            "type": "file",
            "required": false,
            "sourceList": [],
            "audited": false,
            "tooltip": false,
            "isCustom": true
        },
        "teASTRA": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "teSW": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "teJoop": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "tmASTRA": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "tmSW": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "tmJoop": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "bmSW": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "nfASTRA": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "tuSW": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "neuheitenASTRA": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "neuheitenSW": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "neuheitenJoop": {
            "notNull": true,
            "type": "bool",
            "default": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "accountName": {
            "type": "link"
        }
    },
    "links": {
        "createdBy": {
            "type": "belongsTo",
            "entity": "User"
        },
        "modifiedBy": {
            "type": "belongsTo",
            "entity": "User"
        },
        "assignedUser": {
            "type": "belongsTo",
            "entity": "User"
        },
        "teams": {
            "type": "hasMany",
            "entity": "Team",
            "relationName": "EntityTeam",
            "layoutRelationshipsDisabled": true
        },
        "firma": {
            "type": "belongsTo",
            "foreign": "besuchsbericht",
            "entity": "Account",
            "audited": false,
            "isCustom": true
        },
        "dateianlage": {
            "type": "belongsTo",
            "entity": "Attachment",
            "skipOrmDefs": true
        },
        "accountName": {
            "type": "belongsTo",
            "foreign": "besuchsberichts",
            "entity": "Lead",
            "audited": false,
            "isCustom": true
        }
    },
    "collection": {
        "sortBy": "createdAt",
        "asc": false,
        "textFilterFields": [
            "name"
        ],
        "fullTextSearch": false
    },
    "indexes": {
        "name": {
            "columns": [
                "name",
                "deleted"
            ]
        },
        "assignedUser": {
            "columns": [
                "assignedUserId",
                "deleted"
            ]
        }
    }
}