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

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