Relationships and Forms

Archived from the Xataface Users forum.

singersoll — Tue Sep 25, 2007 6:17 am

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 — Tue Sep 25, 2007 8:03 am

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 — Tue Sep 25, 2007 9:48 am

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 — Wed Sep 26, 2007 6:10 am

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