Relationships and Forms

[singersoll]

I have an application called inspections running. Inspectors will input their information into a Submission Form. The end of the Form will have a total rating between 0-100 based on the various field entries in the form. These field entries come from a table called Items.

I want to populate another form with the field value for the total rating. This will be viewed by the general public for inspection results.

Any suggestions on how to approach this so my one form has checkboxes (example: food 1-5, sanitation 1-5, freezer 1-5, etc.) and how to obtain a total for these values.

Second, would I just use a valuelist sql query to this table inorder to populate my form that is viewed by the general public or am I going in the wrong direction.

TIA,
Steve

[shannah]

I think you are going in the right general direction, but I'm not sure if a valuelist is what you want to be looking for. It would depend on what you want to do with this total value? By "populate another form" does that mean that this value should be editable - or is this more a report than a form? By total rating, do you mean the total rating for that one item - or some aggregate sum of a group of items?

If you just want the general public to be able to see the results on a per item basis, then you could use permissions to make it so that the general public can view records from the items table, but not edit them.

[singersoll]

I have 40+ items that have a value (ex: food if checked = 5). I created a table with the appropriate fields and values.

When the inspector completes an inspection he is basically filling out a form. I have that working and want a checkbox for all the values in the items table above. On the inspection form there will be a separate field that would contain the SUM of all 40+ values checked (or not).

My inspection table would contain a field like ->

`inspection_item` int(11) default NULL

My items table would be setup like ->

CREATE TABLE `inspection_items` (
`item_id` int(11) NOT NULL auto_increment,
`item_name` varchar(64) NOT NULL default '',
`item_value` int(11) NOT NULL default '',
PRIMARY KEY (`item_id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

INSERT INTO `inspection_items` VALUES (1, 'Food 01', '05');

-> Finally, in the inspection table (inspection form) there will be a field that will total the inspection items.

Any thoughts on this thinking?

Thanks,
Steve

[singersoll]

I tried using the inspection_items field but it only worked for one field in my inspection table. What I really need is to show all the fields in this table in my inspection "Form" and have the inspector select the default value or 0.

The items in the inspection_items table are constant and will not change.

I found another way so opted to have all my fields in my inspection table and use enum to use one value or the other and default to the normal value. The example is that 02 would correspond to 'item_name' in my inspection_items table.

`02` enum('0','5') NOT NULL default '5',

If there is a better way to do this I would appreciate the input.

Also, how do I do about selecting certain values in the inspection table and have the sum populate the results field? As an example -> fields 01 and 02 would have a total of 9 if the default was used.


CREATE TABLE `inspection` (
`id` int(11) NOT NULL auto_increment,
`inspector_name` varchar(32) NOT NULL default '',
`categories` varchar(32) default NULL,
`permits_number` int(4) default NULL,
`purpose` enum('Complaint','Follow-up','Investigation','Regular','Other') NOT NULL efault 'Regular',
`establishment` varchar(64) NOT NULL default '',
`owner` varchar(64) NOT NULL default '',
`charge` varchar(64) NOT NULL default '',
`address` varchar(64) NOT NULL default '',
`zips_name` varchar(64) default NULL,
`zips_zip` varchar(5) NOT NULL default '',
`01` enum('0','5') NOT NULL default '4',
`02` enum('0','5') NOT NULL default '5',
`results` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Sorry that I am short on the DBA side of the house and appreciate any input.

TIA,
Steve