Building weighting formulas across databases and Lookup

Get Help
1

UPDATE: I got “[Angle Goals].Sum(Priority)” to show values on the Angles database! Yay!

Basically I’m trying to score things in many databases and then run those scores through a formula to then generate a list of “next priority” ideas (maybe in a Doc that can then get made into tasks). My problem is I can’t reliably Lookup or use Formulas to reference the fields in other datasets.

In plain English I have multiple datasets in which each item gets a numerical score and a weighted score - we’ll call them Goals, Blocks, Platforms, Frames, Angles, Slants, and Periods. Each will have a base priority and a weighted priority based on its relationships. That weighted priority is what then gets passed on to the final priority calculation.

Here’s the exact problem I’ve run into already:

I have Goals (many to many) related to Frames and Angles. Frames are one to many Angles. I can Lookup the Frames scores from Angles, but nothing else. I’ve tried using a formula and calling it through the relation, but I can’t figure out for the life of me how to make it work. The Formulas documentation has not been serving me terribly well.

Goals.Sum(Priority) says it’s not declared no matter where I try to put it (Frames, Angles, or trying to get scores out of those two into Goals). Nevermind that isn’t the actual formula I want to use, I just want to test access to the fields lol

The actual formula I want to use is more future proof. Basically I want to convert a plain English priority (1 for first, 2 for second) into a number never larger than 1 - a percent multiplier for weighting a value later on.

And, uhm, I have yet to make such a formula work… so if you could help with that too heh :slight_smile:

Essentially what I’m thinking for the formula is something like this:

Absolute Value of (Priority - (Total Goals Count + 1)) / Total Goals Count

Like in Angles I’m working on this:

Absolute Value of ([Angle Goals].Sum(Priority) - Total Goals Count Squared) / Total Goals Count

UPDATE 2: Are these ONLY for reports? Or some such? I tried using Absolute Value and Running Sum in a formula and it said they didn’t exist.

Why can I not find a comprehensive list of functions for formulas when I’m looking for functions for formulas??

2

Yes

The formula field reference guide is here:
https://the.fibery.io/@public/User_Guide/Guide/Formulas-reference-44

As you have discovered, it is possible to write formulas that calculate aggregate values for related items.
However, any given entity in a database, does not ‘know’ anything about the values of items it is not-related to.

So even if an entity in database A is linked to multiple entities in database B (i.e. A1 → B1, B2) it is not aware of all entities in database B.
There is no possible formula field that will allow A1 to calculate (B1.value + B2.value)/(B1.value + B2.value + B3.value)

Similarly, no entity in database B is aware of other entities in the same database.

It is possible to do such calculations in a report, but report views are read-only summaries.

1 Like
3

.First and .Last and some other similar “sub” functions are not really well explained. And I’ve never found a use for the [This Entity] thing but it’s interesting.

I’ve managed to get formulas to work both ways and since Absolute Value calculations can’t be done in formulas, I found the Formulas guide for Single-Select and that made me super happy - now I can just give it a calculable value then seek Max / Greatest to calculate max then make the percentile weight :slight_smile:

Took long enough but I’m starting to get the hang of this language…

4

HAHA!

Ok so yeah every last formula is going to use info specifically from what is attached… oki doki… let’s see if I can add another hidden value…

5

You might wanna read this.

6

Do you mean Abs(x) ?
It can be achieved using if(x<0,0-x,x)

7

:man_facepalming: That’s much better than Power(.5, Power(2)) like I was assuming I’d have to do…