Home > General - Builders, Payables Management > Matched Tables – Viewing Open & History Documents on the same SmartList in Builder (just like the default Lists!)

Matched Tables – Viewing Open & History Documents on the same SmartList in Builder (just like the default Lists!)

One of the features of the default Lists in SmartList, Excel Reports & Navigation Lists is that you can often see ALL GP transactions for a certain type on one list – Work (unposted), Open (unpaid or not moved to history), and History (moved to history).   Now – don’t get me wrong – sometimes this feature is great, but a lot of my Builder Lists are to isolate just one status of GP transactions.  (For example, most of my clients that use Sales Order Processing have separate Open & History Lists from builder…)

What if you DO want more than one status on a Builder List?    On first glance, it seems difficult or impossible – that data is in separate tables and you can’t just start with the open table and add the work table (there wouldn’t be matching records – so you won’t get anything!)  Don’t worry – there is another way.

For example, you want to build a SmartList for payables transactions for a certain period of time and all of the distributions of these documents.  You really don’t care if the documents are paid or not (How they move to History in PM), you still want the documents to show on your list.

Matched Tables

  • First Start like normal and add your Transaction table for one status (I like to use open, but you could use history – it doesn’t matter).
  • On the Add Table screen – look for the Button “Matched Tables” on the bottom and press this button.

 
On additional tables (second or more) you will have to save the table and then edit it (paper and pen icon) to see the Matched Tables button:

  • On the Matched tables screen, Hit the plus sign on the left section to add your equivalent table for the other desired GP status (You can add more than one, if desired.)

You will see that on the right, Builder has already matched equivalent fields (If a field isn’t matched, it won’t show in the final list).  You can review and change if desired.

  • Press OK and Save back on the Add Table screen

Now you are done!   It is that easy.  

What is does is the equivalent of a SQL UNION ALL query.  It selects all of the data from the first table and then, as part of the same set of data goes and select all of the data from the second or Matched table.

Happy Building,
Devon

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s