I’m just starting to use SeaTable, and I’m completely stuck trying to implement one feature.
To simplify it as much as possible, I keep records of revenues for a business led by several partners. And I need to distribute revenues according to the negotiated shares of each partner. Share of each partner may change over time (new partners join, old ones leave, agreements are re-negotiated etc).
So I’ve created 3 tables:
partners;
shares, with a link to partner, percent in revenues due to the partner, start date & end date of validity of the share (remember, shares are occasionally redistributed);
revenues, with date and sum.
In order to calculate the total amount of revenue due to each partner, I should:
for each share, sum all the revenues which fall into validity period of the share (share.start_date <= revenue.date <= share.end_date) and multiply the result by the percent of the share;
sum all the shares for each partner (this is the easy part if I manage to accomplish the previous task).
The problem is that shares and revenues tables are not linked, so I cannot use linked formula to select all revenues with conditions based on share validity dates. I tried to use data aggregation to create the links (so that each revenue gets linked to each share valid for the date of the revenue), but data aggregation only allows “equals” comparisons, not “greater than” / “less than”. Also I cannot think of any way to change my data model so that the problem is resolved.
Any help would be highly appreciated! I have a feeling I cannot grasp some basic concept, this task should surely be feasible…
Hi @borouhin , and welcome to the forum !
You identified clearly the problem : having a link between the shares and the revenues would greatly simplify the problem, but I’m even not sure that it would be enough, as the conditions in a rollup function don’t cover your need (same problem as what you encountered with the data aggregation) !
For me, you will have to create a script to match your needs. Here is an example considering the following base structure :
Partners table, with the following fields (and type) :
Name (text)
Shares (link to one or more elements of the table below)
Sum (number) : total amount of revenue due to each partner
Shares table :
Id (auto-number)
Start (date)
End (date)
Partner (link to one or more elements of the table above)
Rate (number/percent) : percent of the share
Revenues table :
Id (auto-number)
Date (date)
Sum (number)
And here is the JavaScript script that will update the Sum column for each partner :
// Getting partners
const partnersTable = base.getTableByName('Partners');
const partners = base.getRows(partnersTable, 'Default View');
// For each partner
for (const partner of partners) {
// Getting the shares related to the considered partner
const shares = await base.query('select * from Shares where Partner in ("'+ partner.Name +'")');
let partnerSum = 0;
// For each share
for (const share of shares) {
// Getting the revenues which fall into validity period of the share
const revenues = await base.query('select Sum from Revenues where Date>="'+share.Start+'" and Date<="' + share.End +'"');
// Calculating the sum of all considered revenues
let sum = 0;
for (const revenue of revenues) {
sum += revenue.Sum;
}
// Multiplying this sum by the percent of the share
sum *= share.Rate;
// Summing all the shares for the considered partner
partnerSum += sum;
}
output.text(partner.Name + " : " + partnerSum);
base.updateRow(partnersTable, partner, {'Sum': partnerSum});
}
For now, this script must be run manually, and I don’t know if this is compatible with the constraints of your process (do you want the total amount for each partner to be available at all times, or is it OK for it to be calculated only from time to time? ), but it’s a first answer to your problem.
Thanks a lot for your answer and even a ready to use script! You’ve got my problem perfectly well.
The real database is a bit more complicated (it also has expenses, profit distributions, projects with individual shares in them etc.), but I can elaborate your script further to cover all these issues.
To be honest, I was hoping this could be done without scripting… but alas, since that is the case, I’ll go that route
