Form to enter contacts in linked column

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.

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.

^ Then just do that.

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.

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.

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

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.

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:

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.

This is true! :slightly_smiling_face: That said, I would respectfully argue that fewer records should not, in itself, be a goal of the system. More that the data entry process should be quick and slick and, having entered the data, there is good reporting to support whatever process is going on. Of course, I don’t know if my solution satisfies these goals in this case.

The “reverse thought process solution” might do it too!

JB

True as well. There may be benefits to keeping a running history of what was purchased by each customer, rather than just a massive list with no context.

To clarify @Justin_Barrett’s point: in a two-table approach, forms cannot be used for this purpose without some 3rd-party assistance (API, Integromat, Zapier, etc.). Native Airtable forms cannot update existing records, they can only add new records to one or more tables.

It sounds like you have a solution that works, but isn’t necessarily ideal or efficient. Is the concern of adding records in a third table about reaching the 1,200 record per table limit? I don’t see @JonathanBowen’s solution taking an inordinate amount of time to input purchases unless each of your product names are so unique that you have to type the whole thing each time as opposed to the first few letters or so.

Thanks, @Justin_Barrett ,@JonathanBowen, @Kamille_Parks for the responses. I definitely like Justin’s idea to flip what I’m doing now to the customer side vs product side.

I’m going to try that in the field tomorrow and see how it goes!

@Kamille_Parks, Would using a 3rd party application allow me to use a form to edit a existing record? This would simplify my process some.

Yes, you can use a 3rd party set-up to update existing records. Formnano lets you do this, and of course Zapier and Integromat as well.

https://www.formnano.com/