Welcome to the 3 Rings Community!
In this thread, you can ask questions and discuss the inventory template. If you ask a question, you’ll hear from me. Download the template below.
The template:
The Video:
1 Like
Don’t be shy… ask your question here!
Hi Julian. Can you show us the formula to add the date received automatically onto the forms connected to gallery cards. I have tried a few variations and I am clearly getting something wrong
Hi Lloyd! Welcome to the forum. Passing dates in a form can be a little tricky because of time zones. A simple way to prefill the Date Received would be to:
-
In the Products table, create a new formula field called “Today”, and enter the formula
Today()
-
Add a prefill to your form URL that says that Date Received is Today, referencing your new Today field. If you’re adding it to the one we created in the video, it would look like this (but make sure to use your own form URL at the beginning):
'https://airtable.com/shros3KUvegxUoIqn'&'?prefill_Product%20ID='&RECORD_ID()&'&prefill_Date%20Received='&{Today}
However! Airtable forms accept dates and times in the GMT time Zone. So if I used this method (I’m in New York in the U.S.), the date is going to be one day off because I entered 12am on 7/5/2023 in GMT, which is actually 8pm on 7/4/2023 in EST. Anyway that’s very confusing, but here’s how to solve the time zone issue:
- Create a formula field called “Time Zone Offset” and use this formula:
DATETIME_DIFF( DATETIME_PARSE(DATETIME_FORMAT(TODAY(),'MM/DD/YYYY h:mma'),'MM/DD/YYYY h:mma'), DATETIME_PARSE(DATETIME_FORMAT(SET_TIMEZONE(TODAY(),'America/New_York'),'MM/DD/YYYY h:mma'),'MM/DD/YYYY h:mma'), 'hours')
If your time zone is different than mine, replace ‘America/New York’ with your time zone name from this list
- Create another formula field called “Today with Offset” and use this formula:
DATEADD(Today,{Time Zone Offset},'hours')
- Update your prefilled form URL to use the offset time:
'https://airtable.com/shros3KUvegxUoIqn'&'?prefill_Product%20ID='&RECORD_ID()&'&prefill_Date%20Received='&{Today with Offset}
Voila! It’s annoying to set up, but hopefully will save you lots of time filling out forms 
I created an example base here that you can copy to see all of this in action.
Thanks Julian. I will give this a go tomorrow and let you know how I get on.
1 Like
Hi There,
If I want also to add information regarding the owner, meaning that each person on our team has access to ordering and selling an item. I would like to have a view that enables me to see how much inventory each person owners. and be able to track in the total quantities of all of my items (by owner). Can either call it owner or location.
Thanks in advance
also another element I would like to add is an order request. Any one from our team will be able to access a form that will have a drop down of all of our items and click on what he would like to order and it will be assigned to the specific owner that is in charge of placing an order for that item
Hi Mai! Welcome to the community forum. I created a video on how to track inventory for multiple stores, or locations, which is very similar to what you’re asking. You can watch it here. But also, to briefly summarize the steps:
- Create a new table called “Owners”
- Link the “Received table to Owners”
- Link the “Sold” table to “Owners”
- When you fill out received and sold information, use the new links to specify which owner is receiving or selling an item
- In the “Products” table, create an “Owner 1 Received” field, which is a rollup type
- Set it up to sum the values, just like we did in the original video, but toggle the switch that says “Only include records that meet certain conditions”. You can see how I did that here for multiple store locations, and then picked a specific Store. For you, this would be a specific Owner.
- Create an “Owner 1 Sold” field and repeat steps above.
- Create “Owner 1 In Stock” field, which subtracts sold from received
You can use the same forms that we created in the original inventory video for Received and Sold, but just add the “Owner” field as a required field.
hi!
Thanks a lot for all your great tips! I was wondering how you would tackle the following. For the forms you would have to submit a new form for every single item you have sold or purchased but often you would collectively order a bunch of items from the same store… or report on sold items (we usually sell a lot of items on the same day). is there any option to do a drop-down and add multiple items that were sold in a single form?
No prob!
Unfortunately, Airtable doesn’t have a feature that allows this with a form. To set up ordering of multiple items, think about an invoice or an order receipt that you get when you buy something online. You have the order itself, which usually has an order number, and you have line items, such as 2 t-shirts and 3 pairs of pants. In Airtable, the “Line Items” would be your existing “Sold” table. To place orders with multiple line items, you need a new “Order” table that links to the line items. This video shows how you might set up that ordering table
BUT - Because Airtable forms just create a record in one table, you can’t have a form that creates an order AND line items. This is a limitation that I hope they overcome soon. Airtable interfaces are getting good enough that you can set up a more intuitive ordering system there, but unlike a form it requires all people submitting an order to be logged in. This is what an interface for placing orders might look like:
In this interface, it is showing an order from the order table, but also a grid of line items that are linked to the order
Thanks for all of the super helpful information!
My last question is this:
I created another section in the flow that is called Requested items where all our employees can click on the form to request a new item. Second I created an owner tab as you suggested as well as the location so we can know who is in charge of ordering a specific item (owner) and who possesses an item (location). What I did want to ask you is that we have specific owners for each item on the list and potentially I would like to assign the owner and alert that a new request form has been submitted using automation. I wasn’t able to find a good way to do so and I wanted to ask if you have any suggestions.
Thanks in advance
Absolutely, this is doable. Since this is really a question about automation, would you paste this question into a new topic and also include a couple screenshots? That way others who have a similar question will be able to find it. To do that, just go to the home page and click the button that says “New Topic” (on the right in the screenshot below)
Hi Julian one last question!
On your gallery view, I also reflected the in-stock quantity. Now I would like to show where is the location for that specific item (I don’t care about showing the numbers of how much stock each location has although it would be nice to have it if it doesn’t create an overload) But I would like to show a reflection of where you can find the stock. My question is how do you reflect that as I have received quantity and linked it to location and sold records that are linked to sold records but I can’t create a formula that subtracts in-stock location? Do you have any suggestions for a rule or a workaround for that?
Hi Mai! Here’s one way to do it:
Create a rollup field for received quantity in the Products table that is filtered to a specific location, like this:
Then a sold quantity rollup filtered the same way:
Then an In Stock formula for that location which subtracts the sold from the received:
You can repeat those steps for all locations. Then surface just the “In Stock” fields in the gallery like this:
If you wanted to have one field which just lists the locations with stock of an item, write a formula that uses if statements to check if each location is in stock:
Then surface that field in the gallery:
Here’s the example base I used
Hi Julian,
Thanks for all the super helpful information! I am about to finish my dashboard and wanted to ask a final thing.
I created a total of 3 tabs:
- Teams requests - has a form to fill a new item request
- Received items - has 2 forms: restock form and leftovers form (for extra items that were not fulfilled at one of our events)
- Distributed items - includes a distribution form to report when an owner send items from stock following a team request or an event that is coming up.
As you can see the situation is the following:
An employee sends a request via form - > the owner orders items and restocks (quantities go up) → the owner distributes items as necessary to the request (quantities go down) → the employee or the event owner has leftover items post-event. He sends back the items to stock (quantities go up again).
I wanted to ask what is the best way to build it. I added a location to each form that will give us an indication of items location. How should I create such complex location tracking and quantities?
For example, I ordered 50 pants for storage, kept 25 at the storage, and distributed 25 to the warehouse and at the end of the event I sent back 10 back to the main store.
Hi Mai,
If I’m understanding your question right, the way to achieve this is to make a new transaction (received record OR distributed record) every time a quantity is moved. If you’re moving items internally, you need a received record AND a distributed record for each move. So if you distribute 25 to the warehouse from storage, distribute 25 from storage, and receive 25 to the warehouse. When you return 10, distribute 10 from the warehouse and receive 10 to storage.
What might be the trickier question is how to avoid having to fill out two forms for each transaction. To do that, I’d set up an automation that automatically creates the second record. For example, you create a form called ‘Distribute Internally’ which creates a distributed record. Then, you create a new linked record field in the distribution table called ‘Distributed to’ that’s linked to the locations, and include it in the form. Create an automation that triggers when that form is filled out, and creates a received record for the same item and quantity, at the ‘Distributed to’ location. That way you get both sides of the transaction, but you only need to fill out one form. If you want to keep the number of forms down, you could use your existing distribution form and add the ‘Distribute to’ field as optional.
Hi Julian,
That makes sense but there is another element that needs to be addressed. When I feel out of the distributed form how will it know when to abstract the quantity located in a specific location? meaning I need to have an indication of the location it was held and the location it was distributed in order to get the result that I want. it makes sense to create a record of received items anytime I distribute one but it’s not a complete flow.
Please be reminded that a received record can be received as the following:
- an order was placed
- A surplus item was sent back to the picked location
- As you suggested any time that a distribution form is submitted create a received record
But how is there a connection between the first and last point meaning the quantity of a specific order and owner-item received following a new record that was created on the distributed form
That’s why you need to create a new Location field ‘Distribute to’, like I outlined above. That field can be used in your automation to fill out the received record. So this means that in your Distributed table, you have two location fields. One is for the Distribution (where the item is coming from) and one is ‘Distributed to’ (where the item is going to). That second field isn’t actually used to calculate anything, it’s simply used by the automation to supply the location for the Received record. I think you’re almost there, but If you want to go over it in detail you can hire me for a 1:1 here.
Previously on youtube:
Jeremy asks: Hi Julian, thank you for this video, it’s super clear, helps me a lot to create my Airtable database to organize my nursery (I grow fruit trees). One question: in your example how would you handle it if you had different sizes for your products, such as S, M, L, XL ? I guess you would have one line per unique product, like Green T-Shirt - Size S, Green T-Shirt Size M, etc. But then I’m wondering how you’d keep things organized in the gallery view, having a card per Color / Product / Size doesn’t look optimal, it would be nice to have a small table in each card with the size and quantities. Hope I’m clear! Many thanks!
Julian answers: Hi Jeremy, very cool - I used to work in the farming world. You can achieve what you’re describing by creating what I’d call a summary table. Make a copy of the Products table with all the same fields and records, and call it ‘Product Summary’. In the original Products table, add a new line for every unique combination of size, color, type. You will still reference this table in your sold and received tables to add/remove inventory. Also in your original Products table, create a linked record field to the summary table, and then link each S,M,L etc to the correct record (which doesn’t have a size) in the Product Summary table. Now in the summary table, you can create Rollup field types that summarize inventory of each product by size using the controls in the Rollup to sum only records that are size S etc. In your gallery view, you can toggle each of those rollups on to see S,M,L etc. If you run into any issues trying this out, post some screenshots here and I’ll help you out
Jeremy: Hi Julian many thanks for your super quick and very useful answer. I gave it a go on your template, I’ll post some screenshots on the 3rings forum to make sure I understood well. I’m not native english speaker, so sometimes I may do some misinterpretation!
Also, correct me if I’m wrong, but if I want to use the same picture for a product, whatever its size, or add another kind of information that’s the same for a given product whatever its size, such as Material Composition, I should put it in the New Products Summary table, am I right? So that every time I add a new product, the main table becomes the Products Summary table.
Below : Original Products table with Size and link to Products Summary table
