Automatically add items to an intermediate table when a field is changed

[reuben]

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

[bcooney]

Reuben,

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

Barbara

[reuben]

Hi Barbara,

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

Reuben

[bcooney]

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, 2009 by Guest

[reuben]

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, 2009 by Guest