Creating "Nested" Table / Records

[Matthew_H]

Hello All,

So I’m trying to replicate something I’ve done with MS Access in the past…

I’m looking to create an “AirTable” that will help me track My Vending Customers… but I seem to be having trouble with the lookups / nested table aspects.

So, I have my Customers - No problem there - simple table with Customer Name, Location, Contact Info, Etc.

I have a table for my product types, for example “Candy, Beverages, Toys, etc.”.

I have a table for my products - so a product name and then a linked field to product type, for example: “Skittles, Candy”; “M&M Plain, Candy”;“Grape Soda, Beverages”

Now essentially, I’d like to create a form that will allow me to basically replicate a manual form where I would select the customer and enter today’s “audit” date. I would then select which products are currently at the customer. Then, I need to audit each of the selections for each product they have.

For example…
Joe’s Barber Shop, Date Selection, Then I guess I’d need something like a multi-select, so I could say that they currently have “Grape Soda”, M&M Plain, etc. etc.,

Once I’ve selected each item they have, I then need to audit each of those items in Joe’s Barber Shop, so if I’ve selected that they have M&M Plain, Skittles, and Grape Soda, I need to track (for each item) the Inventory Quantity Remaining (Decimal), Amount Collected, Amount Payout, and Refill Qty (Decimal).

So at the end of the worksheet it would look something like this as a printable/lookup form
Joe’s Barber Shop on 7/9/19
Skittles: Qty Remain: 4, Collected: $2.50, Payout: $0.50, Refill Qty: 5
Grape Soda: Qty Remain: 10, Collected: $6.00, Payout: $2.00, Refill Qty: 12

I’m not sure why, but I can’t figure out how to get this to work properly in AirTables. :frowning:

[Justin_Barrett]

Welcome to the community, Matthew! :grinning_face_with_big_eyes: It sounds like your base (not your “Airtable” - Airtable is the product name; what you’re making inside of Airtable is a base) is already structured pretty well for what you want to achieve. However, Airtable’s form views aren’t designed for the kind of dynamic input you’re seeking, where you need to add an arbitrary number of product entries depending on what you encounter during your audit.

There’s possibly a way that you can achieve what you want with the help of some third party tools—like @openside’s On2Air: Forms that ties into Jotform—but you might also consider a slight tweak to your process that could be accomplished directly within Airtable.

What I suggest is making two more tables: [Audits] and [Audit Items]. The [Audit] table would contain the basic info about the audit itself: a date field, a link field to choose the customer from the [Customers] table, and a link field for choosing records from the [Audit Items] table. (The primary field could be a formula that combines the date and client, so that you have a unique value.)

The [Audit Items] table would have an {Audit} link field for connecting to a record in the [Audits] table, a {Product} link field for selecting items from the [Products] table (one item per record), and entry fields for {Qty Remain}, {Collected}, {Payout}, and {Refill Qty}. This table’s primary field can also be a formula that concatenates the audit name with the product.

When it comes time to do an audit, you’ll fill out the basics first in a new record in the [Audits] table:

To add items, click the + in the {Audit Items} field, then click “+ Add a new record” at the bottom of the dialog. This will open a dialog where you can enter the details for the item you’re auditing. The link to the audit record is already made for you, so choose the product and then enter the rest.

Once you’re done, close the record, click the + to add another new link in {Audit Items}, and do the same with the next product you’re auditing.

When you’re finished, you’ll have links in your [Audit] record pointing to all of the relevant items from that audit. To print a report, you can use the Page Designer block (which requires a Pro account, but it’s worth it) to print the record for a single audit, including all linked audit items and their details all on one page.