Skip to main content

I’ve been having a great time learning to use Airtable, and imagine I am just beginning to touch on its usefulness. So glad to see an active community of knowledgeable users who are willing to help others learn.



We are using airtable to manage weekly vegetable orders on a farm. I have three tables, one with customer information, one with items on offer, and one for order entry, and various order sorting and packing.



The things I need this base to accomplish are:


Customer management


Order management


Item management



Things to note:


We complete all orders weekly. There are no holdovers from one week to the next.


We harvest to order, so we need to know how many of each item to harvest based on orders.



It is working well with one exception. In order to calculate item totals, I have a link to the orders table that populates with all order records which contain each item. I then use a count field to count those records to know how many of that item were ordered. This works great for one week of ordering.



The problem is: I have not found a way to sort, group, or filter the items table to show total item quantities by week (fulfillment date). I need a way to remove previous orders from the total without losing the information in the orders table (where it is easy to group orders by date).



It’s basically like I need to be able to look up a specific group in a specific view which would allow me to group by date in the orders table, then sort so I only see that group in the items view. But I don’t think it works that way so I need a work around.






Hi @melissa_millsap,



Welcome to Airtable Community! :grinning_face_with_big_eyes:



You need a another table which is Order / Line Items. This table will have a list of the orders and items but in separate records. So Order 1 has 4 items, then you will have 4 records with the same order number but different items. This is linked to the Orders table and Items table.



You can also use a formula to get the week number and group by this field. You can them see the orders by week.



BR,


Mo

Hi @melissa_millsap,



Welcome to Airtable Community! :grinning_face_with_big_eyes:



You need a another table which is Order / Line Items. This table will have a list of the orders and items but in separate records. So Order 1 has 4 items, then you will have 4 records with the same order number but different items. This is linked to the Orders table and Items table.



You can also use a formula to get the week number and group by this field. You can them see the orders by week.



BR,


Mo


Hi @Mohamed_Swellam,



Thanks very much for your reply. I’m not quite understanding what the 3rd table will look like. Would each of the 4 records in your example be created individually?



Currently, I create an order by selecting a customer from a linked table, then select their items from another linked table. This makes for pretty quick order entry. I’ve added some screenshots to my original post for clarification.



I really appreciate your help.

Hi @Mohamed_Swellam,



Thanks very much for your reply. I’m not quite understanding what the 3rd table will look like. Would each of the 4 records in your example be created individually?



Currently, I create an order by selecting a customer from a linked table, then select their items from another linked table. This makes for pretty quick order entry. I’ve added some screenshots to my original post for clarification.



I really appreciate your help.


It’s my pleasure @melissa_millsap



Well, my idea would be when you create the order instead of having 1-Beet Green 2-Beet Green (im assuming the 1 & 2 are the qtys?) to enter each order item and its qty individually. Those individual records will help you group as needed.



Alternatively, in the Items table you can create a view where you group by Orders, this will show you each order individually And the items in the said order.



BR,


Mo

I still haven’t found a workable solution to this problem. I’ve had a look at a couple of templates for product and order management which use a line items table in between the orders table and the products table, but all of them require a lot more clicks to populate an order.



Currently I just select my items from a linked record field which is vey convenient, and fast for orders where customers might have 6 to 10 unique items. I setup each product with multiple items which have a defined quantity (for instance 1 lettuce, 2 lettuce, 3 lettuce), so quantity selection and item selection are the same click. I’d like to keep order population this simple.



I still cannot figure a way to get an overall count grouped by product (how many total heads of lettuce) for a given service date. This is because the item records are linked to every instance in which that item has been included in an order. HELP!! I’m so frustrated by this problem.

I still haven’t found a workable solution to this problem. I’ve had a look at a couple of templates for product and order management which use a line items table in between the orders table and the products table, but all of them require a lot more clicks to populate an order.



Currently I just select my items from a linked record field which is vey convenient, and fast for orders where customers might have 6 to 10 unique items. I setup each product with multiple items which have a defined quantity (for instance 1 lettuce, 2 lettuce, 3 lettuce), so quantity selection and item selection are the same click. I’d like to keep order population this simple.



I still cannot figure a way to get an overall count grouped by product (how many total heads of lettuce) for a given service date. This is because the item records are linked to every instance in which that item has been included in an order. HELP!! I’m so frustrated by this problem.


Hi @melissa_millsap,



Well, you can do this with a new approach.



Instead of having records for the products, make them Fields. So for each Client you will fill the fields for all the products. You will then easily rollup the qty by order date.



Let me know if you want help.



BR,


Mo

Reply


Terms & Conditions