What is recommended data structure

[Griff]

I am accustomed to working with MS Access. Extracting data in a meaningful format from parent/child linked tables is fairly easy using SQL. Therefore you would typically only transfer the Key to child tables and lookup the parent details with SQL to create meaningful reports.

I know that AppSheet is capable of doing that, but it seems to be easier to carry data from parent to child tables so spreadsheet filtering can be used. E.g. If the parent table contains order header details, like date, customer name, etc, and the child table contains line item details, it seems more practical to carry forward header details into the child table. That way a filter on the underlying spreadsheet is easy and meaningful.

However, if something in the header table changes, like a minor change to the customer name, then the filter loses value. Filtering on a non-changing Key field, on the other hand, is more accurate, but harder to do, espcially if the key is UNIQUEID(). What is the recommended approach?

[MultiTech · Accepted solution]

It can definitely be helpful to carry over some header information into the child records - but not necessarily EVERY detail from the header. (At that point, you might as well not even have the header.)

Instead of copying EVERY piece of data, only copy one’s that you know will be necessary for filtering and such later.

[Order_Type], [Project_Number] or something.

But copying the [Client_Name] into the child table is a bit much, the [Client_Link] ref connection should suffice; from that you you dereference any piece of information you might need later during reports.

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

---

Generally speaking, I won’t copy any of the parent details into the child until I get to a point where I’ll need something; then I’ll add that column into the mix. But this is rare, honestly.

The most common things I’ll carry from one level all the way down is the relevant reference links. Things like [Client_Link] and [Project_Link]; but I won’t make these ref column types, I’ll use an Enum (with a base type of Ref (set to the correct table)) - that way I can still de-ref from them, but they don’t create reverse references and eat up performance.

As long as you’ve got the reference connection in there you’ll find most instances you won’t need to duplicate data on child layers.

[Griff]

Thanks for your comments. I have been copying all the fields I think may be useful in future searches but it has been bloating the spreadsheets and slowing down the app. Your suggestions make sense.

Not 100% sure I understand your use of Enums, How do you de-ref without creating the reverse reference?

[MultiTech]

Griff:

Not 100% sure I understand your use of Enums, How do you de-ref without creating the reverse reference?

Use Enum/EnumList (Base Type: REF) to De-Reference Data & Reduce Reverse-Reference Clutter Tips & Tricks 💡

Hey Everyone. We all know the power of references inside an AppSheet app, but there’s also some overhead associated with them; and by that I’m talking about: Reverse-References These are the [Related Whatever] virtual columns that appear when you create a reference connection between two tables. These are great, essential even, allowing you to now only view your data in a clear way, but also customize the functionality around your app as well. But sometimes you don’t need the reverse refer…

[Griff]

Thanks. Looks like a neat way to keep things a little tidier and faster. Thanks