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