Action Failed -Column is Invalid

[Resound_FB]

Need help here. I've got a column that gives a number of family members (in the example "Adults Aged 17-24?") that I need to be editable, but populates with an initial value that is taken from another column (which uses a formula on the spreadsheet, so can't edit that actual column). But I am getting an Action Failed message saying column is invalid whenever I try to save the form. The Initial Value is set to the column on the spreadsheet with the original formula, and I have set the Reset on Edit to On, so that it populates with the Initial Value on opening the form (don't know why Initial Value doesn't populate without the reset on edit, but that's a different issue). 

The error comes up whether or not the value is changed in the form.

Don't get the error if I set no Initial Value for the column, but that doesn't work for this situation, as I need the starting value to populate based on another spreadsheet input.

Also, don't get the error if I turn off Reset on Edit, but that also doesn't work since that sets the initial value at 0 in the form, even if there is a different value established by the other spreadsheet input.

Worth noting, that the changes actually save to my spreadsheet, so the data is doing what it is supposed to, but my users are getting the confusing message that the Action Failed.

EDIT: Just discovered that because the Action Failed it is not running my Event Action On Save, which should enter a TRUE in Column "Interview Complete".  If I set On Save Event Action to *Auto, I do not get the error (but it also doesn't run the needed action). I can't understand code wise how a Column that is completely unrelated to the Action is causing the Action to fail...

The Action that is failing is interacting with an entirely unrelated Column.

Form runs action on save.

[Marc_Dillon]

Some of your wording is hinting that you may be manipulating your data outside of Appsheet in some way. Can you be more clear here? Are your records being created outside of Appsheet, and then just being edited by the app?

The error message indicates something is invalid. Are you using any valid_if expressions? Invalid data can also be due to data type mismatches (like storing a text value in a Number column), which is something that can often occur when data is added by an outside source instead of through the app.

The Initial Value that you showed is setting the value of the same column to be its own value. This is an entirely pointless and meaningless thing to do. What is the intention here?

[Resound_FB]

Hi Marc

Yes, some initial data comes in from another office via the Google sheet that is the data source. So some data is edited by app and other added - in this case the invalid column is populated  from the other office, but sometimes needs to be edited via the app (eg if head office input wrong data).

No valid_if expressions, Column is set to Number type and only Numbers are input (in fact, using the up-down counter in form).

Initial Value is misleading: Column causing invalid notification is "Adults Aged 17-24?" (with a question mark), the Initial Value is populated from head office column of similar name but with out "?" in the title.

[Resound_FB · Accepted solution]

I HAVE solved the issue by writing an App Script that posts the initial value in the correct cell as a value instead of a formula.

But that doesn't solve the issue within the App Sheet for others who may run into this problem.

[Marc_Dillon]

I can answer one of your questions at least. "why doesn't Initial Value work without ResetOnEdit?" Because Initial Value only evaluates when a record is first created, and you're editing existing records.

Oh yes, I missed the additional question mark. What is the column type of the original, non-question-mark, column?

What are these formulas that you're referring to? Are they spreadsheet formulas? Does the app know about them (in that columns "Spreadsheet formula" config field)? How is the value getting put into the cell different between the formula method, and the app script method?

Since you're running a data change action when you save the form, this essentially counts as another edit, which would also trigger the Initial Value evaluation to re-run, because you have ResetOnEdit turned on. So, even if it did work (i.e. didn't encounter the invalid error), any manual changes to that column would just get wiped out again by resetting it back to the original value. So you probably need to change your strategy here in a more significant way.