Forums: Search Master and Details tables together - Forums

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Search Master and Details tables together

#1 User is offline   admin 

  • Administrator
  • PipPipPip
  • Group: Admin
  • Posts: 15550
  • Joined: 03-February 03

Posted 08 June 2017 - 09:33 PM

Here is how you can search master and details tables together. For instance you have Orders and OrderDetails tables and need to find orders that contain a certain product.

1. Modify Orders SQl Query to add a dummy field named 'product'. Make sure this field is searchable.


SELECT
	OrderID,
	CustomerID,
	EmployeeID,
	OrderDate,
	ShipAddress,
	ShipCity,
	ShipRegion,
	ShipPostalCode,
	ShipCountry,
	'' as product
FROM orders


2. Orders table, AfterTableInit event:

$srchObj = SearchClause::getSearchObject("orders");

$value = $srchObj->getFieldValue("product");

if( $value != null ) {
$srchObj->setSearchSQL("product", "OrderID in (select OrderID from OrderDetails where ProductName like '%$value%')");
}



In this event we do a subquery to find all orders that contain the product in question.

More info about search API:
https://xlinesoft.co...chapi_about.htm
https://xlinesoft.co..._search_sql.htm
Best regards,
Sergey Kornilov
0

#2 User is offline   Jaja N 

  • Member
  • PipPip
  • Group: Members
  • Posts: 24
  • Joined: 09-October 14

Posted 09 June 2017 - 03:00 AM

Hi Sergey , Can it be implemented in PHPRunner V.8.1 ?
0

#3 User is offline   romaldus 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 457
  • Joined: 19-May 08

Posted 09 June 2017 - 11:04 AM

Thanks
Freelance Programmer | Photographer
0

#4 User is offline   admin 

  • Administrator
  • PipPipPip
  • Group: Admin
  • Posts: 15550
  • Joined: 03-February 03

Posted 13 June 2017 - 09:14 PM

This functionality only exists in PHPRunner 9.7 and later.
Best regards,
Sergey Kornilov
0

#5 User is offline   gwomble 

  • Newbie
  • Pip
  • Group: Members
  • Posts: 3
  • Joined: 29-March 17

Posted 31 October 2017 - 02:38 PM

Is this possible in ASP Runner as well, I tried implementing it but it doesn't seem to work
0

#6 User is offline   Rigmantas 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 117
  • Joined: 22-November 07

Posted 30 November 2017 - 08:53 AM

hi,
Sorry, but i can't master that.
My master and detail pages is the same.
there is master table: inventorius_gauta

id,
diena,
prekes_nr,
prekes_pav,
paketo_nr,
matavimo_vnt,
vieneto_kaina,
vienetai,
suma,
pastabos,
gavo,
vartotojas,
skyrius,
padalinys,
sn,
gauta_data,
grupe,
kita_informacija,.....


detail table is "inventorius_nr" and there is new column "idgauta" (master id)
I want search in master and detail tables let's say sn number
I do all what you wrote above:
$srchObj = SearchClause::getSearchObject("inventorius_gauta");

$value = $srchObj->getFieldValue("sn");

if( $value != null ) {
$srchObj->setSearchSQL("sn", "inventorius_gauta.id in (select idgauta from inventorius_nr where sn like '%$value%')");
}

But this is not worked for me :(

Thanks,
Regards
Rimantas
0

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic