zed

[hpw]

my database is a client/contacts database and our company a consulting firm in sustainability. above all we offer customized services but we also offer some 10 preformatted products.

so, what i want is to flag each client/potential client as "user" or "potential user" each of these products.

I thought it would be a good idea to make a checkbox list for "user" and one for "potential user" with checkboxes of a valuelist called "products".

In the desktop version I designed the layout in a way so the names of the products only appear once by defining the field for the second checkbox list very narrow, so only the checkboxes appear.

In iwp, however, it becomes a complete chaos and i can't manage to get it in shape.

So here the question: Is there a better solution to this problem? Maybe make a field for each and every product with valuelist "user" and "potential"? But then I get a lot of fields and I wanted to avoid this.

Hope you understand the question.

thxs

hans peter

[T-Square]

I'd add two tables, to start:

Products, with each record in this table representing one of your products. For what you describe, you only need a ProductID and a ProductName, but you might also want to add other functionality (price, description, etc.) at a later point.

ClientProducts, with each record in this representing a client's interest in a particular product. This would need fields for the clientID and the productID, as well as an additional field for the User/Potential User flag. This is what is called a join table, and makes a many-to-many relationship possible (each client can have many products associated; each product can have many clients associated).

Join the tables (ClientID to ClientID, ProductID to ProductID--make sure to allow creation of records via the relationship) and put a portal on a client layout that uses the join table as its basis. Put a dropdown field on the portal that stores the ProductID and drops down a value list based on the Products table. Add a second dropdown for User/Potential User.

Now, you can view and create a list of the products that each client is linked to directly on the layout.

HTH,

David

[hpw]

Dear David!

Great solution! Works brilliant.

Thanks a million!

Hans Peter