Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

Need help limiting what rows are selected

Locked
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Need help limiting what rows are selected

Unread post by 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.
Same unit has 2 similar violations
Same unit has 2 similar violations
Browse form with similiar violations.png (46.23 KiB) Viewed 9583 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
Edit form with print letter button
Edit form with print letter button.png (26.87 KiB) Viewed 9583 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
Unique violations id
Unique violations id.png (9.78 KiB) Viewed 9583 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
SQL code for Violations Edit form
SQL code for violations edit form.png (18.82 KiB) Viewed 9583 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
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Need help limiting what rows are selected

Unread post by massiws »

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
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Re: Need help limiting what rows are selected

Unread post by 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
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Need help limiting what rows are selected

Unread post by massiws »

.
Locked