2. PHPRunner General questions
  3. Filtering record count in detail table link
This topic is locked

Filtering record count in detail table link

 		8/4/2011 7:30:52 PM
PHPRunner General questions
B
 btrdev author

I have a header and detail table where each row of the header table represents the description of a type of item in inventory, while the rows of the detail table represent details such as location, quantity on hand, etc.
I have set the Table Link Properties to "Display a number of child records" on the master page, and generally that works well for me. However, is there any way that a filter could be set for the record count to be displayed? For example, rather than displaying all detail records with item number "001", is there some way I can get it to display just the records/record count where quantity > 0? (Other than creating a separate view for the detail table).
Any assistance would be appreciated.
C
 		cgphp 8/5/2011

You can set the count record details var in the "List page: After record processed" event.

In the visul editor check the name of the child counft var. It should be something like:

yourdetailtablename_childnumber



In "After record processed" event enter a code like the following:

global $conn;

$rs = CustomQuery("SELECT COUNT(Quantity) as qty FROM detail_table_name WHERE foreignkey =".$data['id_field_name_of_master_table'],$conn);

$result = db_fetch_array($rs);

$record['yourdetailtablename_childnumber'] = $result['qty'];
B
 		btrdev author 8/5/2011

Dear Christian,
Thanks for the response. However, I am having a problem which I think is caused by the design and contents of the table.
The value returned by $data['id_field_name_of_master_table'] is a string and is causing the SELECT statement to generate a syntax error. For example, the generated statement is something like:
SELECT COUNT(quantity) as qty FROM item_details WHERE item_id =ct_item-167
It is generating a syntax error "Unknown column 'ct_item' in 'where clause'".
I think for this to work, the value "ct_item-167" needs to be in quotes. How can you get it to generate quotes around the value? I tried a few things but none of them seem to work.
I didn't actually create the database, so I can't change the types of values they are using.



You can set the count record details var in the "List page: After record processed" event.

In the visul editor check the name of the child counft var. It should be something like:

yourdetailtablename_childnumber



In "After record processed" event enter a code like the following:

global $conn;

$rs = CustomQuery("SELECT COUNT(Quantity) as qty FROM detail_table_name WHERE foreignkey =".$data['id_field_name_of_master_table'],$conn);

$result = db_fetch_array($rs);

$record['yourdetailtablename_childnumber'] = $result['qty'];
Sergey Kornilov admin 8/5/2011

Use the following:



global $conn;

$rs = CustomQuery("SELECT COUNT(quantity) as qty FROM item_details WHERE item_id='".$data['id_field_name_of_master_table']."'",$conn);

$result = db_fetch_array($rs);

$record['yourdetailtablename_childnumber'] = $result['qty'];
B
 		btrdev author 8/6/2011

Thanks.
That corrected the syntax error. Of course, for it to actually filter out the zero quantity records, I added the filter condition - i.e. WHERE quantity > 0 and item_id='".$data['id_field_name_of_master_table']."'
It now shows the correct count of corresponding non-zero records in the detail table. However, if I click the link, it still shows the records with zero quantity. Is there some way to pass the condition to the detail table so that only records meeting that condition would be displayed?



Use the following:



global $conn;

$rs = CustomQuery("SELECT COUNT(quantity) as qty FROM item_details WHERE item_id='".$data['id_field_name_of_master_table']."'",$conn);

$result = db_fetch_array($rs);

$record['yourdetailtablename_childnumber'] = $result['qty'];
C
 		cgphp 8/6/2011

I'm not sure it is the best solution. In the "javascript onLoad" event of the master table:

$("a[id*='master_yourdetailtablename']").each(function(e){

var href = $(this).attr('href');

$(this).attr('href',href + "&quantity=1");



});



In the "List page: Before SQL query" event of the details table:



if(isset($_GET['quantity']) && $_GET['quantity'] == 1)

$strWhereClause = "quantity > 0";


Sergey, let us know if a more elegant solution exists.
B
 		btrdev author 8/7/2011

Thanks. I'll experiment with the javascript approach later, but for now I've decided to create additional sql views to accomplish this.



I'm not sure it is the best solution. In the "javascript onLoad" event of the master table:

$("a[id*='master_yourdetailtablename']").each(function(e){

var href = $(this).attr('href');

$(this).attr('href',href + "&quantity=1");
});



In the "List page: Before SQL query" event of the details table:



if(isset($_GET['quantity']) && $_GET['quantity'] == 1)

$strWhereClause = "quantity > 0";


Sergey, let us know if a more elegant solution exists.