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 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.

Dave Edis - Senior Developer
interactivetools.com

By KennyH - August 2, 2017

That worked better! No errors

Thanks,

Kenny