Force numerical order in Lookup field?

[jezburrows]

Hey all,

I have a Lookup field that is returning a list of numbers, but for some reason the list is not in numerical order. Is there any way to to return “7, 27, 28, 90, 123, 123” instead of “27, 7, 28, 90, 123, 123”? Alternatively, is there a way to take the numbers from this column and re-order them in another column?

Bonus points: If there was some way to remove duplicate numbers and append a “p” before each number, we’d be in business.

[W_Vann_Hall]

Can’t help you on the numeric order, unfortunately, but you cab try using a rollup field with ARRAYUNIQUE() to remove redundant entries from the array. Whether or not that will work depends on how the array is being created in the first place, so your mileage may definitely vary.

You cab use a rollup field with the following aggregation *formula * (just enter it where the aggregation function would ordinarily go) for a list of numbers prefaced with ‘p’ and separated by a space:

TRIM(
    SUBSTITUTE(
        ','&ARRAYJOIN(
            ARRAYUNIQUE(
                values
                )
            ),
        ',',
        ' p'
        )
    )