Need help limiting what rows are selected

[JohnKlassen]

Hi,
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
 );

Using the above query, I select rows from the violations table based on the unit number (vio_unit_id) and rules number (vio_rules_id). I realized that in some situations, I can have multiple occurrences of the same vio_rules_id for that vio_unit_id.

Browse form with similar violations.png

As you can see in the above screen shot, unit '07-102' has 2 different occurrences of the same violation, 'Recreational Infraction'. Using the above code, when I click on 'Print Letter' (below), it generates 2 letters, one for each violation.

Edit form with print letter button.png

What I really need to do is to select rows based on violations_id instead of 'vio_unit_id' and 'vio_rules_id'.
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.

Unique violations id.png

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)?

SQL code for violations edit form.png

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

[massiws]

John, I try to give you some doubts!

As you can see the 'vio_unit_id' and 'vio_rules_id' are the same for both rows but the 'violations_id' is unique

Do you have to select only one row? Can you say which of the two rows you have to select?

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)?

From wiki: $formValue: This is an array of the Variables from the selection Form, created prior to running an activity.
To get all value in $formValue you can use var_dump($formValue) to print the output on the screen, or nuDebug() function to store in zzsys_trap table:

Code: Select all

foreach($formValue as $key => $value) {
    nuDebug("key: $key - Value: $value");
}

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 think you have to set another WHERE condition in you SQL, to skip out the rows you don't want to get.


Hope this helps,
Max

[JohnKlassen]

Max,

Once again you pointed me in the right direction and helped me resolve the problem.

By using the code you gave me:

Code: Select all

foreach($formValue as $key => $value) {
    nuDebug("key: $key - Value: $value");
}

I determined that the violations_id (primary key) was called 'recordID' when I used '$formValue'

Since I only wanted the one row, I replaced the following code:

Code: Select all

$unit_fk = $formValue['vio_unit_id'];
$rules_fk = $formValue['vio_rules_id'];

with

Code: Select all

   $vio_pk = $formValue['recordID'];

In the WHERE clause I replaced

Code: Select all

   violations.vio_unit_id = '$unit_fk' 
   AND violations.vio_rules_id = '$rules_fk' 

with

Code: Select all

  violations.violations_id = '$vio_pk' 

That simplified the code and I got exactly what I wanted.

If that is too confusing to follow, here is the new code:

Code: Select all

// Logic for Notice Letters

// Retrieve value for primary key (violations_id)

   $vio_pk = $formValue['recordID'];

// 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.violations_id = '$vio_pk' 
   AND violations.vio_unit_id = unit.unit_id 
   AND let_notice_number = violations.vio_violation_number

ORDER BY people.peo_type

EOSQL
 );

Thanks again,

John

[massiws]

.