Form to enter contacts in linked column

[Kendall_Shortt]

Hi,

I’m not sure this is even possible but this could be a lifesaver for me. I go into the field every day and take an inventory of what new items my contacts have purchased in the last few weeks. I would like to add their name to the specific product row, whether it’s one item or multiple items. I already have a tab with all my contacts and another tab with all the inventory but I need an easy way to add their name to each item they purchase, preferably a form I can fill out during my stop or at the end of my day.

EX: Contact A purchased Item A, B,C and D
Contact B purchased B,D

Fill out a form that pulls each contacts name and allows me to select from the inventory table the specific items they purchased.

Let me know if you have questions.

[Kamille_Parks]

Will you need to know the quantity of each item purchased? i.e. 2 chairs, 3 books, etc.

Without quantity info, the easiest solution is to make a third table in your base: [Purchases]. Link this table to both your [Contacts] table and your [Inventory]. Add a {Created Time} field to automatically track the purchase date for you. You should be able to just create a form in this table now to record each day’s purchases, but you will have to submit one form entry per customer.

If you have many customers, such that one form entry per customer each day would become too large a task, or you do need proper quantity/inventory control, you’ll need a more in-depth solution. I would probably incorporate Integromat or Zapier to get a smoother form submission experience.

[Kendall_Shortt]

Thanks Kamille,

I created what you described but unfortunately it won’t work in my scenario.

I don’t want to create new records that links to the contact and inventory because there will be a ton of records within a short period of time. This is because I see the same person every two weeks and I believe I would need a new record to show the new purchases for that day.

I want to take my current inventory list with all the specific details and add in the contacts name in a column to show that they purchased this item.

Hopefully this makes sense.

[Kamille_Parks]

Kendall_Shortt:

I want to take my current inventory list with all the specific details and add in the contacts name in a column to show that they purchased this item.

^ Then just do that.

[Kendall_Shortt]

I do that now but I would like a faster way of doing this, hint the use of a form for this task. Just wanted to know if I could setup something where Airtable will pull up the the person’s name and I can add in each tool that they purchased into the existing column without additional rows being made elsewhere.

[Justin_Barrett]

What if one customer ends up purchasing a given item more than once? Say you visit, and they’ve purchased Item A. Two weeks later you visit again, and they’ve purchased Item A again. How do you want that reflected in the base? When looking at the customer record, do you want to see Item A listed twice, or do you need a record of when each individual purchase was made? I’m trying to get more clarity regarding your end goal, as it will affect how the system is designed.

[JonathanBowen]

I agree with @Kamille_Parks on this one. The “best” way to model this data set is using 3 tables - contacts, inventory and purchases (which connects contacts and inventory).

Could it work for you to then have a quick “order form” on the purchases table to record the contact and the inventory they purchased?

I think the 3rd table would also give you nice ways to summarise purchases by product, by contact, by date. It would also deal with the questions that @Justin_Barrett raises, i.e. multiple purchases over time and so on.

Seems like this would work, but of course, I don’t know the specifics of your business.

JB

[Kendall_Shortt]

They won’t purchase the item twice since it lasts a long time (2-3 years) and then they will upgrade to the newest version.

For instance: Joe purchased 3 items this past month so I want to add those to the items he has already purchased and keep a running account of what each contact
has.

The issue I have is I don’t want to click on every single item and add the name in because I have 300+ items and I keep adding more so this task would take forever.
I’m also using my mobile device in the field to record this info and not my laptop.

[JonathanBowen]

What’s nice about an “order form” is that it would work well on mobile and for the contact and the item you can use the built-in search so no scrolling through hundreds of products or contacts:

[Justin_Barrett]

The only issue with an order form is that it will add new records, which @Kendall_Shortt wants to avoid.

If repeat orders of a given product won’t happen, then perhaps reverse the thought process. Instead of linking names to products on the product table, do the work on the customer table and link products to names. Make a field that links to your products table. Open the customer’s record, tap “+ Link to a record in the Products table”, then start typing a name to search for it. No need to scroll through hundreds of products, and you can add more for the same customer very quickly.