Sub-record?

[georgewash]

I am a newbie. I have gone through some training on the VTC website and a Missing Manuals book, but am still struggling to learn FMP.

I am creating a home grocery inventory/list generator. Here is how I have it structured and the part I am stuck on.

The main layout is Products, which allows for creation of records for any grocery item with a bunch of detail information. The two main areas are Product Info and Store Info. The Product Info has the name, category, sub-category, and description. The category and sub-category are referenced from another table used to set these. This section works fine.

In the Store Info are the following: store, manufacturere, size, and aisle location. Currently I have the store set as a drop down menu displaying values I created in the field/control setup area.

Where I am getting stuck is how to allow one product name to have multiple stores with the specific info attached to each store.

Here is the example.

Product name: orange juice

Category: drinks

Sub-category: juice

Store: Whole Foods

Manufacturer: WF 365

Size: 96 oz.

Aisle: 6

Store: Jewel

Manufacturer: Florida's Natural

Size: 48 oz.

Aisle 11

Store: Next storee

...

Can this be done within one record?

I want to be able to generate a grocery list where I can select products and then choose which store and the result will be a list of products sorted by the aisle of the chosen store.

I realize this is quite long, but I've thought about portals, new tables, etc. as well as going through websites, books, and tutorials.

Any help is greatly appreciated.