Creating Uniqueid for table containing merged data from multiple tables

AppSheet AppSheet Q&A
,
1

I’m building a system to manage our IT Assets. Currently, these assets are stored in 3 distinct tables: Hardware, Services, Subscriptions - and I’ve put them in 3 different tables because there are differences in the fields required.

I also have a BudgetLines table, details are pulled into each record. BudgetLines may be called into each of the 3 asset types. And there is an Academic Year table, so I can plan for this year and future years. Again, pulled into each record in each table.

Now I want to be able to pull out an overview of the budget for each year. So, for the 23-24 year, I need the Hardware, Services & Subscriptions rows which contain 23-24 in the Academic Year field.

I have managed to get this started by creating a stacked query in GoogleSheets. Which is fine, but then I don’t have a uniqueID for each record - AppSheet is using the _RowNumber and I know that’s not ideal, and if I recall correctly, it won’t use a field generated via calculation from the sheet itself.

My questions

  1. How could I go about creating this uniqueID, or should I just relax and stick with the _RowNumber field?
  2. Should I reconsider my data structure and combine everything into a single table, create an AssetType field (Hardware, Services, Subscription) and then select columns for each view depending on type to facilitate data entry?
    I think this would negate my merged table need.
2

I assume you want that overview in the app, not in the report, right? One workaround is to use “Execute an action on a set of rows” as an event action each time you add or update the budget row. That triggers the action in Overview table that calculates values from 3 different tables.

3

Thanks. I haven’t actually tried this because I ended up going with option 2 and rebuilt the whole system around one core table - which is working very well indeed.