Hi,

Here is what I am trying to do:

Make a database to manage companies, what products they can buy and how much they pay for them.

I have multiple 'lists' of prices, each list is of a certain 'type' and all lists of a particular type have the same products in them.

For example:

Type A lists give access to products X,Y,Z

Type B lists give access to products F,G,H

It has been straight forward to relate products to lists using these tables (just bare bones shown):

List

---------

kp_List

kf_ListType

Name

ListType

---------

kp_ListType

Name

Product

---------

kp_Product

kf_ListType

Name

I need to store the price for a product based on what list it is in...so i created an intermediate table to handle this many-to-many relationship:

List_Product

------------

kp_List_Product

kf_List

kf_Product

Price

What I would like to have happen is the intermediate table gets populated with references to Product Id's when a ListType is selected for a List.

When setting up lists I would like a user to name the list, set it's type (say to Type A) and automatically have entries added to List_Product relating this List to products X,Y,Z. The user would then be presented a portal to set the prices of X,Y,Z for that list.

Can I do this with relationships? I can't figure out how. Do i need to use scripts? If so what would the script look like?

Thanks very much.

Reuben

Reuben,

I found this old post of yours, and was wondering if you're still looking for help?

Barbara

Hi Barbara,

If you are able to i'd be very grateful!

Reuben

I put together a bit of a demo. In it, a user can add records to List_Product from either the Product form or the List form.

Eventually, we'll need to add an Order form, and I suppose, classify the customer so that the prices for products are appropriate to the customer type.

Anyway, first step, how does the demo look?

Edit: I might have some rules incorrect. I've reread your post. The demo lets a product be in more than one list. That might not be what you need. From your description, a product is in one ListType, and a list has one type. Where is the many-to-many? Because, if that is the case, then assign a product a ListType, and as soon as you assign a ListType to a List, then you'll see the related Products. If a Product is only in one ListType, then they only have one Price (stored in Products).

Reuben.fp7.zip

Edited September 22, 200916 yr by Guest

Thanks very much,

The demo looks OK except that in the List layout, when I choose a list type i would like the products of that listtype to be shown in the portal automatically ready for their prices to be entered (not added one at a time).

Perhaps the only way to do that is to set things up as you suggest in the edit: section of your comment? It would be acceptable to have a product belong to only 1 list type. The problem i see though is that the price for a product needs to vary depending on what list the customer has access to. (i.e. price cannot be stored in products).

Any ideas on a way forward?

thanks again,

Reuben

EDIT: Sorry, I looked back through my requirements and a product does need to be able to belong to more than one list type. So some products in list A will also be in list B. (So the demo tables+relationships are correct)

Edited September 28, 200916 yr by Guest