Toggle On/Off appformula on physical column

[Koichi_Tsuji]

I have been struggling to come up with the expression to kick into App formula for physical column (not VC).

I wish to run the expression when the other column in the same row meets a criteria. Otherwise the app formula/expression will be basically ignored. Meaning the app user can enter the value intot this particular column as if expression in app formula section is left blank.

For instance, pushing expression to run calculation on the number type field. I place some sort of if/ifs expression to fire formula when Col 1 value = A, otherwise this formula is not to be fired, then the app users are asked to place the number manually into this field.

IF A = B, then C, else D. sort of expression is supposed to this job, but apppsheet always “else” value need to be matching with value C . I just thought there is a way to place “null” sort of value into elseif part of the expression, like “”, or “ “”, but those are recognized as text or number value by appsheet, i.e. this formula stay NOT editable, rather than app user can enter users.

In short, I m looking for a solution to toggle On/Off appformula on physical column.

[Bahbus]

I haven’t tried this explicitly, but what about using Suggested Values? It’s not a perfect solution, but hear me out on it. It requires a list to be returned, but it doesn’t matter what the size is. So you can set up a SWITCH to return various “lists” of possible selections. In the case that there will always be one option, you can also set the Initial Value using ANY or INDEX (which I think will work correctly). And when the list is zero, it should still allow for any other input.

[Koichi_Tsuji]

Thank you for your comment.
I m trying to test if it would fit to my case, but could you eraborate a bit more in terms of exact expressions and its combinations ?
thank you for your time.

[Bahbus]

In Suggested Values:

SWITCH([Column],
value1, LIST(results1),
value2, LIST(results2),
value3, LIST(results3),
LIST())

OR

IFS(
[Column]=value1, LIST(results1),
[Column]=value2, LIST(results2),
[Column]=value3, LIST(results3)
)

And then, optionally, if each of those results lists are one item, you can try setting up Initial Value (this is the part I’m least sure about working reliably):

ANY(
SWITCH([Column],
value1, LIST(results1),
value2, LIST(results2),
value3, LIST(results3),
LIST())
)

OR

ANY(IFS(
[Column]=value1, LIST(results1),
[Column]=value2, LIST(results2),
[Column]=value3, LIST(results3)
))

[Koichi_Tsuji]

Thank you @Bahbus

Actually what I m trying to achive is far more simple, but not able to find out a solution.

Let me explain with simple use case.

[Field 1] - Enum type
[Field 2]

[Field 2] is dependent to [Field 1] . For this Field 2, I wish to run the formula based on Field 1 value conditionally. For instance, When Field 1 is “A” then app formula is run and calculate the number, and app user is NOT able to edit the value here.

In case Field 1 is “B” value, then user can put any value (not enum or enum list) explicitly, meatning the Field 2 is “editable”, app formula is gone.

[Bahbus]

Suggested Values:
IFS(“A”=[Field 1], LIST(Your formula that calculates))
Initial Value:
ANY(IFS(“A”=[Field 1], LIST(Your formula that calculates)))
Editable If:
“B”=[Field 1]

[Koichi_Tsuji]

Thank you @Bahbus

It is close to what I aim, but I want to avoid the data entry throught the “list”, but just let the user input as normal entry field when the field is editable.

I may need to seek workaround, possibly with conventional type of Action + Workflow solution although it is not ideal.

[Bahbus]

That’s the closest way to do it without using actions or workflows. For now.

[Koichi_Tsuji]

thank you Buddie, taking your time. I appreciate.

[Bahbus]

Actually, try:

Initial Value:
IF(“A”=[Field 1], Your formula that calculates, “”)
Editable If:
“B”=[Field 1]
Reset On Edit:
“A”=[Field 1]

[Koichi_Tsuji]

Thank you , this is what i initially tried actually. Aim is to place formula to app formula section to toggle on off.

[Ami]

Can your app formula contain an if? So you use a column and an action to populate the column, only if the column is populated the formula would calculate

[Koichi_Tsuji]

that s not making sense to me, thank you.

[Suvrutt_Gurjar]

Hi @tsuji_koichi,

You may wish to consider following workaround, a bit longish.

As per my understanding, a column may not be simultaneously user editable if it has an app formula.

So the work around has 2 columns for [Field 2], say [Field 2 A] and [Field 2] . For app user the [Field 2A] is not visible ( It needs to be made hidden). The user sees only [Field 2] and experiences the app behaviour exactly as you have described.

Following are settings and app formulas for these two fields.

[Field 2A] Settings

Show If" Off (Field is hidden) , Editable : On

App Formula IFS([Field 1]=“A”, NUMBER(DAY(TODAY())),[Field 1]=“B”,NUMBER(""))

The number formula highlighted is the formula that you wish to have when the [Field 1] =“A”

[Field 2] Settings
Show_If : On , Editable setting has formula of [Field 1]= “B”

It has NO app formula but Initial Value formula of

IFS([Field 1]=“A”, [Field 2A], [Field 1]=“B”,NUMBER(""))

The app back end will capture both [Field 2A] and [Field 2] values. But [Field 2] contains final values for that record. So you please ignore [Field 2A] values for further use and calculation in your app. Simply use [Field 2] back end values.

[Steve]

There is no way to make a column with an App formula editable under any circumstances.

The only way to make a column conditionally editable and computable is a combination of Editable?, Reset on edit?, and Initial value expressions. The limitation, though, is that once the user manually modifies the column value, the computation stops and will not resume without reopening the form.

Consider making a user-editable column (Field 3?) separate from the column with the computed value (Field 2). Using Show? expressions, display the computed column only when "A" = [Field 1], and display the user-editable column only when "B" = [Field 1]. The computed column could set itself to the value of the user-modifiable column if "B" = [Field 1].

[Koichi_Tsuji]

@Steve
@Suvrutt_Gurjar

Thank you for your comments and suggestions.
I have been thinking around to find a way, basically to “make the app formula NULL AND VOID” as it the app formula section left blank on a certain conditions, but looks there is no possible workaround for now to achieve the exact goal for now.

Will refer to your suggestions to implement function through the different approach.