Re: How to clear a value via action

Marc_Dillon · 04-25-2019 04:40 PM

Does Appsheet have a null/blank/void value that we can set a field to? Like with a “set value of column” action. Or is there any way to clear the value from a column, so that it identifies as true in an isblank() expression?

LeventK

@Marc_Dillon
Initial Value is for that purpose. Provided you don’t stated any value there, the field is always blank and an ISBLANK(…) expression returns TRUE

Aleksi

Have you tried to write CONCATENATE("") with your action?

Steve

Or just "".

Marc_Dillon

I currently have the action putting " ". The problem with " " in this case is that it is a ref column, also with a valid if expression that sorts the ref values. By itself being a ref column is fine, but with the valid if, it doesn’t allow the action to run. I guess I can create a “blank” ref record. Maybe a feature request for a delete value action is in order?

Jonathon

Hey Marc, try returning a blank list?

ANY(LIST())

Or as an if:

IF(CONDITION(),
"Prefilled value",
ANY(LIST())
)

Marc_Dillon

I like that better than a blank string. Not sure if it’ll get past the valid if condition though.

Steve

or:

IFS(condition, prefilled-value)

Steve

What does this mean? Is the Ref column value Required? That’s more likely to prevent the action than Valid_If, I’d think. I don’t even think Valid_If is applied to actions.

Marc_Dillon

@Steve

The column is not required, but it does have a valid_if expression, thus a blank/void/null value, or value deletion, would work, but a random value such as the blank string " " doesn’t work because it doesn’t meet validation requirements. Does that make sense?

Valid_if does apply to actions, I’ve actually encountered it twice this week on two separate projects.

By the way the valid_if expression is just an ORDERBY() on the ref’d table.

Steve

Note that when I suggested "", that’s two double-quotes with nothing between.

Marc_Dillon

Maybe this is possible by putting the ORDERBY() expression in suggested values field instead of valid_if field?

Steve

It is possible to use ORDERBY() for suggested values.

David_Ackling-J

I am having the same issue. I need to ensure that a ref column PaymentRequest never has a value in it, when the [Billable] column is not true.

What works is to say ORDERBY(IF(NOT([Billable]),SELECT(PaymentRequest[ClaimReference],[ID]="++++++++"),SELECT(PaymentRequest[Claim Reference],[ID]=[_THISROW].[ID])),[LastModified],true)

It adds to my sync time terribly, though.

Help.

Koichi_Tsuji

Billiable should be boolean, in appsheet, YES/NO type.

NOT([Billiable]) expression is just toggle yes to no, no to yes.

Expression should be corrected like

ORDERBY(IF([Billable]=false,SELECT(PaymentRequest[ClaimReference],[ID]="++++++++"),SELECT(PaymentRequest[Claim Reference],[ID]=[_THISROW].[ID])),[LastModified],true)

David_Ackling-J

Hey Tsuji,

NOT([Billable]) works fine, and your [Billable]=FALSE is fine, too.

The expression above works with:
ORDERBY(IF(NOT([Billable]),SELECT(PaymentRequest[ClaimReference],[ID]=“11111111”),SELECT(PaymentRequest[Claim Reference],[ID]=[_THISROW].[ID])),[LastModified],true)

The “++++++++” key threw up an error.

My issue is that running a SELECT query on the PaymentRequest table is a big job, as it is 11,000 rows plus. It should be possible to say ORDERBY(IF(NOT([Billable]), MakeNull([_THIS]), …

Koichi_Tsuji

When using orderby expression in Appsheet, we need to make sure the expression inside orderby() will return the list of key value.
Im not sure but it seems PaymentRequest table key should be [ID] instead of [Claim Reference]?

Steve

This will produce an empty list if [Billable] is FALSE, prohibiting all input in the column, or the ordered list of things otherwise. The ORDERBY() expression won’t even be processed if [Billable] is FALSE.

IFS(
  [Billable],
    ORDERBY(...)
)

David_Ackling-J

LEGEND!!
I did not know that about the IFS function.
That’s an awesome bit of know-how, thanks Steve!
There is no need to provide a second outcome, and it defaults to null rather than throwing an error.
Wow.
Thank you.

Steve

For reference:

sandeepraon

hi , i need to set a field value to be blank using an action.

what has been deemed to be solution for this. i dont see any suggestion on this to be marked as a solution. .

any help please who have sorted the solution for this.

shall i be using "" will that work? when in other actions i have IF condition with ISBLANK(field) .will all that work with ""?