Displaying Info From Two Sections
5 posts by 2 authors in: Forums > CMS Builder
Last Post: August 2, 2017 (RSS)
By KennyH - July 21, 2017
I have scoured these forums for at least an hour trying to find the answer to this but can't find just the right info. I appreciate the help in advance!
I have two sections: CLIENTS and INVOICES
In the backend, when you create an invoice, you select the client from a dropdown list that gets it's options from the CLIENTS section.
Now I want to create an invoice with the information from both sections combined
list($invoicesRecords, $invoicesMetaData) = getRecords(array(
'tableName' => 'invoices',
'where' => whereRecordNumberInUrl(0),
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$invoicesRecord = @$invoicesRecords[0]; // get first record
list($clientsRecords, $clientsMetaData) = getRecords(array(
'tableName' => 'clients',
'where' => whereRecordNumberInUrl(0),
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$clientsRecord = @$clientsRecords[0]; // get first record
The display of the information would look like this
<address>
<?php echo $invoicesRecord['client:label'] ?>
<?php echo htmlencode($clientsRecord['address']) ?>
<?php echo htmlencode($clientsRecord['phone']) ?>
<?php echo htmlencode($clientsRecord['email']) ?>
</address>
How do I get the client's address information in the CLIENTS section to show up on the invoice with the information from the INVOICE section?
Thanks,
Kenny
By Dave - July 28, 2017
Hi Kenny,
Sorry for the delay in responding. Here's some code that loads a list of associated records and lets you access it by record number:
// get associated records
$clientNums = array_column($invoicesRecords, 'clientNum');
$clientNums = array_filter(array_unique($clientNums)); // remove blanks and duplicates
$clientNumsAsCSV = mysql_escapeCSV($clientNums); // comma separated values, eg: 1,2,3
$clientRecords = mysql_select('clients', " num IN ($clientNumsAsCSV) ");
$clientRecordsByNum = array_groupBy($clientRecords, 'num');
So first you'll want to make sure that you're associating your records with their record numbers. So $invoicesRecord['client'] should be a number. In fact, I often name the field clientNum to reflect that. The reason you want to use a number is so that if the client name is changed (typo, correction, capitalization, etc) that the records are still associated. Because we know the number won't change.
Next, how the above code works is it gets a list of client numbers from $invoicesRecords, then loads all the clients into $clientRecords that have one of those numbers. Then creates an array $clientRecordsByNum with the client arranged by number. So this let's you do this:
<address>
<?php echo $invoicesRecord['clientNum:label']; ?>
<?php $invoiceClient = $clientRecordsByNum[ $invoicesRecord['clientNum'] ]; ?>
<?php echo htmlencode($invoiceClient['address']); ?>
<?php echo htmlencode($invoiceClient['phone']); ?>
<?php echo htmlencode($invoiceClient['email']); ?>
</address>
Let me know how it goes!
interactivetools.com
By KennyH - July 28, 2017
Hi Dave -
I am running into a few problems. I am getting the following error
Notice: Undefined variable: invoicesRecords in /home/sageme/public_html/includes/sections/invoice2.php on line 6
Warning: array_column() expects parameter 1 to be array, null given in /home/sageme/public_html/includes/sections/invoice2.php on line 6
Warning: array_unique() expects parameter 1 to be array, null given in /home/sageme/public_html/includes/sections/invoice2.php on line 7
Warning: array_filter() expects parameter 1 to be array, null given in /home/sageme/public_html/includes/sections/invoice2.php on line 7
Warning: Invalid argument supplied for foreach() in /home/sageme/public_html/webadmin/lib/mysql_functions.php on line 183
Notice: Undefined offset: 38 in /home/sageme/public_html/includes/sections/invoice2.php on line 43
My Page code looks like this:
<?php require_once "/home/sageme/public_html/webadmin/lib/viewer_functions.php";
$domain = "https://" . $_SERVER['HTTP_HOST'];
// get associated records
$clientNums = array_column($invoicesRecords, 'clientNum');
$clientNums = array_filter(array_unique($clientNums)); // remove blanks and duplicates
$clientNumsAsCSV = mysql_escapeCSV($clientNums); // comma separated values, eg: 1,2,3
$clientRecords = mysql_select('clients', " num IN ($clientNumsAsCSV) ");
$clientRecordsByNum = array_groupBy($clientRecords, 'num');
list($invoice_templateRecords, $invoice_templateMetaData) = getRecords(array(
'tableName' => 'invoice_template',
'where' => '', // load first record
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$invoice_templateRecord = @$invoice_templateRecords[0]; // get first record
list($invoicesRecords, $invoicesMetaData) = getRecords(array(
'tableName' => 'invoices',
'where' => whereRecordNumberInUrl(0),
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$invoicesRecord = @$invoicesRecords[0]; // get first record
list($clientsRecords, $clientsMetaData) = getRecords(array(
'tableName' => 'clients',
'where' => whereRecordNumberInUrl(0),
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$clientsRecord = @$clientsRecords[0]; // get first record
?>
<address>
<?php echo $invoicesRecord['clientNum:label']; ?>
<?php $invoiceClient = $clientRecordsByNum[ $invoicesRecord['clientNum'] ]; ?>
<?php echo htmlencode($invoiceClient['address']); ?>
<?php echo htmlencode($invoiceClient['phone']); ?>
<?php echo htmlencode($invoiceClient['email']); ?>
</address>
I think I might be missing something in the implementation of the code you suggested.
Kenny
By Dave - July 28, 2017
Hi Kenny,
If you only need to load client info for a single invoice we can make it even simpler. I was thinking you had multiple invoices on the same page. Try this:
<?php require_once "/home/sageme/public_html/webadmin/lib/viewer_functions.php";
$domain = "https://" . $_SERVER['HTTP_HOST'];
list($invoice_templateRecords, $invoice_templateMetaData) = getRecords(array(
'tableName' => 'invoice_template',
'where' => '', // load first record
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$invoice_templateRecord = @$invoice_templateRecords[0]; // get first record
list($invoicesRecords, $invoicesMetaData) = getRecords(array(
'tableName' => 'invoices',
'where' => whereRecordNumberInUrl(0),
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$invoicesRecord = @$invoicesRecords[0]; // get first record
// get associated client record
$invoiceClient = mysql_get('clients', $invoicesRecord['clientNum']);
?>
<address>
<?php echo $invoicesRecord['clientNum:label']; ?>
<?php echo htmlencode($invoiceClient['address']); ?>
<?php echo htmlencode($invoiceClient['phone']); ?>
<?php echo htmlencode($invoiceClient['email']); ?>
</address>
Let me know if that works for you.
interactivetools.com