Today's #TipTuesday is a bit about the "Originating" fields in SmartList, what they are and what one may find in them. These fields are found under the Account Transactions SmartList (under Financial) and in certain circumstances, users will be able to extract *some* sub-ledger information from their GL to supplement their account analysis.
What are they?
Here are some of the fields and what they may contain.
- Originating Company ID - for intercompany transactions
- Originating Journal Entry - I expected this to be related to Copy or Correct functions but no matter what I tested, nothing populated here.
- 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 empty after it 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 Posted Date - this is "the date on which the original transaction was posted", as in the system date.
- 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 users are relying on this data for any reporting when the next field is more reliable for the Doc Number if that is what they 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 (transaction 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 consistently contained 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 on if users 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 Sequence Number
- This also contains several things but primarily appears to be the line sequence number from the originating transaction (or that is my guess based on the numbering sequences I'm seeing. I did not correlate that with the sub-ledger transaction to confirm 100%).
- Originating Master ID & Name - these are the single most useful fields in the set, IMHO!
- For the 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.
- In IV, the fields are empty.
- I did not test payroll but if payroll is posted in detail, this would also be the Employee ID and Name (at least I know it does this with Cdn Payroll if posting in detail).
Examples
Here is a screenshot of an Excel extract for my testing transactions. On the left, I typed in the source 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 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 are set to "Detail" under "Level of Posting from Series"
- Posting Settings set to "Create Journal Entry per Transaction"
Under these scenarios, users should see the same kinds of data in my example above, for those kinds of transactions. When the Level of Posting is "Summary", some fields will still populate but users may not see other fields 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 one can get from a GL smartlist that they may not be aware of!
That's it for this tip… as always, I hope this was useful!
