Format Settings option missing for Formula Column

[robflate]

I have 2 seemingly identical tables. In one, the Format Settings option is missing from a Formula column.

Working table

Broken table

Format Settings visible in working table

Format Settings missing in broken table

Formula in column with issue (Difference)

[daniel.pan]

It is likely one of the column was created in an old version of SeaTable. Recreating a formula column with the same formula should solve the problem.

[robflate]

Thanks for the reply. I already tried that and the issue remains. What’s strange is that if I create a new Formula column and set it to something like {Cost}, the Format Settings option is visible but as soon as I paste the formula and hit Submit, it breaks and the Format Settings option disappears. However, pasting the exact same formula into a new base works without issue;

if(not({Cost}), "", {Cost}-{Estimate})

Also, if I change the formula to;

if(not({Cost}), 0, {Cost}-{Estimate})

the Format Settings option reappears but this format defeats the purpose of the formula. Basically, I want to say;

if (Cost is empty), show nothing in Difference, else show Difference.

[robflate]

Could it be to do with setting the result of not() to "" which is text not a number so therefore there are no Format Settings? Is "" the correct way to set a number field to be blank?

This wouldn’t explain why the formula successfully works and displays the Format Settings in a different base but I just wondered.

[daniel.pan]

The problem is that, “” in IF formula will produce a result of type string. We will see how to improve it.

[robflate]

Thanks for the answer. Can you answer a couple of quick follow ups?

1. How have I managed to successfully use "" in a formula column whilst still having the column show a number and also expose the number Format Settings menu (see below)?
   

   

2. In the meantime, what would the correct way to achieve if (Cost is empty), show nothing in Difference, else show Cost - Estimate as a currency number.

Thanks again.

[daniel.pan]

@robflate

1. In the meantime, what would the correct way to achieve if (Cost is empty), show nothing in Difference, else show Cost - Estimate as a currency number.

This can’t be achieved now.

[robflate]

Thanks. I’m currently achieving it using;

if(
  countblank({Estimate}, {Cost}) > 0, "",
  {Estimate} - {Cost}
)

The result shows as a currency number and the Format Settings option is visible.

On another note, any plans to include an inline code editor that supports indentation, colour, bracket matching etc. I love the power of the formula editor but it can get hard to read when a formula gets complex.

Anyway, thanks for Seatable, it’s a really amazing app!

[robflate]

I can confirm that this is fixed in version 1.7. Also, I can now use £ with the custom currency format. Thanks!