Skip to main content

Hello all!  I need some help please.  I can work spreadsheets all day long but database creations is not my forte, far from it.  I started using AT a few years ago to track my business whole goods by serial number.  Thankfully, someone helped me here and re-designed the mess I had created.  It has been working great so far, but I would like to change the way I enter the data.

Currently, I just use a main grid and enter all the information, it then populates to combine all the same parts together.  From there I have managed (with some help) to create 3 other tables, one for each manufacturer so I know what is in stock. 

The reason I want to change the design is I am much more busier with the business transactions and I'd like to reduce the redundancy that I find the current design has.  When I set this up, it was basic and understandable to me. 

The main table currently has model, brand, part #, serial no., accessory, vend inv, inv date, cost, status, sold to, cust inv, inv date, paid, due date.  Since I'm entering each line item, several of these data items are needing to be repeated.

My desire is to be able to enter the parts by the billing invoice on a form.  This would then populate to the main table where I can then continue to print the status of inventory on my reports.  This would eliminate the need to keep repeating the invoice #, inv date, part number (if there are multiple of the same kind), cost, due date, etc. for each line item.   I also would like to be able to have the mod# automatically populate with the part#.  Currently, I need to keep entering it.  I suppose I could have just combined them into one field when I started this back when.

I'm hoping someone can help, I've tried to explain it the best I can.  Here is a link to a sample of the database.  I may not be database savvy but with some know-how from the more knowledgeable I'm sure I can manage   😁 Thank you!!

https://airtable.com/appxYuCPWl6IcYEOC/shrlTnQYjomrdOV3F

Gareth put out a nice video on junction tables recently.  Study this, they will be your friend.


As you design your Airtable base keep this core principles in mind:



  1. Each table should only have data about the object itself.

    1. in your case you would have 1 manufacturers table

      1. you reference 3 tables one for each mfg.  combine those into a single table



    2. You will have 1 parts table that uses a linked record to the manufacturer

      1. in this table you will track parts attributes like quantity, color, manufacturer, wheel type, motor type etc.  In your sample, you have a linked record to a serial number.  If you need to track individual serial number, then that would be another table 



    3. An invoice is an object and would have it's own table.  The video I reference above uses invoices as an example.



  2. Junction Tables -- See the video to create many to many relationships 

    1. one invoice will have many line items with many non-unique items (bags of flour).

    2. unique items (with serial numbers) will be attached to one invoice.




Good design will make your data entry on your forms a lot easier. Start with these principles to redesign your tables.  I would start with a copy of my working table so you don't accidentally break it so bad and stop your business.

Where is your working table to start with?

 "I would start with a copy of my working table so you don't accidentally break it so bad and stop your business"

Do you have a template to start from or do you mean from the instructions?

I mean make a copy of your base and work on that copy to "fix it" based on the guidelines I gave above.

I mean make a copy of your base and work on that copy to "fix it" based on the guidelines I gave above.


Thank you Dan!  I've been listening to Gareth's videos and they are really good.  I think I've managed to re-work my database.  Here's what I have:

Table for equipment parts which provides the part # and description.  https://airtable.com/appdw6sxTH9BssTHJ/shroBAsPkq4TgoWKJ

Table for SN so I can attach the equipment part# to a serial number.  https://airtable.com/appdw6sxTH9BssTHJ/shrpdXoSP7LA0lZ3C

Table to enter a purchase and allow for multiple entries for one (ignore multiple manuf. on one bill) bill https://airtable.com/appdw6sxTH9BssTHJ/shrpdXoSP7LA0lZ3C 

Lastly, I have a table for when the equipment is sold to further track the serial number  https://airtable.com/appdw6sxTH9BssTHJ/shrjxmy8LV32uxHkn

As I continue Gareth's lessons I can improve, but I think I'm on the right track.  Any thoughts? 

Also, I'm stuck on how I would quickly see what is in stock.  Any advice

I appreciate your help and anyone that wants to chime in.  Thanks!! - Susan

Congrats. You sound like you are on a good path.


On your table, add a new lookup field of the same serial numbers adding a where the soldto field is empty.

Thanks for that add-on, I missed that!  

Was that meant so I can see a report about what is in stock?  Currently I had a drop down "Inventory" and "sold" so I would run a report on what is "inventory".  This scenario will no longer work.

and one more question and probably overstaying my welcome 🙂  Is it even possible to change all my data from the past 2 years to this new table?  Or is that a reach 

yes that last line was how to see what is in stock and what isn't.


 


Can you convert all your old data?  Sure.  All about time available to do it.

Reply


Terms & Conditions