I have an application where I am selecting a violation and then generating a letter in a report based on that violation. I select the violation and while in the ‘edit’ form for that violation, I select the data needed to generate the letter. Unfortunately, under certain conditions, I retrieve more data than I want. Here is the code in my report.
Code: Select all
// Logic for Notice Letters
// Retrieve values for unit foreign key and rules foreign key
$unit_fk = $formValue['vio_unit_id'];
$rules_fk = $formValue['vio_rules_id'];
// Create dataTable
nuRunQuery(
<<<EOSQL
CREATE TABLE #dataTable#
SELECT
violations.violations_id,
violations.vio_unit_id,
violations.vio_rules_id,
unit.unit_id,
unit.unit_building_unit,
violations.vio_violation_number,
violations.vio_printable_information,
people.people_id,
people.peo_type,
people.peo_building_unit,
people.peo_name,
people.peo_company,
people.peo_address_1,
people.peo_city,
people.peo_state,
people.peo_zip,
rules.rule_key_phrase,
rules.rule_section,
rules.rule_frequency,
letter.let_base,
DATE_FORMAT(CURDATE(),'%M %d, %Y') AS today_date
FROM violations
INNER JOIN (unit, rules, people, letter)
ON (
unit.unit_id = violations.vio_unit_id AND
rules.rules_id = violations.vio_rules_id AND
people.peo_building_unit = unit.unit_building_unit AND
letter.let_frequency = rules.rule_frequency
)
WHERE
violations.vio_unit_id = '$unit_fk'
AND violations.vio_rules_id = '$rules_fk'
AND violations.vio_unit_id = unit.unit_id
AND let_notice_number = violations.vio_violation_number
ORDER BY people.peo_type
EOSQL
);
Here is some of the actual data in the violations table for these 2 violations. As you can see the 'vio_unit_id' and 'vio_rules_id' are the same for both rows but the 'violations_id' is unique. In my code, I use ‘$formValue’ to get the value of ‘vio_unit_id’ and ‘vio_rules_id’ from the violations edit form. I tried to use ‘$formValue for ‘violations_id’ but it doesn’t retrieve a value. I am not sure why ‘$formValue’ works for ‘vio_unit_id’ and ‘vio_rules_id’ and doesn’t work for ‘violations_id’. Is it related to the SQL statement for the violations form (below)? The bottom line is, I need to know how to retrieve the ‘violations_id’ so I can limit the SQL statement in the report to ‘violations_id’ instead of 'vio_unit_id’ and ‘vio_rules_id’.
I appreciate any help you can give me.
John