Re: Show if Valif IF

SABIO_Soporte · 09-11-2019 08:21 AM

Hello there!

I have a table, where I need to create a smart filter (Valid If and Show If) in several columns, with a specific initial value (“Mes completo”) this value will be added or take it away if the sum of the values (numeric) of certain columns are equal to IF(AND([Preg6]=“S”,[Preg7]=“S”,[Preg8]=“S”,[Preg9]=“S”,[Preg10]=“S”),“Mes completo”,true). This expression is repited in several columns regarding the values of other columns. What I need is only allowed 1 value (“Mes completo”) in the hole form, and is this value can be repited in other columns show an error or not allow to save the form, beacause it must be only 1 (“Mes completo”).

I create a virtual column that controls the text values (“Mes completo”) of all the table and converted them into a number 1, so if there is any more than 1 (“Mes completo”) must shows an error in the columns where the value text is equal to (“Mes completo”).
[Mes_completo_notblank_expression]:
IFS(
[Un mes2]=“Mes completo”,1,
[Dos meses2]=“Mes completo”,1,
[Tres meses2]=“Mes completo”,1,
[Cuatro meses2]=“Mes completo”,1,
[Cinco meses2]=“Mes completo”,1,
[Seis meses2]=“Mes completo”,1,
[Siete meses2]=“Mes completo”,1,
[Ocho meses2]=“Mes completo”,1,
[Nueve meses2]=“Mes completo”,1,
[Diez meses2]=“Mes completo”,1,
[Doce meses2]=“Mes completo”,1,
[Quince meses2]=“Mes completo”,1,
[Dieciocho meses2]=“Mes completo”,1,
[Veintiún meses2]=“Mes completo”,1,
[Veinticuatro meses2]=“Mes completo”,1,
,true,0)

Then I create a second virtual column that SUM´s the number values of the expresion:
[Mes_completo_count]: SUM([Mes_completo_notblank_expression]) this is not summing [Mes_completo_notblank_expression]

And the filter condition in the columns that must enter or take away automaticaly the text value (“Mes completo”) has this expression:
Valid if: [Mes_completo_count]<2
Show if: [Mes_completo_count]<2

But all of this is not working, beacause SUM([Mes_completo_notblank_expression]) is not summing

Any ideas?

WillowMobileSys

I am not clear on where the IFS() statement is being used.

It seems you are using it to compute the value of [Mes_completo_notblank_expression]. But in an IFS(), ONE AND ONLY ONE condition will be executed. This means [Mes_completo_notblank_expression] will be either 1 or 0. And performing a SUM on it will only ever return 1 or 0.

What am I missing?

WillowMobileSys

Ok, I think I now understand what you were expecting from the IFS(). The statement doesn’t work like you are thinking.

Instead what you probably want to do is create an App formula in [Mes_completo_count] that is something like:

IF([Un mes2]=“Mes completo”,1,0)

IF([Dos meses2]=“Mes completo”,1,0)
IF([Tres meses2]=“Mes completo”,1,0)
…
IF([Veinticuatro meses2]=“Mes completo”,1,0)

You don’t need the SUM and if [Mes_completo_notblank_expression] is only used for the calculation then you can get rid of it as well.

SABIO_Soporte

Ok, and how I can tell the App if there is more than 1 “Mes completo” show me an error?

Steve

SUM(
  LIST(
    IFS(("Mes completo" = [Un mes2]), 1),
    IFS(("Mes completo" = [Dos meses2]), 1),
    IFS(("Mes completo" = [Tres meses2]), 1),
    IFS(("Mes completo" = [Cuatro meses2]), 1),
    IFS(("Mes completo" = [Cinco meses2]), 1),
    IFS(("Mes completo" = [Seis meses2]), 1),
    IFS(("Mes completo" = [Siete meses2]), 1),
    IFS(("Mes completo" = [Ocho meses2]), 1),
    IFS(("Mes completo" = [Nueve meses2]), 1),
    IFS(("Mes completo" = [Diez meses2]), 1),
    IFS(("Mes completo" = [Doce meses2]), 1),
    IFS(("Mes completo" = [Quince meses2]), 1),
    IFS(("Mes completo" = [Dieciocho meses2]), 1),
    IFS(("Mes completo" = [Veintiún meses2]), 1),
    IFS(("Mes completo" = [Veinticuatro meses2]), 1)
  )
)

WillowMobileSys

First, I just realized that in the previous post the bullet points should have been “+” (not sure how to escape the plus sign). It is a series of IF() statements all added together.

IF([Un mes2]=“Mes completo”,1,0)
‘+’ IF([Dos meses2]=“Mes completo”,1,0)
‘+’ IF([Tres meses2]=“Mes completo”,1,0)
…
‘+’ IF([Veinticuatro meses2]=“Mes completo”,1,0)

@Steve just posted a valid alternative as well

You can still inspect this count in the Valid_If section of any column where you want the message to show as you had suggested.

Steve

Enclose short code snippets in single backticks (`):

`a short snippet`

Enclose multi-line snippets between single lines containing only three backticks (```).

```
multi-line
code snippet
here
```