Dear Abby, need help with my relationships!

[LarryLogic]

I have 75 relationships and I only want to use one at a time.

The field used to link the file will always be the same for all found records. Is there a way to link to one specific file, of the 75, based on the found field without 75 differant layouts?

I'm working out of one file with data for 75 stores. One field of all found records will have the store number. When I find all data for one store, I need to compare it to the file that is specific to that store.

Larry

[Ender]

Dear Larry,

It's not clear what your tables are. Do you have one table with the 75 stores or do you have a file with 75 different tables, each for a different store? What kind of data is in the tables and how do they relate currently? This is in FM7, right?

[LarryLogic]

Sorry, I thought I filled that out. FM6 with 2000.

The relationships will be in the master file linked to each of the 75 store files.

When I find the records in the master file using the store number field, I want to link to the file for that store only. The store number field will be in both files. I could make 75 layouts, one for each file. I would rather use one layout and find a way to point to the proper file from that layout.

[LarryLogic]

I believe the only way I can accomplish what I need is with 75 differant layouts with the fields in each layout specific to each of the 75 files.

These are 150KB files with about 20 fields and 1100 records.

Is there any problems with doing it like that?

Larry

[hayesk]

You could do that, but the real question is why do you have a different file for each store? Could you not put the data for all of the stores in one file?

[Ender]

Larry,

Unless you have compelling reasons to keep your store data in separate files, they should be combined into one. You can then use the same reports and forms for all stores, or certain categories of stores. Having them in separate files creates maintenance problems like you are expriencing. You will not run into any file size issues until you get into the hundreds of thousands of records.

You can update your profile by clicking "update your profile" on the side bar right below "St. Charles, Mo". In the future, it will be helpful if you post your topic in a category that most closely fits the subject.

[LarryLogic]

Master file is all invoices from vendor for all stores.

Each store has an item price file with all items and price.

If I combine them into one file, and use the store number in both files to link, how do I compare item #999 in store #1 to Item #999 in the price file?

There are 75 records with item #999. There is only one record with store #1 and item #999. I can find all store #1 files in the master, how do I link to store #1 only in the price file?

[Fenton]

Short answer ('cause I don't know exactly what you're doing): In a calculation field, concatenate (put together) the StoreID with the ItemID (with some separator between, space, etc.). When you want to reference product data specific to a store, use this concatenated ID.

It seems to me that the ItemID may or may not be the same item in all stores. Is it? Or can any store just add an item and give it an ID. That would be chaotic.

If there is supposed to be some kind of uniformity of products across the stores (with some local variation on prices, and which products they carry), it makes the most sense to me to have 2 files to deal with Products, one for the products themselves, and a second for Product_Store combinations. It's the Product_Store file (table) which would have specific prices. There would be approx. 75 records per product; a lot of records, but not much data in each. It's called a "join" file.

It would have: StoreID, ProductID, and the calculation field, Store_ProductID (or Product_StoreID, either order). Also Price, Qty on hand (locally). It would not necessarily have Store Name, Product Name; this data could be just shown as related fields on the layout (or it could be looked up, if speed is needed (probably); it depends on what you're using the file for).

The Stores file would have 75 records, with a unique ID per store. A relationship to the Product_Store file on the StoreID would show its products.

From Products a relationship to Product_Store on the ProductID would show the Stores carrying that product. If all product distribution happens from a central point, then the Product file becomes that point, with its own Qty field.

I have no idea what kind of synchronization process is involved. But the above seems like the proper relational structure.