Use the masterkey value

I want to set up a lookup on a child table. The master table has a foreign key (PhyId= 123). Is there a way to use the masterkey value of the master table when setting up the WHERE statement in the lookup wizard. The child table does not contain the PhyId as a field. I want to only list the rows where the PhyId is equal to masterkey value of the master table. Or, Is there a better way to do this.

Can you provide a little more detail on the master / child tables and how they are linked? Do you want the lookup as part of the search criteria on the page after it's published or as a hardcoded filter?

I want to set up a lookup on a child table. The master table has a foreign key (PhyId= 123). Is there a way to use the masterkey value of the master table when setting up the WHERE statement in the lookup wizard. The child table does not contain the PhyId as a field. I want to only list the rows where the PhyId is equal to masterkey value of the master table. Or, Is there a better way to do this.

The Master and child tables are linked by a patient ID. The masters foreign Key is the field I want to select for. There is a table that I am using for a lookup it has the same foreign key as the master. I want to know is if there is a way to setup the lookup wizard to use the value of the masterkey. Examle: $_SESSION[$strTableName."_masterkey".$i] I want to just list the rows where the lookups table foreign key field is equal to the masterkey value of the master table. I want the filter to be hard coded.
Thank you for any help with this.

Sure, you can use custom WHERE clause

" ForeignKey = " . $_SESSION[$strTableName.'_masterkey1']"

If master key value is a text you need to wrap it by single quotes:

" ForeignKey = '" . $_SESSION[$strTableName.'_masterkey1']" . "'"

Sure, you can use custom WHERE clause

I am not able to retrieve the data using this statement the master key value is an INT

PhyProp is the master table name and PhyId is the foreign key field name.

"PhyId = " . $_SESSION[$strPhyProp.'_masterkey1']"
Is there somthing wrong with my syntax?

$strTableName is a variable name and you should not replace it.

$strTableName is a variable name and you should not replace it.

This is a example of my invoice module and a discription of the problem I am running into.
There is a master table (PhyInf) that contains physician demographics data and a unique ID (PhyId) for each doctor's practice. This master table has 2 child tables the first one is

( PhyFee) used for adding pricing to medical procedures. Each physician may charge a different price for a particular procedure. The procedures are displayed on a dropdown from a lookup table.
PhyFee schema:

cptId, Int, 8, Key | PhyInf ,Int,8 | cptDesc, varchar, 50| cptFee |
1, 134, Dr. Smith, (123)Broken nose, $53.00

2, 134, Dr. Smith, (123)Broken Leg, $30.00

3, 135, Dr. Jones, (123)Broken nose, $60,00
The second child table is (PatInf) This table holds patient demographics.
PatInf schema:

PatId, Int, 8, Key | PhyInf ,Int ,8 | fname, varchar, 50| Ect...
1, 134, Fname,Lname, other demo info1,

2, 134, Fname,Lname, other demo info1,

3, 135, Fname,Lname, other demo info1,
PatInf is also the parent to an invoice information table (PatInv)
PatInv schema:

cptInvId ,Int,8 |PatId, Int, 8, Key | unit, Num, 3| Mod, Num, 3 |cptDesc, varchar, 50|dx, varchar 5 |cptFee
This is where I need help. I want to use the PhyFee table as a lookup from the cptDesc field in the PatInv table. I only want to display the rows that contain the phyId for the patents physician I am currently working on. Is there a way to use the master key session variable to from the PhyInf table as a filter?
When I enter " PhyId = " . $_SESSION[$strTableName.'_masterkey1']" into the Where section of the lookup wizard I get this message.
"Unable to retrieve data using the Where condition you've entered, all records are displayed"
How do I load masterkey1 so that I can select it.
Thanks,
rgman

Session and other variables don't make sense when you test in PHPRunner. It should work though when you build and run your application in web browser.

Session and other variables don't make sense when you test in PHPRunner. It should work though when you build and run your application in web browser.

I built and ran the application and got no errors but it did not filter the lookup. Am I missing any custom code. I found this code on another simular post. Should I be using it ? If so, in which section of events should I insert it.
global $conn, $strTableName;

if ($_SESSION[$strTableName."_masterkey1"])

{

$str = "select Field1from MasterTable where Key=".$_SESSION[$strTableName."_masterkey1"];

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);

$_SESSION["Field1"] = $data["Field1"];
When I hard code A PhyID number into the Where statement the filter work when I run the application.

???
Thanks for any help.

Hi,
add this code to the Add paeg: Before process event.

Hi,

This is error I get when I add the the Add page: Before process event.
Parse error: syntax error, unexpected $end in /var/www/phpapps/amm_rd_u2/include/phyfeelst_events.php on line 54

Probably closing curly brace is missing.

Thank you for the hints, This section is working now.

Edgar Martinez