Adding weight to produce purchased

Steven_May · ‎Dec 23, 2019

Hello,

Our nonprofit manages a program that measures the weight of fresh produce purchased by our residents. I have a table with a list of various types of fruits and vegetables. I want to create a for that will add individual weights to each type of fruit/veggie. For example, if Ms. Smith buys 3 lbs of apples and 2.27 lbs of bananas, I want to record that information. I can’t figure out how to add a field that will associate a “freeform” number with a lookup list of produce. Any advice is most welcome!

Thanks,
Steve

AlliAlosa · ‎Dec 24, 2019

Hi there @Steven_May! Sounds like you want to add a junction table.

This is definitely one of the more difficult concepts to wrap your head around when it comes to database structure. In essence, you have a many-to-many relationship between customers and items purchased. One customer can buy many types of items, and one type of item can be sold to many customers.

You’ll want to add a table that’s linked to your customers, as well as to the produce table, probably called something like [Purchases] or [Line Items]. Each record should be associated with one item, and one customer. Here is where you can put the weights and quantities of the produce purchased.

You might also want to add another table, called something like [Orders]. If Ms. Smith buys 3 pounds of apples and 2.27 lbs of bananas in one purchase, ideally this would be represented by 2 records on the [Line Items] table, one for apples, and one for bananas. Both of these records could be linked to a record on the [Orders] table, where for example, you can sum up the total cost for that particular order.

Here’s an example base I made that might help to support what I’m trying to explain… it’s regarding invoices and line items, but the concept is the same. :slightly_smiling_face: Hope this helps!

Steven_May · ‎Dec 26, 2019

Thank you very much for responding! It seems like you understand what I’m trying to do; however, I am having difficulty wrapping my head around your solution. Are you recommending that on a [Purchases] table that there be individual records connecting each Customer to a Produce Item? For example, do you mean I should have a record for “Mrs. Smith, Bananas”; “Mrs. Smith, Apples”; “Mrs. Smith, Zucchini”; “Mr. Jones, Bananas”; “Mr. Jones, Apples”; “Mr. Jones, Zucchini”; and so forth? That seems excessively redundant. Is it not possible to associate three tables for [Customer], [Produce Item], and [Weight]?

Any advice or primers you can point me to would be welcome. My database experience is with Access and FilemakerPro, but I’d like to be able to use a cloud based system for our program’s needs. Thanks to you or any other community members for your feedback.

AlliAlosa · ‎Dec 27, 2019

Hi there!

Yes, I am recommending that there be individual records connecting each customer to each produce item purchased :slightly_smiling_face: Without this, there is no where else to put the weight per item purchased. You could just use a long-text field and type in each item and its weight as a string, but you wouldn’t be able to sum those numbers up down the line.

I adjusted the sample base I shared to be about Fruits and include the weight fields:

You might also find this article helpful, it breaks down many-to-many relationships and junction tables in Airtable and how to structure them.

Hope this helps!

Steven_May · ‎Jan 06, 2020

Thank you so much for your time, I think I’m beginning to understand. I have two tables, one called “Residents” with 253 records, and another called “Produce” with 138 records. I believe your recommendation is that I need to associate each record in Resident with all the records in Produce. This will create the one-to-many relationships so I can then create a junction table to fulfill the many-to-many relationships I need. Is there away way to select all the Produce records at once when linking, or must I click each 138 record individually?

Thanks again for your patient response.