Need help limiting what rows are selected

Need help limiting what rows are selected

Postby JohnKlassen » Tue Jul 30, 2013 3:03 pm

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 similiar violations.png
Same unit has 2 similar violations
Browse form with similiar violations.png (46.23 KiB) Viewed 4331 times
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
Edit form with print letter button
Edit form with print letter button.png (26.87 KiB) Viewed 4331 times
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
Unique violations id
Unique violations id.png (9.78 KiB) Viewed 4331 times

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
SQL code for Violations Edit form
SQL code for violations edit form.png (18.82 KiB) Viewed 4331 times


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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Need help limiting what rows are selected

Postby massiws » Fri Aug 02, 2013 9:41 am

John, I try to give you some doubts!
JohnKlassen wrote: 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?

JohnKlassen wrote: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");
}


JohnKlassen wrote: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
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: Need help limiting what rows are selected

Postby JohnKlassen » Fri Aug 02, 2013 1:41 pm

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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Need help limiting what rows are selected

Postby massiws » Fri Aug 02, 2013 6:14 pm

.
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy


Return to Report Writer



cron