Conditional Statement to Set Column Value - Expres...

mykrobinson · 08-15-2022 07:24 AM

I have a field called "Will Not See" where a provider marks why they may not be seeing a patient on their rounding list for the day. I have subsequent fields, [Follow Up], and [No Follow Up Reason], and i would like to automatically set those values depending on the current value of [Will Not See].

I tried this in the Auto Compute section of [Follow Up]

IF(
[Will Not See]="CMO/Hospice",
AND("No Follow Up",[No Follow Up Reason]="CMO/Hospice"),
[Follow Up])

and this seems fine logically to me (could be wrong) but its failing because its expecting a yes/no condition.
If i remove the AND statement, it at least passes validation, but i want to set the value of two columns and not just one 🤔

IF(
[Will Not See]="CMO/Hospice",
"No Follow Up",
[Follow Up])

what am i doing wrong here?

mykrobinson

I'm also finding that if i place anything in that App Formula, it seems to completely remove the ability to manually set anything there and it no longer shows the column in the detail view with quick edit, so i'm back to square one. How do i go about achieving what i'm looking to do?

Steve

AND() - AppSheet Help

mykrobinson

I am understanding the error in the AD() statement in this context, but now i'm stuck on how to implement what i am trying to do here 🤔

Played with the idea of the Initial Value with with reset option enabled, but it is not working as i expected. Guess this may be due to Quick Edit but i've been wrong before.

So what's the recommended method to achieve a field that can dynamically update itself based on the content of another column, but also allow user input?

Steve

Reset on edit? does not work with Quick Edit columns.

Because the row already exists, I don't really see a way to do what you want using Quick Edit.

mykrobinson

assuming we remove Quick Edit and instead force the users to just Edit the row (to me, looks like the interface would be the same, just adds one more click... they'll live)

Is it then possible to do what i am looking for and automatically set a value based on other selections but BEFORE saving the record? Hope i am wording that correctly.

mykrobinson

just talking out loud but i have stumbled across another issue. Even in trying to set some type of "auto compute" formula that is accepted ,it immediately breaks the app because i have other automation that sets the value of [No Follow Up Reason] upon patient discharge.

Back to the drawing board. Hoping that AppSheet has the means to set values dynamically but kind of "on demand" based on user choices. I really feel like i need to take what i have learned and start over on this app.

I don't regret somewhat being forced to make "something" but the more i learn, the more i realize this project could have and still can benefit from a lot more time spent in the planning phase as i learn what AppSheet can and cannot do.

Steve

@mykrobinson wrote:
Is it then possible to do what i am looking for and automatically set a value based on other selections but BEFORE saving the record?

Yes. When not using Quick Edit, Reset on edit? will be considered. If Reset on edit? is ON or evaluates to TRUE, the column's Initial value expression will be applied normally.

mykrobinson

any commentary on the auto compute versus automation issue? I feel like i'm half way to figuring this out, but its that last half that'll get ya 😂

mykrobinson

UPDATE: I got the conditional toggle working after purging the automation. I think i may need another column that is hidden to control the reset in the conditional statement. Just spitballing and thinking out loud, but i am thinking back to a lesson i did on Friday on AppSheet Training where they used a column that incremented a number based on a condition. that number was not visible to the end user but the condition of that number before and after controlled another parameter. Perhaps i have to think "around" the problem in this manner and approach it indirectly 🤔

mykrobinson

assuming you can see this quick video, here's what i'm currently up against:

https://drive.google.com/file/d/1LZZagxVAKVI48QGJRKCjwl0BH7ugaMNy/view?usp=sharing

Steve

There's no reason you can't use both. The setting of the column value by Automation will override Reset on edit? and prevent it from taking effect.

mykrobinson

interesting, because it crashes the app and says that the automation can no longer set the value of the column.

Also, in looking at my video, i think that problem is because one the end user touches a parameter manually, it is no longer an "initial value" and likely no longer able to be affected by what i have put into the "initial value" dialog.

Though now i am curious to go back and add the automation and see if I can get this to work. perhaps i'm just missing something.

Steve

@mykrobinson wrote:
I don't regret somewhat being forced to make "something" but the more i learn, the more i realize this project could have and still can benefit from a lot more time spent in the planning phase as i learn what AppSheet can and cannot do.

I can't tell you how many times I've restarted projects for exactly that reason.

Steve

@mykrobinson wrote:
interesting, because it crashes the app and says that the automation can no longer set the value of the column.

Make sure the column does not have an App formula configured.

mykrobinson

i think that is where i am getting confused. Humor me and let's talk this through.

i want the target column to dynamically change based on the selection made in another column, but also allow the end user to manually set things if the selection made in the other column does not specify the behavior. For example, if A=1 then B=2, but if A<>1, allow the user to set B to whatever they choose.

This somewhat works if i make my condition in the Initial value space, but if you're able to see my video i linked, the initial value method breaks if the end user manually changes the target column value, theoretically because one user input is detected, it is no longer in the "initial value" state. Seems like i somehow need a combination of app formula and initial value with some flexibility.

Am i asking too much or and I just approaching this wrong?

Steve

Once the user interacts with a column value, Initial value is no longer applied. There is no way to restore it without reentering the form view.

Conditional Statement to Set Column Value - Expression Assistance