create fields on the fly or in advance?

[zac123]

hi all,



whats the best way to handle this.

i want users to be able to add new items onto a form. these items are services that a customer may choose.

in theory there could be 100 different services for each customer. but in practice the maximum will probably be 10.

as a new service is created by the user new items will appear on the form. for example, a memo box for description some yes/no chk boxes and a text box.

is there a way to create all these fields dynamically as and when a new service is selected or is it majorly complicated?

would it be eaiser to just create up to 10 sets of fields and just show/hide them as and when the user creates the service?

thanks
zac

[NoellaG]

Hi,

it sounds to me you need a services table, and each new service is a new record in that table, no new field. You can link the services to the customer using a link table Customers_Services. A classical referential design.

grNG

[zac123]

a ha, ok, so perhaps a one to many (form/sub form) type senario?

so we'd have a customer table and a services table.

on the customer form we could have a subform that when saved, inserted into the services table...

hmmm, thinking out loid there

does that sound about right?

[NoellaG]

Hi,

first you have to look at the relationships: I suppose 1 customer could have many services ad 1 service can be attributed to many customers. If so, you have a may to many relationship and you need an intermediate linking table.
So you end up with 3 tables:
1 customers table
1 services table
1 linking table customers_services

for instance:

tblCustomers
--------------
custID --> Primary key field (autonumber)
custName (text)
custAddress (text)
custPostalCode(text)
custCity (text)
.........

tblservices
--------------
srvID (autonumber) --> primary key
srvDescription (text)
..............

tblCustomer_Services
----------------------
csID (autonumber) --> primary Key
csCustomer (Long Integer) --> foreign key to tblCustomers.custID
csService (Long Integer) --> Foreign Key to tblServices.crvID
csServiceFromDate (Date/Time)
..............

grNG

[zac123]

yes correct. multiple servers to multiple customers.

but...

our services are not like products. they are all different.

for example we might offer a window cleaning service to one customer on every wednesday of each week but becuase the customer wants the windows cleaned at 04:00hrs we change extra

also because this customer wants us to use clean water we add an extra charge

also this customer wants us to email a picture of the clean window each time we clean it, so we ad that also.

these are silly examples but you can see how we cant have set products. each customer may have many chargable services but each one is tailored.

also just having a description box wont do as we have to be able to query and itemise for reports etc.

does this change the info you've given me above?