Hi all, this is my first post here. I have be...

references
(Ct Chung) #1

Hi all, this is my first post here. I have been trying out Appsheet with a few nested Excel tables with references, take data entries and app formulas to calculate the data collected.

The Appsheet formula will take data from the ‘inner-most’ table and calculate backward to the table with the highest hierarchy.

My experience is that for the data to propagate backward, each of the related table needs to be manually save else the App formula will not calculate. This will be an ‘operation’ issue if the user has to go through such steps. Am I doing something wrong? Will having the formula in the Excel spreadsheet helps instead of using Appsheet formula? Thanks in advance.

(Aleksi Alkio) #2

The Appformula will be calculated only when you open and save the record. If you need to have a field which should have recalculation in every sync, you should use virtual column instead of normal.

(Ct Chung) #3

@Aleksi_Alkio Thank you for the advise, I will try that

(Praveen Seshadri (AppSheet)) #4

Adding @Adam_Stone_AppSheet FYI

(Ct Chung) #5

This is what I wanted to do. I have a spreadsheet with two tables. First, a stock ledger table that records all in/out transaction types of a few product types. Based on the transaction types and product ID enter into the stock ledger table, I am calculating the stock position in the second Inventory table. The calculation done in the inventory table are using SUMIFS functions The problem I have now is that while I can see the correct calculated value in the Excel table when I open it in Excel. However, in the app I see the value

displayed as #value!, trying to open and save, I get [sync to compute] displayed in the app but any amount of Sync does not resolve the issue. Any help will be greatly appreciated.

(Praveen Seshadri (AppSheet)) #6

I think this is because it is Excel. Where is it stored?

If it is a Google sheet, then Google runs the spreadsheet formulas. If it is Excel, then usually, our backend needs to run the formulas and it messes up some formulas (hence the #value!).

Can you move to Google Sheets? If not, can you use Excel on Office365? We’re starting to shift this also so that Office365 in the cloud will run the spreadsheet formulas and we don’t have to do it in our backend.

(Ct Chung) #7

@praveen Hi currently the Excel file is in Dropbox, I am trying to circumvent the issue with virtual column and App formula. If I have to use similar feature, I will try to move it to Google Sheet. The reason I am using Excel is that I read somewhere that it has a higher limit on the number of cell used compare to Google Sheet. Thanks.

(Praveen Seshadri (AppSheet)) #8

Please do use Google Sheets.

Re: limits, please see help.appsheet.com - Limits on Data Size

With an AppSheet app, you can go beyond the limits of any single sheet with data partitioning as well. Limits on Data Size help.appsheet.com