-LIST("") not working

1minManager · 09-22-2020 11:16 AM

I’m trying to get the following formula to work so that it builds a list and removes the blanks:

(
LIST(
ANY(Select(Pricelist[…,
ANY(Select(Pricelist[…,
ANY(Select(Pricelist[…,
ANY(Select(Pricelist[…
)
-LIST("")
)

But i keep getting this kind of output:

I’ve tried using Unique(), -LIST(""), -LIST(" ") and changing the list item formula to be
IF(ISBLANK(ANY(Select(Pricelist[…),ANY(Select(Pricelist[…,"")

Nothing works, I always eand up with a leading comma. Which is a PITA because what I’m trying to do is generate a list of figures, order them low to high, then pick the first item. But this does not work because I always have a leading comma

Anyone any ideas?

Austin

Try if statements around your ANY that if the ANY is blank then “Value you cant get” and then -List(“Value you cant get”).
I am 95% sure -List() should unique the list. Maybe a bug.

1minManager

Tried IF(…,…,“Foo”) and then -LIST(“Foo”). Always shows foo at least once

Aleksi

What happens if you remove the -LIST("") away? Does it give you a list correctly?

1minManager

Removing the -LIST("") make absolutely no difference.

1minManager

The only thing even slightly unsual about this is the equation is in a “Set the values of some columns in this row” action. Which itself is called by an “execute an action on a set of rows”. Everthing about the formula is fine except the -LIST("") not working. So I’ve tried the exact same formula as well as a virtual column. Which works absolutely fine. So must be a bug, I’ll email support@

Steve

Perhaps - LIST(DECIMAL("")) or - LIST(NUMBER(""))?

1minManager

The column type is Price, but I’ll try both…

1minManager

Wow @Steve your the man!

-LIST(DECIMAL("")) works fine I’ll let support know as its still an issue to me that a virtual column gets a different result from a action formula.

Steve

How so?

1minManager

How do I stop this forum automatically taking out commas etc in a string of text?

Steve

Use markdown.

1minManager

LIST("A","","B","",C","") should give A,,B,,C,

LIST("A","","B","",C","") -LIST("") should give A,B,C

LIST(“A”,"",“B”,"",C","") -LIST("") in an action formula gives the top (wrong) result as it removes the duplicates but not the blanks. So its not implementing the -LIST("") bit correctly

LIST(“A”,"",“B”,"",C","") -LIST("") in a virtual column gives the bottom correct result

Steve

Expressions are evaluated by different code in the app and on the servers. Actions are evaluated in the app, but virtual columns are updated in bulk by servers. This appears to be an inconsistency. I urge you to report it to support@appsheet.com.

1minManager

Its already been reported