Add condition IFS formula

[marinocity]

Hi!

I need to add another condition to IFS formula.

& ([Related RMAs][STOCKONO]=“STOCK”)

[STOCKONO] - Column type ENUM inside RMA Table

When add this condition, error formula

ONCATENATE(
  [PART NUMBER],
  IFS(
    (SUM([Related SIN CARGADORs][CANTIDAD]) > 0),
      CONCATENATE(
        " (<li-emoji id="lia_electric-plug" title=":electric_plug:"></li-emoji>",
        SUM([Related SIN CARGADORs][CANTIDAD]),
        ")"
      )
  ),
  IFS(
    (SUM([Related RMAs][CANTIDAD]) = 1)& ([Related RMAs][STOCKONO]="STOCK"),
      CONCATENATE(
        " (<li-emoji id="lia_hammer-and-pick" title=":hammer_and_pick:"></li-emoji>️",
        SUM([Related RMAs][CANTIDAD]),
        ")"
      )
  )
)

ERROR
Cannot compare List with Text in ([Related RMAs][STOCKONO] = “STOCK”)

[Marc_Dillon]

https://help.appsheet.com/en/articles/2347624-and

https://help.appsheet.com/en/articles/2357277-in

[marinocity]

@Marc_Dillon

https://help.appsheet.com/en/articles/2347624-and

https://help.appsheet.com/en/articles/2357277-in

CONCATENATE(
  [PART NUMBER],
  IFS(
    (SUM([Related SIN CARGADORs][CANTIDAD]) > 0),
      CONCATENATE(
        " (<li-emoji id="lia_electric-plug" title=":electric_plug:"></li-emoji>",
        SUM([Related SIN CARGADORs][CANTIDAD]),
        ")"
      )
  ),
  IFS(
    AND ([Related RMAs][STOCKONO]="STOCK",(SUM([Related RMAs][CANTIDAD]) = 1),
      CONCATENATE(
        " (<li-emoji id="lia_hammer-and-pick" title=":hammer_and_pick:"></li-emoji>️",
        SUM([Related RMAs][CANTIDAD]),
        ")"
      )
  )
)

Wont work, No funciona

Any help?

[Marc_Dillon]

What is the error?

[marinocity]

Expression ‘CONCATENATE( [PART NUMBER], IFS( (SUM([Related SIN CARGADORs][CANTIDAD]) > 0), CONCATENATE( " ( “, SUM([Related SIN CARGADORs][CANTIDAD]), “)” ) ), IFS( AND ([Related RMAs][STOCKONO]=“STOCK”,(SUM([Related RMAs][CANTIDAD]) = 1), CONCATENATE( " ( ”, SUM([Related RMAs][CANTIDAD]), “)” ) ) )’ was unable to be parsed: Number of opened and closed parentheses does not match.

[Marc_Dillon]

opened and closed parentheses does not match

So are you confused by what that means or are you just being lazy? I have no interest in balancing your parentheses for you.

[Swoopy]

I don’t understand your logic but you’d try

CONCATENATE(
   [PART NUMBER],
   IFS(
      SUM([Related SIN CARGADORs][CANTIDAD]) > 0,
         CONCATENATE(
            " (", SUM([Related SIN CARGADORs][CANTIDAD]), ")"
         ),
      AND(SUM([Related RMAs][CANTIDAD]) = 1, ANY([Related RMAs][STOCKONO]) = "STOCK"),
         CONCATENATE(
            " (️", SUM([Related RMAs][CANTIDAD]), ")"
         ),
      TRUE,
            ""
   )
)

ERROR
Cannot compare List with Text

This tells, you’re comparing Text (“STOCK”) with List (output from [Related RMAs][STOCKONO]). You need to convert list to text using an expression like ANY().

[Aurelien]

Hi @marinocity

@marinocity

(SUM([Related RMAs][CANTIDAD]) = 1)& ([Related RMAs][STOCKONO]=“STOCK”),

you are concatenaint a chain of characters here.

[Related RMAs] is a list of references.

[Related RMAs][CANTIDAD] is a list of numbers, so the SUM([Related RMAs][CANTIDAD]) works fine.

[Related RMAs][STOCKONO] is a list of whatever is [STOCKONO] type.

[Related RMAs][STOCKONO]=“STOCK” won’t work, because you can’t compare a list (left part) to a single text (right-part)

On this part, can you tell us which result you would expect to see?

[marinocity]

Thanks @Swoopy

Your formula dont have errors but wont work.

[STOCKONO] - Column type ENUM inside RMA Table - (2) values - STOCK / CLIENTE

If I choose STOCK, the result should be:

PRODUCTO ( 1) ( 1)

If I choose CLIENTE, the result should be:
Because only need that show it when choose STOCK, not CLIENTE

PRODUCTO ( 1)

[marinocity]

Thanks @Aurelien

STOCKONO] - Column type ENUM inside RMA Table - (2) values - STOCK / CLIENTE

