Today’s #TipTuesday is a bit about the “Originating” fields in SmartList, what they are and what you may find in them. These fields are found under the Account Transactions SmartList (under Financial) and in certain circumstances, you will be able to extract *some* subledger information from your G/L to supplement your account analysis.

What are they?

Here are some of the fields and what they may contain. There are some items that I can’t actually tell you what they are for, to be honest!

• Originating Company ID
  • For intercompany transactions
• Originating DTA Series
  • Unsure, perhaps related to MDA (Multi-dimensional analysis)?
• Originating Journal Entry
  • I expected this to be related to Copy or Correct functions but no matter what I tested, nothing populated here. So… I’m not sure anymore about what should be here! (I tested Copy, Back Out and Back Out & Correct entries).
• Originating Debit & Credit amounts
  • When using multi-currency, these are going to be the transaction debits and credits in the original currency of the transaction. In comparison, the regular “Debit” and “Credit” fields will always be the functional currency amounts.
• Originating Source
  • This is often the original Batch Number. There is a field in Account Transactions called Batch Number but it’s usually empty after something is posted. In my Fabrikam data, the Originating Source tended to be useless as the default posting settings are Post To the GL not Post Thru, so my “batch numbers” were the audit trail codes, not the original batch number from the source itself. If posting settings are set to Post Thru for a given Series/Origin, it would show the actual batch in most cases. For GL transactions, it does show the batch number, which is often helpful later.
• Originating Type
  • In my testing, from various transactions, the only thing this shows is “Normal” for a standard GL entry and “Clearing” for a Clearing Entry. Even on a Reversing GL Trx it shows “Normal”.
• Originating Posted Date
  • This is “the date on which the original transaction was posted”, as in the system date.
• Originating TRX Type
  • In my testing, the values here were useless. I believe in the GL tables this would possibly contain the Trx Type integer value of the originating transaction. However, in SmartList, it seems to be translating that to the “word” pair in the GL for that Trx Type value. Example: my data would show “Standard” or “Reversing” in things where that made no sense since the trx wasn’t a GL journal entry originally.
• Originating TRX Source
  • This is the Audit Trail code for the original batch from the originating transaction.
• Originating Control Number
  • This can be the original doc number but not always, especially when the original module has both a doc number field and a sequential transaction number field (like PM Transactions have Voucher Number, which is sequential and independent of the Document Number). So, it’s important to be careful if you are relying on this data for any reporting, when the next field is more reliable for the Doc Number if that is what you are looking for.
  • In the Bank module, the value was an integer but meaningless as far as I can tell.
  • In SOP and RM, the value was the document number.
  • In POP (receiving), the value was the receipt number.
  • In PM (trx and payments), the value was the voucher or payment number.
  • In IV, the value was the transaction number.
• Originating Document Number
  • In comparison to the above field, Originating Control Number, this field is pretty consistently the actual original Doc Number.
  • In Bank, it’s the receipt number, cheque number, or transfer number.
  • In RM and SOP, it’s the document number.
  • In POP, it’s the receipt number. ** This is a notable exception: it did not populate the Vendor Doc Number I put in the receiving transaction, perhaps because it isn’t always a required field (depending if you are entering a Shipment or a Shipment/Invoice).
  • In PM, it’s the document number (or cheque number) .
  • In IV, it’s the transaction number.
• Originating Master ID & Name
  • These are the single most useful fields in the set, IMHO!
  • For Bank module, the ID is the chequebook and the Name is the name on the transaction (i.e. Paid to/Received from). On Bank Transfers, interestingly enough, the ID is “Transfer From:” and the chequebook and the Name is “Transfer To:” and the chequebook.
  • In SOP and RM as well as POP and PM, it’s the customer or vendor ID and Name. I did not test payroll but if you post payroll in detail, this should also be the Employee ID and Name (at least I know it does this with Cdn Payroll if you are posting in detail). ** side note: this would be an important reason NOT to post Payroll in detail as the detail available to anyone with GL smartlist access would be far too much for most organizations!
  • In IV, the fields are empty.
• Originating Sequence Number
  • This also contains a number of things, but primarily appears to be the line sequence number from the originating transction (or that is my guess based on the numbering sequences I’m seeing. I did not correlate that with the subledger transaction to confirm 100%).

Examples

Here is a screenshot of an Excel extract for my testing transactions. On the left, I typed in the nature of the transaction. In many cases I deleted some rows of data just to fit the info into a small picture (i.e. only showing one side of a transaction instead of both debit and credit). I’ve hidden fields that would normally be visible, like Date, Debit/Credit, Account Number etc. because I simply wanted to show you some of the Originating fields on a sample of transactions.

Caveats

Most of the data I’m describing as something that might be visible is based on the default account configuration and posting settings, namely:

• Accounts set to “Detail” under “Level of Posting from Series”
• Posting Settings set to “Create Journal Entry per Transaction”

Under these scenarios, you should see the same kinds of data in my example above, for those kinds of transactions. If you have Summary as Level of Posting, some fields will still populate but you may not see others, like Master ID and Name won’t populate, because the GL entry is a summary.

The Fixed Assets module and the Invoicing module (not RM and not SOP) appear to be the anomaly in the group, based on what I’m seeing. It must be coded differently because both of those modules populate very few of the Originating fields at all - it does not post Master ID, Name, Source, Control Number or Document Number. I didn’t test Manufacturing… FWIW.

The other caveat of this post is I’m describing only what the out-of-the-box Account Transactions smartlist holds and how it translates some of the data. The table itself may contain things that don’t translate in the smartlist (such as the Originating Trx Type not making sense). I didn’t look at the tables at all as the intent of this tip is purely to give some information on what extra info you can get from a GL smartlist that you may not be aware of!

That’s it for this tip… as always, I hope you found it useful!