Initial value of blank

[Chris_Jeal]

Hi,
I wonder if you can set a column to reset to it’s initial value of blank, so basically when another column’s value matches the formula then the cell is reset to be empty.?

Thanks in advance,
Chris

[Mike_A]

Just off the top of my head - If the column you want to set to blank is an calculated column and you can accept that blank is the same as “”, then a formula could do this. If the column you want to be blank is not calculated (user filled), the. You might have to create an action to do this (overwrite current value).

[Chris_Jeal]

Think the creating an action route may be best, will give it a shot…

Thanks very much.
Chris

[LeventK]

@Chris_Jeal
Concur with @Mike here. You can set an initial value with IFS:

IFS(
[ColumnA]="SomeValue","Show This",
TRUE,CONCATENATE("")
)

If you also set that this column value is resetted with every edit, the IFS expression will be re-evaluated on each edit.

[Steve · Accepted solution]

@LeventK’s IFS() is equivalent to:

IF(
[ColumnA]="SomeValue","Show This",
""
)

or:

IFS(
[ColumnA]="SomeValue","Show This"
)

[Uribe]

Hello Steve, 

I use this in a calculated column

IF(
[ColumnA]="SomeValue","Show This",
""
)

The "" in the formula writes a zero, but I need it to be blank. How can I do it?

[Steve]

Please post a screenshot of the column's configuration screen.

[Uribe]

Hope this is what you mean

[Steve]

Thanks, that is in fact what I meant. I don't see any problems with it. Now, please post a screenshot of the entire App formula expression.

[Uribe]

Here it is:

IF([Tipo de planilla]="Regular",
IF([Horas trabajadas]>8, [Horas trabajadas]-8, ""),
[Horas trabajadas]
)

[Steve]

Try this instead:

IF(
  [Tipo de planilla]="Regular",
  IF(
    [Horas trabajadas]>8,
    [Horas trabajadas]-8,
    DECIMAL("")
  ),
  [Horas trabajadas]
)

[Uribe]

It works! thank you!

[Uribe]

Hello again. 

I use DECIMAL("") with other formulas. It works fine, but I'm wondering if this way is not efficient causing the app to encrease sync time, because it has to evaluate the formula on the first argument and check the result against the <>0 condition and according to that result perform the second or third argument. Here is an example:

IF(
(SUM(SELECT(Asistencia[Salario bruto ordinario],
AND([_THISROW].[Empresa]=[Empresa],
[_THISROW].[Empresa planilla]=[Empresa planilla],
[_THISROW].[Fecha desde]<=[Fecha],
[_THISROW].[Fecha hasta]>=[Fecha],
[_THISROW].[Nombre]=[Nombre],
ISBLANK([Planilla pagada])
)))+(([_THISROW].[Salario mensual])/2))<>0,

SUM(SELECT(Asistencia[Salario bruto ordinario],
AND([_THISROW].[Empresa]=[Empresa],
[_THISROW].[Empresa planilla]=[Empresa planilla],
[_THISROW].[Fecha desde]<=[Fecha],
[_THISROW].[Fecha hasta]>=[Fecha],
[_THISROW].[Nombre]=[Nombre],
ISBLANK([Planilla pagada])
)))+(([_THISROW].[Salario mensual])/2),

DECIMAL(""))

[Mike_A]

@LeventK - love the idea of considering “reset on edit”. Will have to explore more use cases for that new feature.

[Chris_Jeal]

Ok,
So I have managed to get the desired result by creating behaviors for all of the actions I needed completing.

I created 3 separate behaviors and then one to complete these 3.

Sometimes, the simple answers are the most effective.

Thanks chaps for your help and suggestions, got there in the end.

Chris.

[Mike_A]

@Chris_Jeal - glad you were able to solve it! Keeping it simple is good.