bushki
11-08-2007, 04:54 AM
Im new so, hi everybody....
my question is, i have a database to keep records of customers and extension numbers, i am wanting to add further functionality by keeping a track of where extnension numbers have been throughout their lifespan in my database.
i shall give you an example, extension number 1111 is added to the database on the 01/01/2001 (dd/mm/yyyy) it stays dormant until someone gives it to a customer. so the user would then access the database, create a workorder and click add extension, this will load a form with all extension numbers currently dormant so the user can now select any extension and allocate it to the new customer (all of which is currently possible with my database). below is how my tables are set up:
customer: customerid, name, address, pcode etc....
extension: extensionno, customerid, workorderid, dormant, comments
extensionowner: extensionownerid, extensionno, customerid, datefrom, dateto
customer and extension are linked by an innerjoin 1:m, customer has many extensions, however there is no referential integrity because extensions can be added to the database without a customer owning them and some extensions can be removed from a customer and thus leaving them without a customer. extensionowner is a weak entity between customer and extensionnumber which i want to hold the record of: the customer, the extension they owned and between what dates they owned it.
the code i would very much like is to do the following:
i will use 2 seperate events to do 2 vital opperations.....1: i want a click event on a button to, write a complete new record to extensionowner, taking the customerid and extensionnumber from the currently loaded form, and add to this record the datefrom as date().......so say i added my above example to a customer with CustomerID: 1:
extensionownerid = 1, extensionno = 1111 customerid = 1, datefrom = 01/01/2001, dateto remains empty
.......2: this is where it gets tricky, i want another operation embedded within a click event to lookup the record which is currently selected, in the extensionowner table and add a value. So say i am on my extension form and the user wants to remove an extension from a customer, i want the click event to lookup the current extensionowner (maybe i should add a boolean to extensionowner to show whether they are the current owner) which in my example would be:
extensionownerid: 1, extensionno: 1111, customerid: 1, datefrom: 01/01/2001, then write date() to the "dateto" field.
can anyone help with this please i am tearing my hair out?
thanks
my question is, i have a database to keep records of customers and extension numbers, i am wanting to add further functionality by keeping a track of where extnension numbers have been throughout their lifespan in my database.
i shall give you an example, extension number 1111 is added to the database on the 01/01/2001 (dd/mm/yyyy) it stays dormant until someone gives it to a customer. so the user would then access the database, create a workorder and click add extension, this will load a form with all extension numbers currently dormant so the user can now select any extension and allocate it to the new customer (all of which is currently possible with my database). below is how my tables are set up:
customer: customerid, name, address, pcode etc....
extension: extensionno, customerid, workorderid, dormant, comments
extensionowner: extensionownerid, extensionno, customerid, datefrom, dateto
customer and extension are linked by an innerjoin 1:m, customer has many extensions, however there is no referential integrity because extensions can be added to the database without a customer owning them and some extensions can be removed from a customer and thus leaving them without a customer. extensionowner is a weak entity between customer and extensionnumber which i want to hold the record of: the customer, the extension they owned and between what dates they owned it.
the code i would very much like is to do the following:
i will use 2 seperate events to do 2 vital opperations.....1: i want a click event on a button to, write a complete new record to extensionowner, taking the customerid and extensionnumber from the currently loaded form, and add to this record the datefrom as date().......so say i added my above example to a customer with CustomerID: 1:
extensionownerid = 1, extensionno = 1111 customerid = 1, datefrom = 01/01/2001, dateto remains empty
.......2: this is where it gets tricky, i want another operation embedded within a click event to lookup the record which is currently selected, in the extensionowner table and add a value. So say i am on my extension form and the user wants to remove an extension from a customer, i want the click event to lookup the current extensionowner (maybe i should add a boolean to extensionowner to show whether they are the current owner) which in my example would be:
extensionownerid: 1, extensionno: 1111, customerid: 1, datefrom: 01/01/2001, then write date() to the "dateto" field.
can anyone help with this please i am tearing my hair out?
thanks