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!

Dave Edis - Senior Developer

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 KennyH - August 2, 2017

That worked better! No errors

Thanks,

Kenny