With the new UI, I can't figure out how to en...

(Phil Robertson) #1

With the new UI, I can’t figure out how to enter a “Spreadsheet formula” in the Auto Compute section.

My existing ones were ported over, but the field seems to be missing?

Please advise. Thanks!

(Dinh Nguyen Nguyen) #2

Adding @praveen to investigate.

(Praveen Seshadri (AppSheet)) #3

Hi @Phil_Robertson this is by design. Spreadsheet formulas are defined in the spreadsheet, not via the app editor. APpSheet will pick them up when you add a table or regenerate a column structure.

So if there is a spreadsheet formula, the app editor shows it. In fact, it should not be editable, but there are some cases where we extract the spreadsheet formula wrong and it needs to be manually corrected.

Hope this explains

(Phil Robertson) #4

@praveen There’s a piece of the puzzle missing for me here.

AppSheet will add a new row to the spreadsheet with each new record, so if I want it to pick-up a formula where do I locate it in the spreadsheet?

I just attempted to put a formula in the previous row (i.e. most recently created record) and then do a regeneration of the table structure but I don’t see a formula populated in the AUTO COMPUTE section. Conceptually, locating formulas in the sheet still doesn’t make sense to me because it’s creating a dependency between previous records and new records.

Unless I’m missing something?

EDIT: I copied the formula to all existing rows in the column.

I then regenerated the table structure.

I still don’t see formula in the AUTO COMPUTE section of the table columns.

Please advise how I do this?

(Praveen Seshadri (AppSheet)) #5

If the formula is in all the rows and you regenerate, you should see the formula picked up automatically. Check your Info pane for warnings. Most likely, the formula is not one that AppSheet will accept because it uses positions.

From AppSheet’s point of view, a column’s value can be assigned in one of three ways:

a) it is added and edited by the app end user

b) if it automatically computed by an App Formula (Virtual columns are one instance of this)

c) it is in the data tier (i.e in Google Sheets or Excel or SQL etc) or it is computed by AppSheet.

In case ©, AppSheet will just treat this as a read-only column. However, when inserting a new row, it needs to know what formula to put back into it, so that’s why it detects and extracts the spreadsheet formula. AppSheet makes no guarantees of the row position where a new row is going to be added. Therefore, any spreadsheet formula that is based on position will be ignored.

(Phil Robertson) #6

@praveen I did have a warning on the info pane–inconsistent formula, seems I missed a row!

It’s working now, thanks!