Should different actions be placed in different columns or different rows in the same column?

AppSheet AppSheet Q&A
1

Hi.

I am creating an AppSheet to manage orders, production and sales of cheese. Currently I use Sheets to enter all of this data and create reports using the Query() function. When using Query() I can enter orders, production and sales quantities in the same column and use the WHERE clause to filter out the data I want to work with.

In AppSheet should I create a separate column for each action and each quantity? So should I create ‘qty ordered’, ‘qty produced’ and ‘qty sold’ columns OR have a single quantity column with another column for Action with ‘Ordered’, ‘produced’ and ‘sold’ entered in different rows for each record?

I am yet to create functions within AppSheet so I have difficulty visualising the best spreadsheet structure.

Thanks in advance.

Paul.

2

You would have the following tables:

  1. cheeseProducts, with columns listing intrinsic information about each product, and a basePrice column.
  2. Productions, with columns related to each production, like date, lot number, etc.
    • You should have a productionDetails table, with a Ref column to Productions table, with Is part of option checked, another Ref column towards cheeseProducts table, a Quantity column, an Expiration Date column, an Inventory column, and other columns listing the information of this productionDetails row.
  3. Orders table, with Ref to Clients table, a Ref to salesRepresentatives table, and a Status column with values like: (pending, on-hold, cancelled, processed, delivered, etc.).
    • You should have an orderDetails table with Ref to Orders table, a Ref to cheeseProducts table, Quantity, an orderPrice column, and a Ref to productionDetails column that would be filled upon a successful sale.
  4. Sales table, with relevant column listing information about the sale, with Ref to Orders table, total value column, etc. You can also add columns reflecting information about payment, deliveries, etc. Payments and Deliveries would be other tables referencing this order.
    Upon completion of a successful sale, the Ref column to productionDetails would updated, to know from which lot this sale was delivered, and the quantity would be discounted from the Inventory column in that production lot.

This would be the general setup, that you can further elaborate or simplify based on your needs.

2 Likes
3

Thanks very much Joseph.

It seems I will need to create many more tables than I would normally use in my spreadsheet.

A couple of questions:

It seems the ProductionDetails table and the OrderDetails table etc. function like a Lists! sheet in spreadsheet that is accessed via a VLookup() function. Is this correct?

Could you recommend a template similar to what I’m looking to create so I can go in and have a look around?

Thanks very much.

Paul.

4

Hi Paul,

It is not about creating lists, rather about constructing a relational database out of your sheet. Modelling your data appropriately is the way to create a well-functioning, efficient and scalable app.

I’m not very familiar with the existing templates unfortunately, sorry, perhaps other colleagues can advise better. Nevertheless, I strongly encourage you to study this guide following the links inside: Data: The Essentials - AppSheet Help.

Tell me should you face any difficulty. Thanks.

3 Likes
5

Ok - so I need to start thinking less spreadsheet and more relational database. Those links will be super helpful. Thanks Joseph.

2 Likes