Formula Field doesn't show reference table attribute

[imantung]

* Describe the bug

Formula Field doesn’t show reference table attribute

* Environment

• NocoBase version: 1.6.1

• Database type and version: PostgreSQL 16.8

• OS: MacOS

• Deployment Methods: Create-nocobase-app

• Docker image version:

• NodeJS version: v18.20.6

* How To Reproduce

1. Create General Collection Some Reference Table that contains Some Modifier (Field Interface: Number)
2. Create General Collection Some Table that contains Some Number (Field Interface: Number) and Reference Table (Type: many to one, refer to Some Reference Table table)
3. Create new formula field to multiply Some Number with Some Modifier, the expression didn’t show Reference Table field

Expected behavior

Reference Table field shown so we can select Some Modifier

Screenshots

Logs

[moicestjj]

Hey there

I’m having exactly the same situation where basically I want to create a formula field within one of my data source. I want the formula to basically use one association field within the data source, but it seems that Nocobase does not support currently.

I would appreciate if anyone has some info about this.

Thanks!

[support]

For performance reasons, formula fields currently don’t support relationship fields and only support fields within the same table.

[imantung]

Is there any workaround for performing calculations using a field from the reference table? I attempted to duplicate the field and set its value using ‘Set Default Value’ in the form, but in some cases, we cannot display it to the user.

[support]

You can leverage the workflow feature for calculations on the backend.

[moicestjj]

Thank you very much for your answer.
Do you think that this feature could be added or is it something that would affecting the performances anyway?
Thanks again

[support]

There are potential performance risks and some hard-to-solve problems. For example, when relational data changes (even in batches), when should the values of formula fields that reference it be updated.

[moicestjj]

Thank you for your answer.

I can see the challenges that it would be indeed.

Thanks!

[Hafiz]

Hi

Do you have tutorial on how to do it in workflow?

Ive been trying since morning, but doesn’t work at all

[lukaskotik]

I have exactly same issue. I have one table with users. It has one to many relationship with another table, call it e.g. “products table”. I have form that fills the products table. In this form in record picker I choose associated user and I would like to make dynamic calculations using both the fields from form and the values from associated table with users.

There is one workaround for forms, that in some sort opposing the point of relational databases “no need to store the same information multiple times in multiple tables”:

1. In the form, where you have field with formula, create another field that “mimics” (same type) the field from the associated table. It is only auxiliary field.
2. Add this auxiliary field to the form.
3. Add field linkage rule for this auxiliary field such that it sets in properties: axiliary field → value → expression → choose the associated field (you can even make some simple calculation here using formula.js). The condition could be that the associated field is not empty.
4. Add another linkage rule that set this auxiliary field as “hidden (reserved value)”. You can leave condition empty - it will be applied all the time.
5. Set your formula using this auxiliary field.
6. If you need to update formula field after the data form original field were changed, make workflow with “collection event” that triggers if original field is changed and that makes the calculation you want.

Optimal solution? No. Does it have higher negative impact on performance than native solution? Yes. But it works. One of the case where no code platform can cost you more energy than low code platform or “normal” platform.