Perform computed value based on other fields

License: GNU General Public License v3.0

TypeScript 94.32% Vue 5.45% JavaScript 0.24%

directus directus-extension directus-extension-interface

directus-extension-computed-interface's Introduction

Introduction

A Directus extension for automatically calculating the value of a field based on other fields of the same item, on the client side.

Support templating, arithmetic operations. Concat strings, sum, subtract, multiply, modulo, convert to slug, currency, etc.
Can be used as an alias field.
Calculation is performed on the client side, so it would not work if the item is created/updated via direct API calls or hooks.
Lightweight. No third-party libraries.

Installation

npm i directus-extension-computed-interface

Get Started

Go to Settings, create a new field with type string or number.
In the Interface panel, choose Computed interface. There are 8 options:
1. Template: Similar to M2M interface, determine how the field is calculated. Learn more about syntax in the next section.
2. Field Mode: Choose how the value is displayed.
  - null: Default option. Show an input with the computed value but still allow manual editing.
  - Display Only: Show the computed value but will not save it to the database. Usually used for alias fields.
  - Read Only: Show an input with the computed value and disallow manual editing.
3. Prefix: a string to prefix the computed value.
4. Suffix: a string to suffix the computed value.
5. Custom CSS: a JSON object for inline style binding. Only works with Display Only and Read Only mode. You can use this option to customize the appearance of the computed value such as font size, color, etc. Example: {"color": "red", "font-size": "20px"}.
6. Debug Mode: Used for debugging the template. It will show an error message if the template is invalid. It will also log to console the result of each component of the template.
7. Compute If Empty: Compute the value if the field is empty. This is useful if you want a value to be computed once such as the created date or a unique ID.
8. Initial Compute: Compute the value when opening the form. This is useful if you want to compute a value based on the current date or other dynamic values.

Syntax

The template consists of 2 elements: plain strings & expressions.

Plain strings are string literal, often used for text interpolation.
Expressions can contains operators, field names, numbers & strings. They must be enclosed by {{ and }}.

Examples

Sum 2 numbers:

{{ SUM(a, b) }}

Multiply 2 numbers:

{{ MULTIPLY(a, b) }}

Convert string to slug:

{{ SLUG(title) }}

Text interpolation:

/{{ SLUG(title) }}-{{ id }}

Complex calculation:

{{ SUM(MULTIPLY(2, x), b) }}

