Distribution Tables - adding the GL accounts to Builder Lists

As Part two of my response to a question on my Suggestions Page, I thought I would discuss the Distributions tables from GP transactions.

Couple points to remember when thinking about adding Distribution tables to your lists in Builder:

• Transaction Distribution tables are different then the actual Journal Entries stored in the Financial Tables. 
  • Distribution tables store data per transaction and do not contain JE numbers. 
  • Depending on the posting setting in GP the JE in Financial tables could be for one or many transactions. (Or could not make a JE at all.)  If your posting setting are per transaction, your could try to link the Financial tables – but this is not a good link via SmartList Builder – most of the time you will need to do a SQL view. (Issue with key fields.. whole different post…)
• There will be at least two entries for each Transaction and may have many more.   IE – Taxes, Misc, COGS, etc –  all of the entries on the Distribution Button on the transaction. (Important point – since this will cause transactions to be listed several times on your final list.)

• There is a Distribution type field that can be used to limit what parts of the entry are shown on your list. (For example, you may not want to have the Accounts Payable accounts show on a Payables transaction report.)  This is a Dropdown type field – unfortunately Builder generally doesn’t know the values for these by default – but typically they are in order on the dropdown on the transaction entry screen.  See my post: Drop Down Lists in SmartList Builder (Document Type on Transactions, etc) for more info on this type of field.

• Most Distribution tables store records for various GP status (WORK & OPEN)  or (WORK & HIST).   Avoid the tables with the word TEMP in them – they don’t have what you want!   Some common tables:
  • PM Distribution WORK OPEN File  / PM Distribution History File
  • RM Distribution Work File (Don’t be confused by the name – this has Open too!) / Receivables GL Distribution History File
  • Sales Distribution Work and History
• Generally, you will link to the Transaction table with the key fields from the Transaction table – often the GP assigned unique transaction ID (Voucher number, SOP Number, or Document Number) and the Document Type field. To find these – see my post: Adding additional tables to your SmartList (Successfully)

Good luck & Happy Building!
Devon

About these ads