If I choose STOCK, the result should be:

PRODUCTO ( 1) ( 1)

If I choose CLIENTE, the result should be:
Because only need that show it when choose STOCK, not CLIENTE

PRODUCTO ( 1)

[Swoopy]

Because I don’t know your data structure, I guess

CONCATENATE(
   [PART NUMBER],
   IF(
      SUM([Related SIN CARGADORs][CANTIDAD]) > 0,
      CONCATENATE(
         " (", SUM([Related SIN CARGADORs][CANTIDAD]), ")"
      ),
      ""
   ),
   IF(
      AND(SUM([Related RMAs][CANTIDAD]) = 1, [STOCKONO] = "STOCK"),
      CONCATENATE(
         " (️", SUM([Related RMAs][CANTIDAD]), ")"
      ),
      ""
   )
)

[marinocity]

Thanks @Swoopy
Still wont work

Structure:

-STOCK (Table)

• Part Number (Key)
• Cantidad
  etc

-SIN CARGADOR (Table)

• Part Number (ref)
• Cantidad
  etc

-RMA (Table)

• Part Number (ref)
• Cantidad
  etc

-SALIDAS (Table)
-ENTRADAS (Table)

[Swoopy]

STOCK(Part Number*, Cantidad) – parent table
SIN CARGADOR(Part Number, Cantidad) – child1 table
RMA(Part Number, Cantidad, StockoNo) – child2 table

Assuming this expression is placed in a virtual column of the STOCK table, try

CONCATENATE(
   [PART NUMBER],
   IF(
      ISNOTBLANK([Related SIN CARGADORs][CANTIDAD]),
      CONCATENATE(
         " (", SUM([Related SIN CARGADORs][CANTIDAD]), ")"
      ),
      ""
   ),
   IF(
      IN("STOCK", [Related RMAs][STOCKONO]),
      CONCATENATE(
         " (️", SUM([Related RMAs][CANTIDAD]), ")"
      ),
      ""
   )
)

[marinocity]

Thanks @Swoopy
It’s almost finished, only have a problem.
When RMA is fixed, [CANTIDAD]=0 or
SIN CARGADOR, [CANTIDAD]=0

Show Product in Stock like that:

is possible if [CANTIDAD]=0, only show MPQ83LL/A ?
Only parentheses if [CANTIDAD] > 0

Thanks!!

[Swoopy]

try,

CONCATENATE(
   [PART NUMBER],
   IF(
      ISNOTBLANK([Related SIN CARGADORs][CANTIDAD]),
      CONCATENATE(
         " (", SUM([Related SIN CARGADORs][CANTIDAD]), ")"
      ),
      ""
   ),
   IF(
      IN("STOCK", [Related RMAs][STOCKONO]),
      IF(SUM([Related RMAs][CANTIDAD]) > 0,
         CONCATENATE(
            " (️", SUM([Related RMAs][CANTIDAD]), ")"
         ),
         ""
      ),
     ""
   )
)

[marinocity]

Thank you!!! @Swoopy

Only replaced

ISNOTBLANK([Related SIN CARGADORs][CANTIDAD]),

for

SUM([Related SIN CARGADORs][CANTIDAD]) > 0,

CONCATENATE(
   [PART NUMBER],
   IF(
      SUM([Related SIN CARGADORs][CANTIDAD]) > 0,
      CONCATENATE(
         " (", SUM([Related SIN CARGADORs][CANTIDAD]), ")"
      ),
      ""
   ),
   IF(
      IN("STOCK", [Related RMAs][STOCKONO]),
      IF(SUM([Related RMAs][CANTIDAD]) > 0,
         CONCATENATE(
            " (️", SUM([Related RMAs][CANTIDAD]), ")"
         ),
         ""
      ),
     ""
   )
)

works perfect!