2. PHPRunner General questions
  3. Select Query in Before record added event
This topic is locked

Select Query in Before record added event

 		11/20/2016 6:31:22 PM
PHPRunner General questions
R
 rgfischerjr author

I am working with a third party database and I can't modify it. The customer table has various columns (Name, FullName, CompanyName, ParentRef_FullName) which are used to establish relationships between companies.
I need to select a value from the ParentRef_FullName column based on the user selection from the CompanyName column (selection is made using a dropdown showing unique values in the CompanyName column) and write the selected value to row being added to the table. Something along the lines of:
$values["ParentRef_FullName"]= SELECT customer.ParentRef_FullName FROM customer WHERE customer.CompanyName = $values["CompanyName"] AND Sublevel = 2;
A company name will appear several times in the CompanyName column, however it is only the value in ParentRef_FullName WHERE Sublevel=2 that we want to select.
I want to accomplish this without the user having to select the 'correct' ParentRef_FullName in a dropdown - too many opportunities for errors.
What is the best/easiest way to accomplish this? I'm assuming it needs to be in the Before record added event (after user selects CompanyName)?
Thank you for your assistance!
romaldus 11/20/2016



I am working with a third party database and I can't modify it. The customer table has various columns (Name, FullName, CompanyName, ParentRef_FullName) which are used to establish relationships between companies.
I need to select a value from the ParentRef_FullName column based on the user selection from the CompanyName column (selection is made using a dropdown showing unique values in the CompanyName column) and write the selected value to row being added to the table. Something along the lines of:
$values["ParentRef_FullName"]= SELECT customer.ParentRef_FullName FROM customer WHERE customer.CompanyName = $values["CompanyName"] AND Sublevel = 2;
A company name will appear several times in the CompanyName column, however it is only the value in ParentRef_FullName WHERE Sublevel=2 that we want to select.
I want to accomplish this without the user having to select the 'correct' ParentRef_FullName in a dropdown - too many opportunities for errors.
What is the best/easiest way to accomplish this? I'm assuming it needs to be in the Before record added event (after user selects CompanyName)?
Thank you for your assistance!


What version of phprunner you use? For example in phprunner 8x you can accomplish this by create a custom query in lookup and than use phprunner autofill feature. or... Sometimes some screenshots can hep
R
 		rgfischerjr author 11/21/2016

[/quote]
What version of phprunner you use? For example in phprunner 8x you can accomplish this by create a custom query in lookup and than use phprunner autofill feature. or... Sometimes some screenshots can hep

[/quote]
Version 9.x. I have tried using Lookup/Autofill but the SQL in the WHERE clause throws an error due to the ". If I simply use "Sublevel = 2" in the Lookup, it pulls all CompanyName WHERE Sublevel = 2, not just the CompanyName that the user has selected from the dropdown. I cannot seem to get the AND to function in the Lookup.
Sergey Kornilov admin 11/22/2016
  1. Create a Custom view of lookup table adding WHERE `Sublevel` = 2

    at the end


  2. Use this custom view as a source for dropdown box
  3. Make ParentRef_FullName control dependent on CompanyName