I need some advice on a challenging Scriptcase situation (at least for me).
Application: Reporting and limited editing (for error correction purposes) on items (cars, motorcycles, trucks) contained in a MySQL database.
Background: The data I need to report on (and edit) is created by another process. Business rules are enforced by that system during data collection.
Database tables: I have two tables (I am simplifying the application here). One table is called ‘attributes’ which is static and contains permitted values. The other table is called ‘items’ and contains one or more attributes (detail_code) per item.
Here are the table structures and sample data (limited)…
attributes
autonum | type | detail_code
1 | car | 2-door
2 | car | 4-door
3 | motorcycle | honda
4 | motorcycle | yamaha
5 | truck | twin_axle
6 | truck | lift_gate
7 | truck | heavy_duty
8 | truck | no_extras
items
autonum | item_number | detail_code
1 | 1 | 2-door
2 | 2 | honda
3 | 4 | twin_axle
4 | 4 | lift_gate
5 | 4 | heavy_duty
6 | 6 | 4-door
7 | 7 | no_extras
[Note: In the full attributes table, car has 16 detail_codes, motorcycle has 3 detail_codes and truck has 19 detail_codes.]
Business rules:
- An item can have only one type - car, motorcycle or truck.
- An item that is a truck can have one or more detail_code(s). Items that are either a car or a motorcycle can have only one detail_code.
- If a truck has a detail_code of “no_extras”, it may not have another detail_code.
- Truck can only have one of each detail_code - no repeats.
- All detail_code(s) are unique.
Scriptcase Requirement: Create a form (or editable grid) to correct (edit) errors in categorizing items which can be activated by clicking on a grid displaying items. All detail_code(s) per item should be edited at once (so as not to confuse the user).
Progress So Far: I have been successful in creating a form which uses a select lookup for detail_code and a query to only show the car-related/motorcycle-related detail_code(s) in the dropdown as appropriate. So, if there is only one detail_code per item, all is working fine.
Issue: I am unable to figure out how to edit detail_code(s) for trucks where there may be one or more records in ‘items’. I think using a checkbox style of interface would be ideal - where each of the 19 possible boxes would be pre-checked based on database value(s). If the user “unchecked” a previously “checked” box, then that record would be deleted from the table (‘items’). If a previously “unchecked” box where “checked”, then a record would need to be inserted in the table (‘items’). If “no_extras” were checked, then other checkboxes would need to be cleared. I would prefer to issue a warning message (with a cancel option) before clearing the checkboxes.
Attempt(s) So Far: Pivoting the table (‘items’) to create a structure with one row (record) per item_number. Unfortunately, this results in a non-updatable MySQL view. I considered emulating the pivot table structure in a temporary table, and trying to update the main table (‘items’) using triggers but that seemed very complex and awkward.
Any suggestions on how to approach this in Scriptcase would be appreciated!