[thelist] database to create/maintain form, and record submissions

Joel D Canfield joel at spinhead.com
Tue Dec 21 17:08:53 CST 2004


Having a mental block about this:

I need to create an order form (marketing supplies for inhouse sales
reps) which can be edited by end users (it's all inhouse, and I'll be
able to train them.) The form currently has about a dozen sections, with
less than dozen items in each section. They need to be able to sort the
items in whatever order strikes Marketing's fancy.

I assume the best method would be to database the section and field
names,
create an editable sort order, and give them a form to add or modify it.

On the other end, we need to maintain a record of individual orders,
writing to the db the values for each item, and linking it to our main
HelpDesk database (using the unique ID from that database.)

Finally, if someone needs to update a request, the existing data should
populate the form, allowing additions, modifications, or deletions, and
resubmission using the same ID.

Here's what I envision, and it seems a little kludgy:

SuppliesSections
	Section
	SortOrder
SuppliesItems
	Item (items are unique, so this is a many to one relationship)
	Section
	SortOrder
SupplyOrders
	WO_NUM (this is the unique ID from the HelpDesk database)
	Item
	Value

The form admins could add, modify, or disable form sections, fields, and
sorting.

Individual orders would be tracked by the unique ID from the other
database,
which would be repeated for each item in the table SupplyOrders; not
'normal', but not a huge waste of space in this case.

The form would be built from the Sections and Items tables, and the
'update'
version of the form would be built from that table, and populated from
the
SupplyOrders table based on the WO_NUM field. For each field in the
Sections/Items cross, if a matching 'Item' was found in 'SupplyOrders'
the
value would be written. That way, going forward, if new fields are
added,
they'll just be blank for old orders. If a field is disabled, they'd
have to
decide if that affected only the 'new orders' form, or would be retro to
the
'updates' form, effectively deleting those items from availibility.

Am I missing something which would make this all simpler? Does it all
make
sense?

Thanks ever so much

joel


More information about the thelist mailing list