Switch() / ISBLANK() / CONCATENATE() all in one formula Possible?

AppSheet AppSheet Q&A
,
1

I am trying to combine Text values into One Column, based on whether or not there is a value to combine. I thought a SWITCH() function would work but I am struggling with the logic. Below is the output with a simplified formula.

jrice_0-1646928655579.png

SWITCH([Dimensions],
ISBLANK([Var3]),CONCATENATE([Var1],'" x ‘,[Var2]),
ISBLANK([Var4]),CONCATENATE([Var1],’" x ‘,[Var2],’" x ‘,[Var3]),
ISBLANK([Var5]),CONCATENATE([Var1],’" x ‘,[Var2],’" x ‘,[Var3],’" x '[Var4]),

CONCATENATE([Var1],'" x ‘,[Var2],’" x ‘,[Var3],’" x ‘[Var4],’" x ',[Var5])
)

If i am thinking correctly I should not be getting any output until values are present. If you could help I would greatly appreciate it. Thank you.

2

You need to review how SWITCH works:

https://help.appsheet.com/en/articles/2355972-switch

You should probably be using IFS() here instead:

https://help.appsheet.com/en/articles/2355961-ifs

1 Like
3

Unfortunatly when testing both IFS() and Nested IF() statements the column formula executions ends once the first operation is True. For accuracy this formula would need to keep checking even after the first operation is True.

IF(
  [Dim1]>0.000,CONCATENATE([Dim1],'"'),
  IF(
    [Dim2]>0.000,CONCATENATE([Dim1],'" x ',[Dim2],'"'),
    IF(
      [Dim3]>0.000,CONCATENATE([Dim1],'" x ',[Dim2],'" x ',[Dim3],'"'),
      IF(
        [Dim4]>0.000,CONCATENATE([Dim1],'" x ',[Dim2],'" x ',[Dim3],'" x '[Dim4],'"'),
        "Enter Dimensions"

        )
      )
    )
  )

jrice_0-1646970642800.png
jrice_0-1646970642800.png310×466 8.61 KB

jrice_1-1646970723056.png
jrice_1-1646970723056.png836×472 29.7 KB

4

Will this work?

A temp VC [conc]

CONCATENATE(
 IF(ISBLANK([v1]),"",[v1] & " x "),
 IF(ISBLANK([v2]),"",[v2] & " x "),
 IF(ISBLANK([v3]),"",[v3] & " x ")
)

Your Dimensions VC

LEFT([conc], LEN([conc]) - 2)

TeeSee1_1-1646977254462.png
TeeSee1_1-1646977254462.png181×493 3.76 KB

You could combine the above into one VC, kinda ugly looking.

LEFT(
 CONCATENATE(
  IF(ISBLANK([v1]),"",[v1] & " x "),
  IF(ISBLANK([v2]),"",[v2] & " x "),
  IF(ISBLANK([v3]),"",[v3] & " x ")
 ),
 LEN(
  CONCATENATE(
   IF(ISBLANK([v1]),"",[v1] & " x "),
   IF(ISBLANK([v2]),"",[v2] & " x "),
   IF(ISBLANK([v3]),"",[v3] & " x ")
  ) 
 ) - 2
)
3 Likes
5

This worked beautifully, Thank you. Only downside is if a user has a value in v1,v2,v3 and deletes the value in v2, It breaks but I can put other checks in to ensure i recieve the information needed. Your perspective is appreciated.

6

Mine seems to work fine regardless of how you enter/delete values.

Animation.gif

3 Likes
7

ah, I should have been more specific to my case. Yes it works perfectly but for the application I am building it it cant have an empty V2 value.