Try it this way:

let xBal := sum('UN On Hand' - 'UN to Distrib');
styled(text(xBal), if xBal = 0 then
"green"
else
if xBal > 0 and xBal < 5 then
"yellow"
else
if xBal > 5 then "red" end
end
end)

Sorry, I'm stumped! I can get the styled and color functions to work in form view but not in table view. All it shows is an unformatted 0. Is this correct?

BTW your formula above doesn't check for the number 5 use xBal <= 5 to include in the yellow set or xBal >= 5 to include it in the red set

Regards John

Hi Nick

That's wierd. I took a random table in my sandpit database and added a number and formula field, it displayed OK in form view but I had no joy whatsoever in table view. Created a new table and added the same fields and bingo it all worked.

Thanks for that.

Regards John

Hi John,

...outside the earth or its atmosphere 

These two formula fields have the same formula!

Hi All

When it comes to colouring a field according to its valuse I thought I would put something together that is more re-useable and have come up with the following

let numberField := Number;
let upperBounds := [0, 4, 10];
let colourRange := ["red", "orange", "green", "blue"];
if numberField = null then
styled(text(numberField), "")
else
let colour := item(colourRange, 0);
let i := 1;
for a in upperBounds do
if numberField > a then
colour := item(colourRange, i)
end;
i := i + 1
end;
styled(text(numberField), colour)
end

You only have to amend the first three lines to accommodate your own needs.

Line 1 - amend this to make numberField take the value of your own number field

Line 2 - fill in the upper boundaries that you wish to use. These are inclusive boundaries so for my example its up to and including 0, up to and including 4, etc.

Line 3 - fill in the colours you wish to use. Again the first one will be used up to and including the first upperBound. There is one more colour than upperBound and this is used for everything above that last upperBound

Hope that makes sense.

Regards John

Thanks for sharing John. 

Thanks John