May 23, 2019
Objective: Orders will automatically update stock levels accordingly. Stock levels do not require an order to change (eg, withdrawing stock for internal use, or adding stock when new stock arrives at a warehouse) ad so manual entry needs to be possible to update stock levels Products can exist without stock (stock levels) Stock movement can exist without orders (manual adjustments) but cannot exist without Products One stock movement record can only be linked to 1 order, and 1 product A graph view is required to show all products current stock QTY (thinking a graphic view in products, with a formula field that calls on info from stock movement to show the current stock levels - not sure how to calculate this though as it would need to sum all records for that SKU, and those records could be + or - ) There are 2 types of product: bags and resin. Resin is sold by the kg, and each resin product is made up of 25kg bags. Bags are sold by the bag, pack, or carton - the number of bags per pack/carton varies by product. So! Questions: Is this my best data model to achieve what I want? If so, how can I calculate current stock based on all incoming/outgoing stock movement per SKU? Keeping in mind that once this is done I want to try and create a pricing and inventory table where there are various price variations per product (retail, wholesale, distributor, custom pricing for certain customers) which orders would call on (based on select fields for pricing type and the product) and I will also be working on a quoting tool that will call on products and pricing unless its creating a new price sheet which would be determined by a select field
May 20, 2019
So I'm trying to figure out the best structure for this scenario: We have products, which we manufacture and sell. We sell these products at multiple price points (distributor, wholesale, retail, and custom price points for certain customers). I'm trying to figure out how I could have multiple price sheets, linked somehow with products. Realistically, we can get away with price sheets being a separate table and not linked in with anything, but I feel like that makes adding it to the DB redundant. If it could be linked, the idea would be that when quoting or "invoicing"* we could select the price sheet which would then populate the cost based on the product we select. I thought I almost figured it out but then it fizzled into a pile of 'not going to work'. *we do our actual invoicing in an accounting software, but setting up pseudo-invoicing would be beneficial as it could act as a packing slip as well as an invoice displaying our product by the pack or carton instead of individual unit.
May 17, 2019
In General Discussions/News
Heya I'm having a crisis. You know, one of those ones where you've recently paid for 3 paid members on airtable, and have essentially finished building your CRM, and are halfway to being done with a manufacturing-order-process... and you start to wonder, is Airtable right for this company? I love Airtable, I really do. But, I'm worried there might be a better solution for our needs out there, so I'll provide a summary of what we want to do, and we'll see what the experts think. TL;DR - I need to build: - a custom CRM with order tracking, task tracking and interaction tracking - inventory management (currently excel) - manufacturing database (create new purchase order, track all jobs, schedule jobs to different machines depending on what stage the job is in etc) - Project tracker - upcoming, current, completed, past "projects", asset manager for different components that are essentially copy and paste Features - I'd love to be able to link all the bases to the CRM, which with airtable isn't available and I have no idea if its going to happen - Document creation for manufacturing purchase orders (current solution, integromat and webmerge) - Graphs, kanbans, calanders, spreadsheet views, and the ability to filter those and create custom views - Available in China - Rich text editing would be a major plus but not required - Relatively easy to learn (I'm fine with HTML and CSS but PHP and JS aren't second nature to me, so I tend to do a lot of googling and trial and error) CRM I currently have this split into 2 bases - Contacts (industry contacts etc) and then our CRM. Contacts is simple - table for companies, contacts and interactions, all linked together. If a contact becomes a potential/actual lead/customer, they get moved to the CRM The CRM has companies, contacts, tasks and interactions - plus, I use it to track out incoming orders via woocommerce and email/phone. So that section is orders (linked to companies), product line items (linked to prouct table), shipping I enter all the order info manually - I tried a woocommerce integration via zapier, but had issues when it comes to products. I haven't looked at trying it with integromat yet because I've only just started using it a few days ago MANUFACTURING PROCESS *a manufacturing job in our company is referred to as a BLA, and can be for stock items or customer orders This is the really tricky one. Basically what I have at the moment is a set-up similar to how you would do an invoicing table, and I'm working on an integration with webmerge to create PDF 'purchase orders' so to speak. This is in a separate base so unfortunately can't link to the CRM. Where it will get really tricky is in Scheduling the jobs to be manufactured. Its currently in an excel spreadsheet, with several tables, and while the overview will be simple, I then have to figure out a way to crate the tables that show the forecast for every machine, which can then be updated with actual results, to then update the production schedule, and also show the monthly forecast as a bar graph... all I'm sure I can do with airtable. I haven't figured out how yet, but I would like to try and implement some way of checking off tasks in a BLA before it proceeds to the next step I plan on using Airtable for other things as well, but they're a lot simpler. The major things that are lacking with airtable for me are: - Linking between bases - Lacking rich-text editing - I want to create a view to each company/contact that is more reminisce of a traditional CRM in layout while retaining interactivity (so page designer isn't really an option) - The page designer is lacking in design options/flexibility so I'm having to use a third party via (webmerge) to create documents Features I love about airtable: - It's available in China, and has attachment fields, which is a big deal since all our documents are on dropbox which means we have to email things to China instead... airtable will fill in that gap for us - The different views, filtering options, and relative ease for me to learn - Aesthetically, it's pretty nice compared to other options I've seen - All those other little things that make Airtable great I'll also be needing to onboard staff members that aren't the most tech savvy, and I'm wondering if there's a solution out there that will be easier for them to navigate. In saying that, they're all reasonably comfortable with excel So there's my massive, messy brain dump. Honestly, if airtable is going to be my best option, I'm more than happy to continue with it. I need to put together a cost report, and considering how much time this custom DB is going to save us, I may be able to justify additional services that will fill in the gaps for airtable. I just need to be sure it is the best option, before I spend too much more time on it.