Aug 21, 2024 05:34 AM - edited Aug 21, 2024 11:04 AM
Hi everybody,
Let's say I've got 2 tables linked to one another (NUMBERS and OBJECTS)
My question...
In OBJECTS I would like to create a rollup field that shows me all the NUMBERS ranked higher then 11 according to the {ranking} field in OBJECTS
It can't be done with the "regular" condintional settings. So I tried to include a formula at the bottom of the Rollup field:
IF(value > {ranking},ARRAYJOIN(values, ", "))
I was hoping to see: 12, 13 but i got #ERROR instead... Is there a way to include logical operators and field values within the formula of a Rollup field?
Also... working with text fields instead of numeric fields leads to a new problem. Thats only works when the NUMBERS are linked in the right order. For instance if OBJECT A is linked like this: 7, 11, 5, 13, 12, 6... I still want my outcome to be 12 and 13.
I've looked into the 'ARRAYSLICE' function which I haven't used before. But that function also seems to work with a position of a certain string in a text, rather than a numeric value like < or =
Any suggestions?
Thanks,
Aug 21, 2024 06:29 AM
Hi Koen360>
You could try this.
Add a new formula field to your NUMBERS table called something like {Higher than Ranking} with:
IF({Rank} > {ranking}, {Rank}, "")
Then in your OBJECTS table use a rollup field of {Higher than Ranking} with the aggregation formula: ARRAYJOIN(values, ", ")
If I understand you, I think that would give the result you are looking for.
Aug 21, 2024 06:56 AM
Hello,
@Dominic If I understood correctly, "Ranking" value is set in table "Objects", not in table "Numbers", meaning that formula "Higher than Ranking" cannot be set as such.
I have a solution that sounds weird but seems to work:
2 conditions:
- "Ranking" field's type in table "Objects" must be Text
- Ranking value must be one of the numbers linked to the object (there is a turnaround to this condition, but it makes it more complex)
I separated in different formulas to make it clearer
- Step 1 - Rollup field "Number list":
- Step 2 - Formula "Ranking Position"
- Step 3 - Formula "Start extraction":
- Final step - Formula "Ranks":
Regards,
Pascal
Aug 21, 2024 09:09 AM
Your approach is quite clever. However working with text fields instead of numeric fields leads to a new problem. It only works when the NUMBERS are linked in the right order. For instance if OBJECT A is linked like this: 7, 11, 5, 13, 12, 6... I still want my outcome to be 12 and 13.
I've looked into the 'ARRAYSLICE' function which I haven't used before. But that function also seems to work with a position of a certain string in a text, rather than a numeric value like < or =
Any suggestions?