List produces "null"

[Kirk_Masden]

I would like to report on something I noticed but which seems to be resolved no.

What I noticed
A rather complicated list expression was producing something like “230, null” but then after a few seconds had passed and the background sync had completed it would revert to “230” (a list of one item), which was correct in this case.

What I did
I added the following to the expression, and the addition seems to have fixed the problem:

-list(“null”)

Since then
I tried to replicate the problem but, for some reason, I haven’t seen the “null” word again, even after removing -list(“null”) from the expression.

My question
The list expression is one I use to write to my sheet. I’m afraid that the addition of “null” might mess up my data. So, I have returned -list(“null”) to my expression. I don’t really understand what happened and wonder if anyone out there can help me understand why list expression might result in “null”.

[Aleksi]

Normally you are able to see the “null” option for example with the Enum field if the formula in Valid If produces an empty list. May I ask what kind of formula you have in your Valid If?

[Kirk_Masden]

Actually, it’s not a Valid If. It’s an expression that produces a list, separated by commas that is written to the spreadsheet. Here’s what I am using now:

list(concatenate(LOOKUP("Session1", "Settings", "Key", "Number")))
+split(
	index(
		split(
			concatenate("-100 , ",
			sort(
				split(
					concatenate(
						IFS(
							number(left([Schedule position],1))=6,
							list(
								LOOKUP("Session1", "Settings", "Key", "Number"), LOOKUP("Session1", "Settings", "Key", "Number")+33),
							isblank([Extra]),
							{0},
							true,
							LIST([Extra]))+
							list(
								LOOKUP("Session1", "Settings", "Key", "Number"), 
								[Start at session no] + index({2, 6, 14, 30, 63},
									number(left([Schedule position],1)))
							)
					)
				," , ") - list() - list("0"))
			),
		concatenate(LOOKUP("Session1", "Settings", "Key", "Number")," , "))
	,2)
," , ") -list("null")

Thanks in advance!

[Steve]

list(concatenate(lookup("Session1", "Settings", "Key", "Number")))
+ split(
  index(
    split(
      concatenate(
        "-100 , ",
        sort(
          split(
            concatenate(
              ifs(
                (number(left([Schedule position], 1)) = 6),
                  list(
                    lookup("Session1", "Settings", "Key", "Number"),
                    (lookup("Session1", "Settings", "Key", "Number") + 33)
                  ),
                isblank([Extra]),
                  {0},
                true,
                  list([Extra])
              )
              + list(
                lookup("Session1", "Settings", "Key", "Number"), 
                [Start at session no]
                + index(
                  {2, 6, 14, 30, 63},
                  number(left([Schedule position], 1))
                )
              )
            ),
            " , "
          )
          - list()
          - list("0")
        )
      ),
      concatenate(
        lookup("Session1", "Settings", "Key", "Number"),
        " , "
      )
    ),
    2
  ),
  " , "
)
-list("null")

What in the world?!

[Kirk_Masden]

I realize that what I’m doing is unconventional but this works. In a nutshell, it’s a “stuff to do list” and stores the numbers of “sessions” (a concept in my app) that should be revisited.

I feel like I showed everyone the contents of a closet – and everything fell out when I opened the door.