best approach to limit value list based on deeper relation

[Jonas Löfvenmark]

I have a table of Companies, operating in differing Regions (defined in a Regions table), stored in a repeating Regions field for each company.

For my Items table (located in a different file) would like to store Companies in a repeating field in a similar matter, and be able to set Regions for each item via a value list based on the Regions defined in each Company.

If:

Company A have Regions 1, 2, 3

Company B have Regions 2, 4

and I select Company A for the first repetition of the Items Company field and B for the second repetition, I would like the value list for the items Region first repetition to show 1, 2, 3, and the second repetition to show 2,4.

i.e an item might belong to a company, but not all the regions of that company. It would be nice if the Regions Value list only showed the Regions corresponding to each company.

My first approach was setting up a value list of Regions related to each Company in the Company File, and then creating a relation to the (linked) company DB in my Items file, and using the external value list method but the value list only shows "unrelated". I tried using portals, but.... I'm getting over my head here!

best regards

Jonas

[bcooney]

Jonas,

I would restructure your system to include the following tables:

Companies

Regions

CompRegions (join table...rather than your repeating field. Stores unique combos of CompID and RegionID).

Items (why is it in a different file?)

Do items related to more than one Company? Do items have more than one region?

[Jonas Löfvenmark]

Dear bcooney, thank you for your input! There are different files as this project will be a huge PIM system involving thousands of products from hundreds of manufacturers sold through multiple franchises in different regions and languages with storing of images as well as price calculations in different currencies, also tracking competition pricing. Phew, almost fainted writing that - what a project... Anyway, 1 file simply gets too blurry incorporating everything so I try to plan ahead and keep things as separate as possible.

Do items related to more than one Company? Do items have more than one region?

Yes and yes...

[bcooney]

I feel that you are best served with join tables rather than multi-keys or repeating fields.

If items relate to more than one company, imho, you should have a join table storing those matches. Same thing for regions and items.

Regardless of the size of the system, the data should be modeled correctly. I've not see any benefits to separate files. In fact, they make for more work for the developer. Images should not be embedded in the file, btw.

[Rick Whitelaw]

I agree with Barbara regarding separate files. When I first started, I created a solution for my business that I still use. It uses 7 files. I had thought it would be better to keep things modular. As it turns out there really isn't an advantage since all tables must relate the way they're needed to regardless of whether they're in the same file or not. Each file has its own relationship graph which makes for a lot of work compared to having only one. As well, I believe the limitations on the number of custom menu items and layouts have changed although I'm not sure. These limitations made a good case for multiple files in version 8.

[Jonas Löfvenmark]

Thanks for your input, it is much appreciated! :laugh2: