Capturing Customer Options in their profile

[Stephen Schell]

Hi All

We install and maintain tropical plants in our client's offices. As part of the installation of the plants we select containers that we plant the tropical plants in.

We want to capture the limited number of containers chosen for that client (there would never be more than say 20 and usually it is 2 to 4) and make that info available in the future. The container choices are already loaded into another A5 table.

I am assuming that I would add a field or fields to the client table to record a pointer back to the original container part record. Should I add say 20 extra fields to the client table, or can I add multiple container names into one field in the client table. I would prefer the one field option but don't know how I would be able to use that info in the future.

Does anyone have any ideas that I can use?

Thanks for the help.

Steve

[TheSmitchell]

Re: Capturing Customer Options in their profile

I would highly suggest you create a table that maps the client ID to the container ID. The combination of client ID & container ID can be used as your primary key. At a minimum, the combination of the two fields should be unique and both fields should never be NULL. This allows you to map a variable number of containers to clients.

You would use a join on the Client table with the Container table using the intermediate Client to Container table.

I would advise against creating 20 extra columns in your client table for this information. For starters, that's 20 extra fields that are added to every record - taking up space. And if your average case is 2-4 containers per client, you've created 16-18 columns of waste. Also, it's much MUCH MUCH easier to generate reports, grids, etc, if your data is normalized out to 3rd normal form (client --1:many-> client2container <-many:1-- container).

[Stephen Schell]

Re: Capturing Customer Options in their profile

Hi

Thanks for the reply.

Yes I see where you are going with a second table. We already have a unique Customer field that we can use as a link betwen tables. That is what we will do.

Within the table, I was thinking along similar lines with avoiding many unused fields in the new table but how do I structure the info so that I can capture and then reuse several container part numbers in a single record?

The end result I want is to use the container part number as a list that we can select from for online forms that staff would fill out so that they only see the types of containers installed at that site and when they select the part number we would fill in the form using other info from the underlying container profile record.

This is what I am stuck on and any help would be appreciated.

[TheSmitchell]

Re: Capturing Customer Options in their profile

Hi

Thanks for the reply.

Yes I see where you are going with a second table. We already have a unique Customer field that we can use as a link betwen tables. That is what we will do.

Within the table, I was thinking along similar lines with avoiding many unused fields in the new table but how do I structure the info so that I can capture and then reuse several container part numbers in a single record?

The end result I want is to use the container part number as a list that we can select from for online forms that staff would fill out so that they only see the types of containers installed at that site and when they select the part number we would fill in the form using other info from the underlying container profile record.

This is what I am stuck on and any help would be appreciated.

I'm moderately confused about what you're trying to do.

If you wanted to store all the part numbers in a single field, you could dump them in a field and use a delimiter of sorts to separate each ID. This would not be very fast to search/join. Maybe that's a non-issue for you?

There are lookups available for textboxes and combo boxes in alpha. For example, for a combo box you can specify a dynamic lookup using a connection string & query.

There is an additional tool called "lookup and fill in fields". You can add that action to the on change event for the combo box or textbox. Look up the container info using the container_id & populating relevant fields.

Ascii below. Because I'm not following what you're talking about having unique Customer fields for use as a link between tables.

Customer table:
+------------------------------+
| Customer                     |
+------------------------------+
| Customer_Id                  |
+------------------------------+
| ........                     |
+------------------------------+

Container table:
+------------------------------+
| Container                    |
+------------------------------+
| Container_Id                 |
+------------------------------+
| ...                          |
+------------------------------+

Mapping table (one entry per customer per container; so if your customer has 4 containers, there would be 4 entries):
+------------------------------+
| ContainerToCustomer          |
+------------------------------+
| Customer_Id                  |
+------------------------------+
| Container_Id                 |
+------------------------------+


List of all container records available for that customer:

SELECT Container.*
FROM Container 
	INNER JOIN ContainerToCustomer 
		ON ContainerToCustomer.Container_Id = Container.Container_Id
WHERE ContainerToCustomer.Customer_Id = :CUSTOMER_ID

[Stephen Schell]

Re: Capturing Customer Options in their profile

Hi Sarah

Thanks for the effort that you have made to help. It has all clicked in now and I can move forward.

Thanks