AppFlowy-IO / AppFlowy-IO/AppFlowy
[FR] Add Formula field to database
- Langage dominant
- Dart
- Étoiles
- 76.6k
- Forks
- 6k
- Métriques de merge des PR
- Aucune PR mergée en 30 j
Description
### Description
I'd like to propose adding a first-class **Formula** property to AppFlowy databases.
This is related to [#8470](https://github.com/AppFlowy-IO/AppFlowy/issues/8470), which includes formula support among several other database-related requests. I'm opening a separate issue so that the **formula-specific syntax, behavior, and general approach** can be discussed independently.
Instead of a simple feature request asking "please add formulas," this proposal aims to outline a possible approach and some considerations for the feature, while leaving room for discussion and alternative implementations.
A formula property would provide a way to calculate derived values from other properties in a database, including values obtained through relations and rollups.
For example:
```text
Equipment Definition
├── Model
└── Price
Project BOM
├── Equipment Definition ← Relation
├── Quantity
├── Price Each ← Rollup
└── Price Total ← Formula
```
The formula for `Price Total` could simply be:
```js
quantity * priceEach
```
This is a relatively simple example, but the same capability could support considerably more expressive calculations.
### Impact
A "calculated" field in AppFlowy's database is the **only** feature that prevents myself and likely countless others from moving off of the "alternative" platform.
Coupled with the existing Relation and Rollup features, adding a Formula field would make AppFlowy **the only** other platform with a complete database implementation.
### Additional Context
#### Formula Syntax
Rather than introducing another Excel-like function language, I think a **JavaScript-inspired expression syntax** could provide a familiar and expressive foundation.
For example:
```js
quantity * priceEach
```
could be extended naturally with familiar operators and expressions:
```js
value || "N/A"
```
```js
condition ? valueIfTrue : valueIfFalse
```
and, for collection-valued properties such as rollups:
```js
values.first()
```
```js
values.filter(value => value > 100)
```
```js
values.map(value => value * 2).sum()
```
This approach could make formulas substantially more expressive without requiring a large collection of specialized functions for every possible operation.
The intent would **not** be to execute arbitrary JavaScript. Rather, the proposal is for a deliberately constrained expression language inspired by JavaScript syntax, with the available operators, functions, types, and capabilities explicitly defined by AppFlowy.
For safety and predictability, the use of AST parsing and controlled evaluation would seem to be the appropriate approach.
The exact syntax and supported functionality would of course be open for discussion.
#### Type Awareness
I think formulas would benefit significantly from being aware of the types of the properties they reference.
For example, if a rollup produces a collection of numbers:
```text
Rollup
↓
Collection
↓
.first()
↓
Number
```
the formula system could understand that `first()` produces a number and therefore allow appropriate operations on the result.
Likewise, collection methods could operate on the collection itself:
```js
values.filter(value => value > 100)
```
This type information could potentially support a much better formula-editing experience as well, including things such as:
- Property-aware autocomplete
- Method/function completion
- Type-aware diagnostics
- Invalid-operation detection
- Inline formula errors
- Potentially inferred formula result types
#### Relations and Rollups
Relations and Rollups are a powerful feature in AppFlowy. With the addition of formula properties, users could perform more advanced calculations and unlock their full potential.
Conceptually:
```text
Existing AppFlowy properties
│
├── Number
├── Text
├── Date
├── Relation
├── Rollup
└── ...
│
▼
Formula Property
│
▼
Derived Value
```
This would make formulas a general-purpose calculation layer over the database functionality AppFlowy already provides.
One could even utilize two-way relations within formulas to create more dynamic and interconnected calculations.
Consider the example above, but with a two-way relation:
```text
Equipment Definition
├── Model
├── Project BOM Members ← Back-Relation from Project BOM
├── Project BOM Quantity ← Rollup
├── Total Cost ← Formula
└── Price
Project BOM
├── Equipment Definition ← Two-way Relation to Equipment Definition
├── Quantity
├── Price Each ← Rollup
└── Price Total ← Formula
```
The formula for "Total Cost" would be:
```js
projectBOMQuantity.sum() * price
```
Which would calculate the total purchase cost currently required across all projects (other databases similar to "Project BOM" would be included for more comprehensive calculations).
This would make formulas a general-purpose calculation layer over the database functionality AppFlowy already provides.
#### Discussion
The primary goal of this issue is to discuss the **fundamentals of the feature**:
- What should the formula syntax look like?
- Which operators and types should initially be supported?
- How should collections and rollups behave?
- How should null/empty values be handled?
- How should formula results be typed?
- What should the security/execution boundaries look like?
- How should formulas interact with existing AppFlowy properties?
- What would be the most natural way to integrate this with AppFlowy's existing architecture?
I'm particularly interested in whether a JavaScript-inspired expression language could provide a good balance between familiarity, simplicity, and expressiveness.
If this is something that interests any AppFlowy maintainers, contributors, or community members, I'd love to hear your thoughts on the approach and what would make the most sense within the existing codebase.
Guide de contribution
Aucun guide de contribution indexé pour ce dépôt
Évaluation
Cette issue n'a pas encore été évaluée.