ARRAYJOIN Rounding Question

[wrasse]

When using Array Join, if my price was 410.00 it removes the decimals on output and becomes 410 and it does not look good on my print sheet.

How do I force a number to output two decimal places inside an ArrayJoin?

Example, 410 becomes 410.00
Also, 0.1 becomes 0.10 etc

Thanks!
Joey

[Justin_Barrett]

Welcome to the community, Joey! :grinning_face_with_big_eyes: One thing to keep in mind is that the decimal and trailing zeroes that you see in 410.00 are due to display formatting. ARRAYJOIN isn’t rounding anything. What it’s collecting are the actual numbers, not their display-formatted versions. There’s no way to change this behavior, and no way to mass-reformat numbers inside an array.

You’ll need to use a formula to manually re-format the value, and then collect that manually-formatted version instead of the numeric value. I do this exact thing in one of my own bases, though this post prompted me to revisit my system, and I was able to optimize it a bit. In my actual base I use some helper formula fields as pseudo-variables to keep the main formula smaller, but here’s the full thing with all of that stuff in a single formula (replace references to Amount with your own value field):

SUBSTITUTE(
    "$" &
    LEFT(
        (Amount * 100) & "",
        LEN((Amount * 100) & "") - 2
    ),
    IF(
        Amount >= 1000,
        "$" & FLOOR(Amount / 1000)
    ),
    IF(
        Amount >= 1000,
        "$" & FLOOR(Amount / 1000)
    ) & ","
) &
"." & RIGHT((Amount * 100) & "", 2)

Note that this is specifically optimized for currency display with a leading dollar sign. If you don’t want that, taking the dollar signs out of the formula may mess up the formatting depending on the value you feed it. You’ll need to wrap that full formula inside a SUBSTITUTE to remove it.

For another take on pretty-printing values like this, check out this thread and associated sample base by @W_Vann_Hall .

[wrasse]

tried it but getting an error. All I want to do is have the currencies listed as a new line, not comma separated. The rollup makes them comma separated, maybe a different way to do it?

[wrasse]

I got it working but it’s doing some really funky stuff and the numbers don’t match. I’ll have to do some work on it.

[Justin_Barrett]

Could you describe in greater detail the end goal you’re trying to reach, and the steps you’re currently taking to get there? Perhaps we can offer an alternate solution.

[Justin_Barrett]

FWIW, the comma separation in a rollup is only cosmetic. That’s just how arrays are displayed. The commas aren’t actually in the data. If you want to separate each rolled-up item onto its own line, alter the ARRAYJOIN aggregation function to use a newline character (\n) as the separator:

ARRAYJOIN(values, "\n")