Duplicate data across many sheets... Excel = or AppSheet Ref_Rows / Select

[ghost5]

Hi,

My app will be going live for around 40 users in 2 weeks' time so I have time to tweak it.  Currently, the app has been tested with 3 people and it works well apart from the speed that it loads (17 sec) and writes data (up to 10 sec). The app is for field equipment calibration, location tracking and self-tests.  It has been built in excel and lives on SharePoint.  The users must reload the app every time to make sure they have the most up-to-date information.

The app has 7 tables all in the same workbook and each table has 2000 rows with the same ID [Asset Number] and only allows updates.  In total there are 221 columns.

So that the user sees useful 'worded' information instead of just the asset number in each view, my excel table contains a lot of formulas like =IF('Equipment Register'!C2="","",'Equipment Register'!C2) so much so that currently the excel file is 1.7mb in size but if I remove these formulas it is only 1mb.

My question is, should I continue with the sheet as it is or should I remove these formulas in excel and create virtual columns in AppSheet using ref_rows or SELECT(Order Details[Order Details ID], ([_THISROW] = [Order ID]))  Am I likely to gain speed increases by using virtual columns?

I have previously rebuilt the app using only one table and slices, but due to the way AppSheet updates whole rows rather than cells, it constantly broke formulas in excel and became very hard to manage.

Thanks,

[ghost5]

I have tried various lookup and select formulas on a test copy of the app but AppSheet is always expecting the resulting data to be a list type whereas the values that I want back are various hyperlinks, images, text, dates or numbers.  I am a bit stuck with trying out the virtual columns.

I am trying formulas such as LOOKUP(Asset number,Condition,asset number,condition image) and SELECT(Condition[Days Since Last Equipment Check], ([_THISROW] = [Asset number]))

Does anyone have any ideas where I am going wrong, with what in excel is a simple this cell = this cell on this other sheet formula?

Thanks,

[dbaum]

I don't understand much of what you've set up so far. However, it does sound atypical. With regard to the following point, be sure that you're leveraging the References between tables - AppSheet Help and Add row labels - AppSheet Help features. For example, if you indeed need multiple tables each with the Asset Number as the key column, ensure that in all but one table that column is a Ref type referencing one table that serves as the source for the others.

---

@ghost5 wrote:

So that the user sees useful 'worded' information instead of just the asset number in each view,

---

[ghost5 · Accepted solution]

Thanks.  You sent me down a rabbit hole with your first link but what I ended up using was the lookup function.  To answer my original question, yes it did speed up syncing (12 seconds compared to 22 but sharepoint is being very slow tonight), but in the end the way I have set up my app with different views being filtered, it broke too much.  Maybe I could tweak my app a little with some virtual lookups in the future.  I could not use any reference or select formulas as it would always pull the data through as a ref type, which broke a lot of formatting.