Hi @Chr1sG ,
After playing a bit with Databases I found two workaround this issue. The first one, using the Reports as you suggested (Thank you!) and a second one that I find very interesting as it allowed the use of recursive formulas within a Database (which apparently was not possible -but it seems it is!). I will elaborate on the two options as it could be helpful for other new users, and also there are some questions arising from each one where I would love to have your thoughts:
Using Reports:
As an Example, I create a Database called “Weight Tracker” to monitor weight gain/loss during a diet. The input entities have Fields such “Weight” and “Date” to record the weight each time is measured. The aim is to calculate the Cumulative Weight (Total Weight Loss/Gain) and the Variation against the last measure (the difference between current measure and the last recorded one).
Here it is a screenshot of the Database
I created a Table Report for the Database adding the following Fields: Weight and Date. Then I created two additional fields using calculations within the Report’s configuration menu, “Weekly Weight Loss” (->Variation) and “Total Weight Loss” (-> Cumulative). Please find below the formulas used for each:
Weekly Weight Loss Formula
IFNone(
DIFFERENCE(
SUM([Weight])
),
0
)
Total Weight Formula
IFNone(
RUNNING_SUM(
DIFFERENCE(
SUM([Weight])
)
),
0
)
As result, the following Report was Generated:
Questions/Comments for @Chr1sG consideration:
- I loved the report, it’s clean and easy to use, also allow some cool extra details such adding colour and bars, etc. Fantastic.
- The only “but” is that to generate the report using the “ad-hoc” calculations it is mandatory to include the necessary Fields that will be used to make those calculations (all fine with this); however when the report is generated I cannot “hide” any of these columns. For example, if I only want to see the last two columns (for which I created the calculations) I’m unable to do so. This may be an inconvenient when you have calculations requiring several Fields that will not be relevant to display once the calculations are done.
Now, the second approach (which I find much more interesting to discuss), generating the formulas directly on the Database.
Using Relations
Context: as per the initial trend message, the issue was that Formulas within a Database cannot call, use or aggregate information from other entities in the collection. The formulas can only call Fields within the same entity or Relations of the same entity. Therefore, tasks such Running Sums or calculating the difference between a new entity (Field) and the previous one, were not possible. @Chr1sG commented that one of the reasons to avoid Formulas to be dependant of other entities within the Database was to prevent overloading when using large database - which make total sense.
The workaround I found to solve this directly from the Database (without using Reports) was as Follows:
Step 1: Generate a Relation (many-to-many) of the Database with itself. For this example I created a new Database called “Rsum” simulating a “Sales Tracker”: it records total sales of the month, and similar to the previous example, the aim is to calculate The Running Sum of total sales (-> Cumulative) and the Sales Diferencial from the previous entry (->Variation). Therefore the Database has a Field called “Sales” (numeric Entry) and two Formula Fields named “Cumulative” and “Variation”. And again, I Related the Database with itself. See screenshots below:
Step 2: I create an Automated Rule using a Script that will automatically Link (Related) any new entry to the Database with all existing entries of the Database (which has been related to itself as per the previous Step). So, the Scrip runs every time an entity is created. Here it is the script (which was generated using your AI assistant - which was great for me as I don’t now how to code):
const fibery = context.getService('fibery');
// Get the IDs of the current entities
const currentIds = args.currentEntities.map(entity => entity.id);
// Retrieve all RSum entities
const allRSums = await fibery.executeSingleCommand({
"command": "fibery.entity/query",
"args": {
"query": {
"q/from": "Main Menu/RSum",
"q/select": ["fibery/id"],
"q/limit": "q/no-limit"
}
}
});
// Extract IDs of all RSum entities
const allRSumIds = allRSums.map(entity => entity["fibery/id"]);
// Prepare the batch operation to link the current RSum entities to all RSum entities
const linkOperations = [];
for (const currentId of currentIds) {
for (const rsumId of allRSumIds) {
linkOperations.push({
id: currentId,
itemId: rsumId
});
}
}
// Perform the batch linking operation
if (linkOperations.length > 0) {
await fibery.addCollectionItemBatch("Main Menu/RSum", "RSums", linkOperations);
}
return `${currentIds.length} RSum(s) linked to all RSums`;
Step 3: Generate the Formulas for “Cumulative” and “Variation” Fields in the Database.
Cumulative Formula
RSums.Filter(Period < [This RSum].Period).Sum(Sale) + Sale
Variation Formula
If(
Month(RSums.Sort(Period).First().Period) >= Month(Period) and
Year(RSums.Sort(Period).First().Period) = Year(Period),
0,
Sale -
RSums.Filter(
Month(Period) = Month([This RSum].Period) - 1 and
Year(Period) = Year([This RSum].Period)
).Sum(Sale)
)
Step 4: The Database will now automatically calculate the Running Sum of the sales as well as the difference between the entity Sales and the previous recorded value. Without using Reports!
Step 5: Generate a custom View of the Database. See screenshot below:
Questions/Comments for @Chr1sG consideration:
- Although this method is a bit more complicated to setup I think it is a bit more customizable than the Report, and then you can create a custom View using only the specific fields you want to show, and hidden the rest of unwanted fields from the Database (which apparently cannot be done when using Reports).
- I guess this is a viable workaround when a situation requires to make Formulas or Calculations between entities of a single Database.
P.S.:
- I don’t know how to code and I’m just starting with Fibery, so perhaps the formulas used here could be more “elegant”.
- @Chr1sG can you think of another Title for this Trend? Perhaps it could be useful for other users (specially newbies like me). And perhaps I’m not using the best Title to address the issues presented in the trend. Let me know.
Have a wonderful weekend and I’m looking foward to having your thoughts about this.
Best,
Miguel
