Building weighting formulas across databases and Lookup

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??


The formula field reference guide is here:

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

.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…


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…

You might wanna read this.

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

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