Rollup field formula combined with Logical operators

[Koen360]

Hi everybody,

Let's say I've got 2 tables linked to one another (NUMBERS and OBJECTS)

• Table 1: 100 NUMBERS ranked from 1-100
• Table 2: OBJECTS which are randomly linked to these NUMBERS 
• OBJECT A is linked with 6 random NUMBERS: 5, 6, 7, 11, 12, 13
• In OBJECT's there's a field {ranking} that says 11

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,  

[Dominic]

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.

[Pascal_Gallais-]

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.

@Koen360 

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

[Koen360]

Hi @Pascal_Gallais- 

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?