Literal strings are enclosed by double quotes ("):

{{ CONCAT(file, ".txt") }}

Use . to access nested fields in M2O or M2M fields:

{{ CONCAT(CONCAT(user.first_name, " "), user.last_name) }}

Combine AT, FIRST, LAST, JSON_GET to access nested fields in O2M or JSON fields:

{{ JSON_GET(AT(products, 0), "name") }}
{{ JSON_GET(LAST(products), "price") }}

Note: For M2O, O2M, M2M fields, you can only access the fields of the direct relation. For example, if you have a user field that is a M2O relation to the users collection, you can only access the fields of the users collection. You cannot access the fields of the roles collection even though the users collection has a M2O relation to the roles collection. On the other hand, JSON fields have no such limitation!

Available operators

Type conversion

Operator	Description
`INT(a)`	convert to integer
`FLOAT(a)`	convert to float
`STRING(a)`	convert to string
`DATE(a)`	convert to date

Format

Operator	Description
`SLUG(a)`	transform string to slug (e.g. "This is a title" → "this-is-a-title")
`CURRENCY(a)`	format number to currency (e.g. 3000 → "3,000")

Date

Operator	Description
`DATE_ISO(a)`	transform date or date-like object to ISO string
`DATE_UTC(a)`	transform date or date-like object to UTC string
`DATE_STR(a)`	transform date or date-like object to string with format "YYYY-MM-DD"
`TIME_STR(a)`	transform date or date-like object to string with format "HH:mm:ss"
`YEAR(a)`	get year of a date object, similar to `getFullYear`
`MONTH(a)`	get month of a date object, similar to `getMonth`
`GET_DATE(a)`	get date of a date object, similar to `getDate`
`DAY(a)`	get day of a date object, similar to `getDay`
`HOURS(a)`	get hours of a date object, similar to `getHours`
`MINUTES(a)`	get minutes of a date object, similar to `getMinutes`
`SECONDS(a)`	get seconds of a date object, similar to `getSeconds`
`TIME(a)`	get time of a date object, similar to `getTime`
`LOCALE_STR(a, locale, options)`	transform date or date-like object to string with locale format, `options` is a stringified JSON object. Example: `LOCALE_STR("2023-01-01", "en-US", "{\"weekday\": \"long\", \"year\": \"numeric\", \"month\": \"long\", \"day\": \"numeric\"}")` returns "Sunday, January 1, 2023".

Arithmetic

Operator	Description
`ABS(a)`	absolute
`SQRT(a)`	square root
`SUM(a)`	sum an array of numbers
`SUM(a, b)`	a + b
`AVERAGE(a)`	average value of an array of number
`SUBTRACT(a, b)`	a - b
`MULTIPLY(a, b)`	a * b
`DIVIDE(a, b)`	a / b
`REMAINDER(a, b)`	a % b
`CEIL(a)`	returns the smallest integer greater than or equal to `a`.
`FLOOR(a)`	returns the largest integer less than or equal to `a`.
`ROUND(a)`	rounds to the nearest integer.
`ROUND(a, n)`	rounds number `a` to `n` number of decimals, (`ROUND(1.23, 1) = 1.2`).
`MAX(a, b)`	max value between `a` and `b`.
`MAX(arr)`	max value of an array of numbers.
`MIN(a, b)`	min value between `a` and `b`.
`MIN(arr)`	min value of an array of numbers.
`POWER(a, b)`	a^b
`EXP(a)`	returns `e^a`, where `e` is Euler's number.
`LOG(a)`	returns the natural logarithm (base `e`) of `a`.

String

Operator	Description
`STR_LEN(str)`	length of string (deprecated, use `LENGTH` instead)
`LENGTH(str)`	length of string
`FIRST(str)`	first character of string
`LAST(str)`	last character of string
`REVERSE(str)`	reverse string
`LOWER(str)`	to lower case
`UPPER(str)`	to upper case
`TRIM(str)`	removes whitespace at the beginning and end of string.
`CONCAT(strA, strB)`	concat 2 strings `strA` and `strB`.
`LEFT(str, count)`	extract `count` characters from the beginning of the string `str`.
`RIGHT(str, count)`	extract `count` characters from the end of the string `str`.
`MID(str, startAt, count)`	extract `count` characters from `startAt` position of the string `str`.
`ENCODE_URL_COMPONENT(str)`	encode string to URL component.
`REPT(str, count)`	repeat string `count` times.
`JOIN(arr, separator)`	join an array of strings with `separator`.
`SPLIT(str, separator)`	split string `str` by `separator` to an array of strings.
`SEARCH(str, keyword)`	search `keyword` in `str` and return the position of the first occurrence. Return -1 if not found.
`SEARCH(str, keyword, startAt)`	search `keyword` in `str` and return the position of the first occurrence after `startAt`. Return -1 if not found.
`SUBSTITUTE(str, old, new)`	replace all occurrences of `old` in `str` with `new`.
`AT(str, index)`	get character at `index` of `str`.
`INDEX_OF(str, keyword)`	get the position of the first occurrence of `keyword` in `str`. Return -1 if not found.
`INCLUDES(str, keyword)`	check if `str` contains `keyword`.
`SLICE(str, startAt, endAt)`	extract a part of `str` from `startAt` to `endAt`. `endAt` can be negative. Similar to `slice` method of `String`.

Boolean

Operator	Description
`NULL(a)`	check is null
`NOT_NULL(a)`	check is not null
`NOT(a)`	logical NOT
`EQUAL(a, b)`	a = b
`NOT_EQUAL(a, b)`	a <> b
`GT(a, b)`	a > b
`GTE(a, b)`	a >= b
`LT(a, b)`	a < b
`LTE(a, b)`	a <= b
`AND(a, b)`	logical AND
`OR(a, b)`	logical OR

Array

Operator	Description
`ARRAY_LEN(a)`	length of array (deprecated, use `LENGTH` instead)
`LENGTH(a)`	length of array
`FIRST(a)`	first element of array
`LAST(a)`	last element of array
`REVERSE(a)`	reverse array
`CONCAT(a, b)`	concat 2 arrays `a` and `b`.
`AT(a, index)`	get element at `index` of `a`.
`INDEX_OF(a, element)`	get the position of the first occurrence of `element` in `a`. Return -1 if not found.
`INCLUDES(a, element)`	check if `a` contains `element`.
`SLICE(a, startAt, endAt)`	extract a part of `a` from `startAt` to `endAt`. `endAt` can be negative. Similar to `slice` method of `Array`.
`MAP(a, expression)`	apply `expression` to each element of `a` and return a new array, each element of `a` must be an object. Example: `MAP(products, MULTIPLY(price, quantity))` returns an array of total price of each product.
`FILTER(a, expression)`	filter `a` with `expression` and return a new array, each element of `a` must be an object. Example: `FILTER(products, GT(stock, 0))` returns an array of products that are in stock.
`SORT(a, expression)`	sort `a` with `expression` and return a new array, each element of `a` must be an object. Example: `SORT(products, price)` returns an array of products sorted by price.

JSON

Operator	Description
`JSON_GET(a, key)`	get value of `key` in JSON object `a`.
`JSON_PARSE(a)`	parse string `a` to JSON object.
`JSON_STRINGIFY(a)`	stringify JSON object `a`.

Relational

Operator	Description
`ASUM(a, b)`	Aggregated sum of O2M field. For example: calculate shopping cart total price with `ASUM(products, MULTIPLY(price, quantity))`, where `products` is the O2M field in the shopping cart and `price` & `quantity` are 2 fields of `products`.
`AMIN(a, b)`	Aggregated min of O2M field.
`AMAX(a, b)`	Aggregated max of O2M field.
`AAVG(a, b)`	Aggregated average of O2M field.
`AMUL(a, b)`	Aggregated multiplication of O2M field.
`AAND(a, b)`	Aggregated logical AND of O2M field. Only return `true` if all values are `true`.
`AOR(a, b)`	Aggregated logical OR of O2M field. Only return `true` if at least one value is `true`.
`ACOUNT(a, b)`	Aggregated count of O2M field. Only count true values. For example: count the number of products that are in stock with `ACOUNT(products, GT(stock, 0))`, where `stock` is a field of `products`.

Condition

Operator	Description
`IF(A, B, C)`	return `B` if `A` is `true`, otherwise `C`
`IFS(A1, B1, A2, B2, ..., An, Bn)`	return `Bi` if `Ai` is the first to be `true`, if none of `Ai` is `true`, return `null`

Others

Operator	Description
`RANGE(start, end, step)`	create an array of numbers from `start` to `end` with `step` increment/decrement. Example: `RANGE(1, 10, 2)` returns `[1, 3, 5, 7, 9]`.

Dynamic Variables

There are 2 dynamic variables available that you can use in the expressions:

$NOW: return the current Date object. Example: {{ YEAR($NOW) }} returns the current year.
$CURRENT_USER: return the current user's id. Example: {{ EQUAL($CURRENT_USER, user) }} checks if the user field is the current user.

directus-extension-computed-interface's People

Contributors

Stargazers

Watchers

directus-extension-computed-interface's Issues

"compute if empty"

Hello!

I'm using this interface to generate a slug from a title field. I want the slug to be generated only if it hasn't been set yet during record creation. After saving the record, I don't want the slug to update automatically when I modify the title field. However, I still want the ability to manually edit the slug. I tried using the "compute if empty" option, but the slug field still updates whenever I change the title field.

How can I prevent the slug field from automatically updating after the initial save?

Cheers

Kamil

M2M relationship computation

Excellent Extension btw :).
Any plans to implement m2m relationship computation (if this is even possible?). I see m2o is available.
If not I'm willing to help fund this item. Do let me know.

