Column Formula

Austin · 10-31-2019 12:16 PM

I have a column with a formula in it to update a status number conversion field. When I update my rows through quick edit the field updates as intended but if I update through a form, it isn’t updating the field at all. Any ideas as to why that behavior is different?

WillowMobileSys

To help identify why the values are not changing when edited in a Form, please post the expressions you have implemented in that column.

It is my understanding and experience that Quick Edit fields do not utilize the App Formula or Initial Value expressions. It is a simple straight edit. They do use Valid_If and Required_If constraints if any.

Here is an article with some other information that may help:

Steve

Curious where you got this from?

WillowMobileSys

Is it not correct? I have a number of Quick Edit fields and have noticed that in a couple, formulas I expected to be applied did not. When reading the article, it mentions the it mentions that Valid_if and Required_If are applied but does not mention the other expressions.

I am reading between the lines and interpreting that to mean that the App Formula and Initial Value expression are not intended to apply to Quick Edits.

If you feel that is incorrect, please let me know. I’ll go back and look at the fields I had trouble with and see if it was I that was doing something wrong.

Steve

It may be correct. I haven’t tinkered with QuickEdit columns so I can’t speak from experience. Was hoping for more information.

WillowMobileSys

I hate to give out wrong information, but sometimes I overreach…so I’ll double check myself.

@Austin_Lambeth Still, to understand why maybe your changes are not working as expected in the Form, it would be good for us to see your expressions in the App Formula and Initial Value properties of a column you are having trouble with.

Austin

IFS([HiringStatus]=“Unread”, “0”,[HiringStatus]=“Reviewing”, “5”, [HiringStatus]=“Screening”, “10”, [HiringStatus]=“Scheduled Interview”, “15”,[HiringStatus]=“Offer Position”, “20”, [HiringStatus]=“Hired”, “25”)

This table is UPDATEONLY for all users other than myself the admin. There is no initial value for this column. So in the form the user is able to update the HiringStatus which I need as a number for math equations elsewhere. When I edit the status in the form the HiringStatusNumber(the field that has that formula as its App Formula) stays the same. But if I edit another field, such as Overall Score, my formula edits my HiringStatusNumber to the correct value.

WillowMobileSys

I see nothing syntactically wrong with the IFS().

It seems there are 2 possible causes of the issue you are seeing (which you probably have thought of)

None of the IFS() conditions are met for whatever reason.
The change is not being properly recognized within the Form.

You can quickly rule out the former by adding a default condition to your IFS().

BUT Since it seems a change in another field later DOES update the HiringStatusNumber, I think the later is more likely.

I’m curious about your statement “UPDATEONLY for all users other than myself the admin” and how you have implemented that. Adding Security Filters, etc is unfamiliar waters for me. @Steve is probably more knowledgeable. I’m wondering if the way in which you have this implemented is contributing to your issue. Have you tried taking the Security out to see if the Form behaves normally?

Steve

I agree.

Were this the case, the IFS() expression would produce a blank value, which would erase any previous value.

Agreed.

Steve

Here’s your expression with some formatting for clarity:

IFS(
  [HiringStatus]=“Unread”,
    “0”,
  [HiringStatus]=“Reviewing”,
    “5”,
  [HiringStatus]=“Screening”,
    “10”,
  [HiringStatus]=“Scheduled Interview”,
    “15”,
  [HiringStatus]=“Offer Position”,
    “20”,
  [HiringStatus]=“Hired”,
    “25”
)

AppSheet handles the is-equal-to operator (=) in a non-intuitive way: if the left-side value is blank, the expression will be TRUE regardless the right-side value. For instance, in [HiringStatus]=“Unread”, if [HiringStatus] is blank, the expression is TRUE, even though a blank value and "Unread" are not the same. One way to avoid this behavior is to put a value you know will never be blank on the left side: “Unread”=[HiringStatus].

IFS(
  “Unread”=[HiringStatus],
    “0”,
  “Reviewing”=[HiringStatus],
    “5”,
  “Screening”=[HiringStatus],
    “10”,
  “Scheduled Interview”=[HiringStatus],
    “15”,
  “Offer Position”=[HiringStatus],
    “20”,
  “Hired”=[HiringStatus],
    “25”
)

This entire expression could instead be expressed using SWITCH():

SWITCH(
  [HiringStatus],
  “Unread”, “0”,
  “Reviewing”, “5”,
  “Screening”, “10”,
  “Scheduled Interview”, “15”,
  “Offer Position”, “20”,
  “Hired”, “25”,
  ""
)

To be clear, I don’t think anything here is the problem.

Steve

Please provide screenshots of the DATA VALIDITY, AUTO COMPUTE, and UPDATE BEHAVIOR sections of the HiringStatusNumber column configuration screen.

Austin

The table itself is update only for anyone other than myself.
The field that determines update/add/delete for a table can take a formula
SWITCH(USEREMAIL(),
“myEmail@mycompany.com”, “ALL_CHANGES”,
“UPDATE_ONLY”)

Steve

UPDATE_ONLY should be UPDATES_ONLY (plural). I don’t think this is contributing to the problem.

Austin

I typed that in manually. The actual formula had the “s”.

Austin

The only field that is cut off is reset on edit with is off.

This is the expression in the app formula column

Steve

Well, I’m nearly at a loss.

Does this mean the column value displayed in the form itself isn’t updating? Or is the form value updating but not being saved to the spreadsheet?