Funny you should mention that, Andrew, as just this morning I changed the
relationships. It was set up as you mentioned, but now it goes PO->Services
and a separate PO->LineItems ... I was thinking that may make a difference. I
haven't tested it yet, but plan to today. Yesterday, I was so frustrated, that
within my first "foreach" statement (showing lines), I hard coded ten if
statements ... i.e. :
if ($poline=="2") {
echo "<input type='checkbox' name='Access_System'
class='input_checkboxes' value='Access Security System' />Building Access,
CCTV, Alarms & Monitoring*<br />";
}
if ($poline=="3") { .... Etc
I was getting slightly panicked and it was a workaround until I figured out the
proper way to query it from the table. Really don't want to have to change
code every time a service is added/removed/changed!
I really appreciate your input and can't wait to see if that's what the problem
was.
Thanks,
Trish
From: fx.php_list-bounce@xxxxxxxxxxxxx
[mailto:fx.php_list-bounce@xxxxxxxxxxxxx] On Behalf Of Andrew Denman
Sent: Thursday, December 22, 2016 10:30 AM
To: fx.php_list@xxxxxxxxxxxxx
Subject: [fx.php_list] Re: Related records
To fully help we need to know how the tables are related. Is this how your
tables are set up? PO > Line Items > Services
In this case you will need to do two finds to get all of your information since
we can't have portals within portals. In your description you mentioned putting
the Services portal on the PO layout but that won't work: it needs to go on the
Line Items layout. You will search the PO layout for that info and then the
Line Items layout (with the services portal) and be able to get all of your
information within the FX data arrays. (You could also do this with one find if
you put the PO info on the Line Items layout, but depending on how much PO info
you need and how many line items there are that could be a lot of duplicative
information which could slow the query down.) If you aren't getting the data
you need with this setup, then there is something going on with permissions or
the relationship.
The other option is going down your ListSvcs calculation route, but that
requires some PHP data transformation instead of having everything already in
FX data arrays as above.
Andrew Denman
From: fx.php_list-bounce@xxxxxxxxxxxxx<mailto:fx.php_list-bounce@xxxxxxxxxxxxx>
[mailto:fx.php_list-bounce@xxxxxxxxxxxxx] On Behalf Of Van Buskirk, Trish
Sent: Tuesday, December 20, 2016 2:02 PM
To: fx.php_list@xxxxxxxxxxxxx<mailto:fx.php_list@xxxxxxxxxxxxx>
Subject: [fx.php_list] Related records
I have two tables I am querying, related by LineCategoryID. One is a PO table,
the other contains services available per line item on the PO.
I have tried two separate searches; the first finding the Lines available on
the entered PO #, then next querying the Services with the returned
LineCategoryIDs. I also tried putting a portal on the PO layout which displays
the services. I can't get either to work properly. The only thing I've been
able to get to work is adding a ListSvcs calculated field which is
List(ServicesPO::ServiceName). However, that won't work, as I need to create
checkboxes for the user to select services.
Here's the bare-bones query I have done using 2 searches, which only returns
the first service.
<?php
// define('DEBUG', true);
require_once('FX/FX.php'); // FX.php file
include_once('include/server_data.php');
// IP address submitting form
$ip = $_SERVER['REMOTE_ADDR'];
$GL = $_POST['GL'];
$NR_GL = $_POST['NRGL'];
$NRPO = $_POST['NRPO'];
$PO = $_POST['PO'];
echo $GL."<br />";
echo $PO."<br /><br />";
// look up lines on PO
$polineQuery=new FX($dbHost,$port,$dbType,$conType);
$polineQuery->SetDBData('ReferenceTables', 'FSU_AUX_ITS_POS', 'all');
$polineQuery->setDBPassword($dbPass,$dbUser);
$polineQuery->AddDBParam ('GL_String', $GL);
$polineQuery->AddDBParam ('PO_No', $PO);
$polineQuery->AddDBParam ('CountServices', '0', gt);
$polineQuery->AddDBParam ('Descript', 'Telecom Non-Recurring', neq);
$polineQuery->AddSortParam('Descript', 'ascend');
$polineResults = $polineQuery->DoFXAction('perform_find');
foreach ($polineResults as $polines) {
$poline=$polines['LineCategoryID'];
$podesc=$polines['Descript'];
echo $poline." - ".$podesc."<br />";
// look up ITS services on PO lines
$svclineQuery=new FX($dbHost,$port,$dbType,$conType);
$svclineQuery->SetDBData('ReferenceTables', 'SvcListForWeb',
'all');
$svclineQuery->setDBPassword($dbPass,$dbUser);
$svclineQuery->AddDBParam (POitemID', $poline);
$svclineQuery->AddDBParam ('POCatID', '12', neq);
$svclineQuery->AddDBParam ('TypeWorkOrder', 'Orders');
$svclineQuery->AddDBParam ('Status', 'A');
$svclineQuery->AddSortParam(ServiceName', 'ascend');
$svclineResults = $svclineQuery->DoFXAction('perform_find');
foreach($svclineResults as $svc) {
$svcline=$svc[ServiceName'];
echo $svcline."<br />";
}
}
Any assistance or direction would be GREATLY appreciated!
Thanks,
Trish V.
_____________________________________________________________________
FX.php Official Web Site -- http://fx.iviking.org/
FX.php Official Mailing List -- //www.freelists.org/list/fx.php_list
(Subscribe, unsubscribe, and more at the mailing list site!)
FX.php_List@xxxxxxxxxxxxxxxx