Not showing up

I can't get my production server to show up in the create fields list. It shows as active. 10.9.3

Any thoughts?

Input 'field' attribute is missing

All other directus input fields have a field attribute. Can you please add it for consistency?
For my use case, this is needed for selecting the input and doing further manipulations in another interface.

add relation column id, because used static 'id', but id relation can be other

if (arrayOfIds.length) {
const relatedCollection = isM2O ? relation.related_collection : relation.collection;
const path = relatedCollection === 'directus_users' ? '/users' : items/${relatedCollection};

				if (relatedCollection) {
					let data;
					if (relatedCollection in itemCache && arrayOfIds.every(id => id in itemCache[relatedCollection])) {
						data = arrayOfIds.map(id => itemCache[relatedCollection][id]);
					} else {
						**type FilterType = {
							[key: string]: any; 
						  };
						let filter_: FilterType = {}
						//console.log('relatedCollection', relatedCollection, relation )
						let id_ = relation?.schema?.foreign_key_column? relation.schema.foreign_key_column : 'id'
						filter_[id_] ={ _in: arrayOfIds.join(',') }**
						data = (await api.get(path, {
							params: { filter: filter_ },
						})).data.data;
					}

index.js

Hi,
I'm hosting directus using coolify. Unfortunately, when I install the package and restart, coolify pulls a new image, so the packages are gone.
Would it be possible to provide the index.js files that I can put in the folders?
Some extensions provide it and I've installed them successfully by putting the files in the folders. I don't know how to generate them myself, so if you could provide them it would be great.
Thanks a lot!
Manuel

ASUM and ARRAY_LEN doesn't work if there are more than 22 items

We are getting 'Request failed with status code 500', name: 'AxiosError', code: 'ERR_BAD_RESPONSE'" when there are more than 22 items for calculation using operators ASUM and ARRAY_LEN.

Possible issues:
directus/directus#16659
directus/directus#16745

Sum Values is not working

Hi team,

I'm using Directus v10.4.2 with the version extension 1.8.0

I don't know if I'm doing something wrong, but I can't do SUM

This is the template I'm doing {{ SUM(alimentacao, outro) }}

When I enter the values, the computed field concat the numbers instead sum.

I'm doing something wrong?

Thank you

Decimal parseInt

Hello, when selecting a decimal value it is always converted to an integer value
I find this in interface.vue

if (['integer', 'decimal', 'bigInteger'].includes(props.type)) {
return parseInt(res) || 0;
}
if (['float'].includes(props.type)) {
return parseFloat(res) || 0;
}

I hope it is helpful, thank you

Is it still a it works?

I have a field o2m, I write in a calculated field ASUM(feedback, rating)
I'm getting 0 all the time. The add-on is no longer being developed, right? And in directus 9 it doesn't work?

Feature request/check

Should computes field be calculated on entry open event ? It will be useful because now it calculate only if something changes.

Also it only calculate result and input to field but after entry save the value not saved to db. Is it expected result ? The value saved only in case field mode not set to Read Only or Display Only

---

Not working in directus 10.3.0

Is it available for directus 10.3.0?

I tested simple formula {{ SUM(1,2) }} and nothing calculated in interface.

Also tested simple {{ currency(1000) }}. Nothing converted.

I use self hosted directus in docker, db is postgresql.

Documentation on field names

The documentation could use more examples and get started & syntax sections. In particular, it would be nice if there was information about how to format field names in the syntax section. It seems that you can look for related fields. but the syntax for nested relations escapes me. I looked at the code and it looks like we're getting the relationship functions from Directus. Do they have any documentation on this we could link out to?

Possible new Relational Operators

We have a case when we need to display MAX and MIN for related table, currently I don't see a way to get it. Possible solution would work same a ASUM, just with MAX and MIN, AMAX and AMIN, and possibly other operators.

How can i save fields from M:N relation?

Hi there,

Thanks for your extension! I'm trying to make a shopping cart inside directus:

How can i capture the selected values from a table relationship? Do you have another idea to do a shopping cart inside directus app?

Date manipulation

Hi,
is it possible to add date manipulation functions like DATEADD (datepart , number , date ) from SQL or DateTime.AddYears(Int32) from C#?
Usecase: I have purchase date and waranty is 2 years. Just simply add 2 years to the purchase date.

Thanks.

How to overwrite computed value?

Great extension! I would like to use it to suggest a date one year in the future, given a certain start date. I succeeded in formulating the template, but when I want to overwrite the computed value, it re-computes (and therefore resets) its value on each keystroke.

field: my_date (format: DD.MM.YYYY)
computed field: future_date
template: {{ CONCAT(LEFT(my_date,6),SUM(INT(RIGHT(my_date,4)),1)) }}

e.g.
my_date: 14.03.2023
future_date: 14.03.2024 (computed)
want to overwrite with 15.12.2024

but when I overwrite the computed field, it won't work. Neither does copy & paste. Is there a workaround by any chance?

Computed Field are not getting date from DateTime Field

Hi,

I'm using Directus 10.5.2 and now the computed fields are not getting dates

Reproduce:
Create DateTime Field with name date
Create Computed Field

use the formula {{ YEAR(date) }}

When you set a date, the computed field should show the year of the selected date, but doesn't return anything.

I saw in the browser console this beaver

If you use the formula {{ YEAR($NOW)}} the field returns the correct year 2023

Can you help me understand if I do something wrong?

Thank you

Lateral String

Hello,

I know you have stated that lateral string does not work, but is there any turnaround for it?
I have created the below computed value and i need it to run when discount_type is "value", how can i reach this?

{{IF(EQUAL(discount_type, "value"), SUM(subtotal, discount_value), subtotal)}}

Thanks

O2M relation operations failing

Hey,

I am having a problem configuring a M2O computed field. The logs are showing the following error:
http://localhost:8055/items/[object%20Object]/2?fields[]=child (403 Forbidden)

Here are my test collections

parent:

child:

computed field:

error:

Directus version 10.5.3

{{ YEAR($NOW) }} not compute

Move from #29.

[Suggestion] Add IF-like IFS

How about adding IFS.
It's in spreadsheet programs.
It has the same effect as overlapping IFs.

e.g.) IFS (conditions, values, conditions, values, ....)

Getting age from a datetime input field

The template can be something like {{ AGE(birth_date) }}, where birth_date is a datetime value from same item.

Other possibility is to make it more generic and get years from a date. Then something like {{ YEARS(birth_date) - YEARS($NOW()) }} would result in an integer representing the age - where $NOW() is the operator from Directus returning the current date.

link: Calculate age given the birth date in the format YYYYMMDD

Compute on the collection table level

Hey,

Is it possible to force computing the value on the collection level?
I know it might be an expensive operation, but say I am willing to delay displaying the data until this is computed.

All other fields values are NULL

Using numbers as operators works just fine, but getting values from fields does not work.

Using Directus 9.21.0.

Tying to calculate the sum of days from two date time inputs?

Tying to calculate the sum of days from two date time inputs, such as:

{{ DAY(SUBTRACT(end, start)) }}
or
{{ SUBTRACT(DAY(end), DAY(start)) }}

But just getting 0, debug doesn't seem to display anything:

Probably user error...

CURRENCY(a) format option not working properly

Hi team, thanks for such great extension

I found a bug with the CURRENCY() formatting syntax feature

I use Directus 10.7.2 and Postgres 15

When I use CURRENCY() on a FLOAT field, it only return the first two digits (excluding everything after the first thousand separator

i,e I have 16000, it should display "16,000". However it only display: "16"

Ability to interact with system fields

Currently, it seems you can't use the computed field with date_created and date_updated
It would be nice if you could read those fields

Calculations not work for decimal data type

Using directus 10.5.3 Need accurate calculations when using sum on decimal data type field getting weird concatenation when displaying as a string in the computational field or no calculations at all.

Please help.

Phil.

Does Directus 10.5.3 break this extension?

I updated last night and then all my calculations broke. I'm reverting back to 10.4.3

Updating empty values

If you clear the input of the original field, the computed interface may throw a error because [].replace cannot exists, for example.
Or, if we clear a number field, the computed value may emit a "NaN" string, instead of null.

Interface should compute if NULL

I had countless trials and errors before I figured this out, I am sure a lot of people go through the same. I actually thought something was wrong with the extension itself.

To reproduce this, create a collection with fields a and b and save an item for it.
Now modify the collection and add a computed field c that adds a and b.
Go back to the collection item and notice the field doesn't show anything.

I thought that something was wrong with my syntax and tried changing to different templates etc, it only worked when I use relation fields (because it had to do something before it displays), which is how I figured out that the interface is calculating this lazily. I now understand that it only calculates when a and b are changed. But a and b already had values. Since this is a new compute field it should initialize by doing what is expected of it.

What I think this should do is, it should initially calculate if its own value is NULL, meaning that it has never done anything before. If it's not NULL then there is a cached value there and it should then lazily wait for changes.

Question: ASUM in Many-to-Many relationship

Hello folks, I have a question that I can't solve by myself.
I have a many-to-many relatioship as below:

products

Column Type

id UUID

name text

price decimal
orders

Column Type

id UUID

user FK to directus_users table
orders_products

Column Type

id UUID

order_id FK to orders table

product_id FK to products table

Column	Type
id	UUID
name	text
price	decimal

Column	Type
id	UUID
user	FK to directus_users table

Column	Type
id	UUID
order_id	FK to orders table
product_id	FK to products table

I have a doubt how I can write a syntax to compute the field like this sql query:

SELECT
        SUM(p.price)
FROM
    orders AS o
INNER JOIN orders_products_1 AS op
    ON (o.id = op.orders_id)
INNER JOIN products AS p
    ON (op.products_id = p.id)

My goal is, when I add the product should calculate the total of order.

Not support multiple computed interface in one item

directus version:
9.23.4
how to reproduce the problem:
create a new table test
add one input field named "h" ,type int
create three computed fields:
a
{{ MULTIPLY(3,h) }}
b
{{ MULTIPLY(4,h) }}
c
{{ MULTIPLY(5,h) }}
when submitted, only c's value will be submitted, a and b will be empty

no value for $CURRENT_USER

no value for {{ STRING($CURRENT_USER.staff_no) }} and also {{ YEAR($NOW) }} in field ..
using "directus-extension-computed-interface": "^1.4.0",

Support $CURRENT_USER data

Hii any plan to support $CURRENT_USER value to computed interface

Others functions [SUGGESTION - FEATURE REQUESTS]

Hi there, great job !
It will be great if we can use others functions like :

IF / ELSE
NULL / NOTNULL
LEFT / RIGHT
Examples : https://support.airtable.com/docs/formula-field-reference

Sum more than two fields

Hi!

First thanks for the plugin which is something I was searching for.
I am facing an issue if I want to add more than two fields.
In my case I have 6 fields to add. I tried several combination but dit not manage to make it work.

Again thank you that is a very useful extension.

Issue with relational calculated field

When using relational ASUM(a, b) we are getting "Failed to load resource: the server responded with a status of 400 ()", we can't figure it out, what is causing it. We are using 10.2.0 version of Directus.

Update: We have modified name of id column, and that is what the problem was. Your name of ID column must be ID.

Field Mode Read Only doesn't show an input field

Hi, as far as I understood from the guide we have 3 fields mode:

null: Default option. Show an input with the computed value but still allow manual editing.
Display Only: Show the computed value but will not save it to the database. Usually used for alias fields.
Read Only: Show an input with the computed value and disallow manual editing.

However I tried all the modes and the only one that shows an input field is the null mode, whilte the Display Only and Read Only show a label. I thought that the Read Only field was going to show a disabled input field.

Am I missing something?

Usage : In display conditionnal

Thanks for this really good Module.
Can we use it for display conditionnal, one way or another ?

I would like to use GT(A,B) and if true, show it in red color for example.

ASUM Method BUG when editing value

Hi,when I change the price value, for some reason, the final price is the product price twice but when I complete the item and return to edit, it changes to the right value as shown in the following video

output.1.mp4

displayOnly prevents computed value to be updated

Checking the code, when props.displayOnly is true, we dont emit our new computed values, just show on the screen.
I think that's not the expected behavior, displayOnly should means that you can't directly edit the field, but you still wish that the value will be saved on database.

Question: Calculate date values

Hey :)

First: awesome extension, thanks.

I've got a question before testing in the field. Does the extension also work with dates? So I saw I can parse values to ISO Dates, but would it be possible to use those for something like SUBTRACT("2022-11-10", "2022-11-08") expect to be 2?

Best regards
Andreas

Result filled not the same as in console.

Result in console log = 110.50000000000001
Result filled = 110

a,b,c,d are all decimal type fields.
Directus Version 10.10.4

Unexpected Error when Primary Key Field Name is not `id`

Took me a little while to debug, but looks like the primary key field name id is hard coded in the extension.

I was trying to calculate against a field via a M20 relationship and the foreign table primary key field was set to uuid instead of id.

Not sure if you can look up the primary key field name and use that or if you have to add an additional input field to allow us to specify the primary key field name. Or alternatively document that as a requirement.

Cheers
Ben

rezo-labs / directus-extension-computed-interface Goto Github PK