Unable to Create / Update Concatenated Field in Table through Form!

[vladt]

Hello Support,

I’ve been struggling to create / update a concatenated field (named “DealName”) in the table of my database from the form (named “Deals”) based on selections on other fields on the same form. (Database table structure image and the “Deals” form’s fields image are attached.)

In the table “Deals”, the field “DealName” holds concatenated values of 3 fields from 3 related tables: “Deals”, “Lenders”, and “Clients”. (see the database structure image attached).
I need the “DealName” field of the “Deals” table to update automatically whenever any of the values in the 3 related fields are updated.
The fields in the 3 related tables are:
“DealDate” in “Deals” table
“LenderName” in “Lenders” table
“ClilentCompanyName” in “Clients” table.

I used the following Function (named “ConcatenateDealName”) attached to the Do Complete event of the “Deals” form:

function ConcatenateDealName(five, context, result) {
const dealDate = five.field.Deals.DealDate;
const lenderName = five.field.Lenders.LenderName;
const clientCompanyName = five.field.Clients.ClientCompanyName;

five.field.Deals.DealName = (dealDate + "-" + lenderName + "-" + clientCompanyName);
return five.success(result);

}

When I try to save the form by clicking the ticker icon, I get the following error message:

Error : ErrScriptError

Message : TypeError: Cannot read property ‘Deals’ of null > ConcatenateDealName.fs.js:3:33

System Error : TypeError: Cannot read property ‘Deals’ of null

Trace : TypeError: Cannot read property ‘Deals’ of null at ConcatenateDealName (ConcatenateDealName.fs.js:3:33)

Query: mutation ProcessTransactions($selectionKey100: String!, $actionKey100: String!, $parentSelectionKey100: String, $keys100: [String]!, $versionKey100: String!, $fieldUpgradeData100: String, $fieldNames100: [String], $values100: [String]) { update100:dbapiFormDataSave( selectionKey: $selectionKey100, actionKey: $actionKey100, parentSelectionKey: $parentSelectionKey100, keys: $keys100, versionKey: $versionKey100, fieldUpgradeData: $fieldUpgradeData100, fieldNames: $fieldNames100, values: $values100) { entityUpdate{ keys versionKey versionsExist permissions values { name value type } } } }

Variables: {“selectionKey100”:“ed77d8e5-8fae-4903-895b-1205a70cb869”,“actionKey100”:“add80175-8752-4f67-8d21-eecd8221c677”,“parentSelectionKey100”:“000000-0000-0000-000000”,“keys100”:[“2ae3a1ff-cf44-4036-ad85-6222f3a78b3f”],“versionKey100”:“5384378d-4b41-11ef-85ca-509a4c524f9a”,“fieldNames100”:[“DealName”,“DealDate”,“DealAmount”,“DealCommissionRate”,“DealCommissionAmount”,“LendersKey”,“ClientsKey”,“DealStatusKey”],“values100”:[“2024-01-22-Rapid Finance-Stellar Dynamics”,“2024-01-22”,“970000”,20,“194000”,“196827be-0ffb-4873-9865-684bf7c1b335”,“5f4cc96b-1aee-4c4d-bb97-7a1213cd1870”,“b3091ed9-5994-4452-b531-21f9ba124056”],“fieldUpgradeData100”:“”}

Please HELP to solve this issue!

THANK YOU!

Vlad

[Pranoy]

Hi @vladt,

Your function looks right except for just one part. You don’t need to add the table name when accessing fields through five. For example, in your function you are accessing the DealDate field via:

const dealDate = five.field.Deals.DealDate;

This becomes:

const dealDate = five.field.DealDate;

So your new function will look something like:

function ConcatenateDealName(five, context, result) {
    const dealDate = five.field.DealDate;
    const lenderName = five.field.LenderName;
    const clientCompanyName = five.field.ClientCompanyName;
    
    five.field.DealName = (dealDate + "  -" + lenderName + "-" + clientCompanyName);
    return five.success(result);
}

Secondly: Instead of attaching the function on the DoComplete event, since we are making a concatenated field, let us put the function on the field level, not the form level.

1. Start by navigating to the ‘Deals’ form by clicking on ‘Visual’ → ‘Forms’

2. Click on the ‘Deals’ form to reveal its details on the right and now navigate to the ‘Pages’ tab and click on ‘General’
   

   

3. Once inside the general page click on the ‘Fields’ tab to reveal all the fields in your form. Here we can attach the ConcatenateDealName function on each of the 3 fields onExit event such that whenever the values inside these fields are updated the function gets triggered and populates the data on the DealName field.
   

   

4. For example, you can start by clicking on any of the 3 fields (let’s say Lender Name) then click on the events tab
   

   
   
   Here you will see the event ‘onExit’. Attach your function on this event.
   
   

Now just simply do the last 2 steps (step 3 and 4) for all 3 fields such that whenever the value of them are updated we trigger the function and populate the DealName field.

Let me know if this works or if you have any other questions