How to Add GL Account Column in Smartlist - Purchasing

(0) Share

Report

Posted on by Community Member

Hi everyone,

I try to add column to show GL account in smartlist from "Smartlist - Purchasing - Payable Transactions - Transactions by Vendors".

I guess my challenge here is the total purchase amount splits into different GL accounts.

For example, if I have an invoice for $1,000; $500 goes to travel and another $500 goes to meal. When I add "Purchase Amount" column; it will show $1,000, but there are not any account numbers under "Purchases Account Number" column because this $1,000 has been split unto two amounts in two accounts.

If I put entire $1,000 to meal account, when I run smartlist, Purchase Amount column shows $1,000 and Purchases Account Number shows meal.

Are there any way I can create a smartlist that show me each debit and credit line with GL account number?

Thanks.

Pei

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

MG-16101311-0 26,225 on at

Like (0)

Report

There are two ways to approach this request:

1) You can create a new SmartList Designer smart list based on the Transactions by Vendor smart list. You will add both the PM Distribution WORK OPEN and the PM Distribution History tables from the Purchasing series and create a left join between the Transactions by Vendor.Voucher Number and the Transactions by Vendor.Document Type, and the PM Distribution WORK OPEN.Voucher Number and PM Distribution WORK OPEN.Control Type fields; and the Transactions by Vendor.Voucher Number and the Transactions by Vendor.Document Type and the PM Distribution History.Voucher Number and the PM Distribution History.Control Type.

You will then want to add both the Account Index Master and the Account Master from Microsoft Dynamics GP > Tables > Financial series and relate both the PM Distribution WORK OPEN.Distribution Account Index and the Account Index Master -- this will give you the account number string. Do the same for the PM Distribution History.Distribution Account Index and the Account Index Master.Account Index.

Finally, you will add a filter on the distribution type field for both the PM Distribution WORK OPEN and the PM Distribution History, to ensure you are only getting the expense side of the distribution.

2) The second approach is to use the Financial > Account Transactions smart list and filter out just the purchasing transactions (series 3 I believe).

Was this reply helpful? Yes No
Verified answer

ShawnMD 1,456 User Group Leader on at

Like (0)

Report

I get asked this a lot - and I usually just use this SQL View from Victoria - then add it to smartlists:

https://victoriayudin.com/2008/11/28/sql-view-to-show-all-gl-distributions-for-ap-transactions/

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Mariano. Sorry about this late reply.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Shawn,

One more question if you do not mind. How am I able to import Victoria's SQL view to GP smartlist?

Pei

Was this reply helpful? Yes No
Suggested answer

ShawnMD 1,456 User Group Leader on at

Like (0)

Report

I started typing out the steps and remembered I had a blog in queue for this quick step by step: check it out here:

https://gplifehacks.com/2018/03/06/gp-lifehacks-137-add-a-sql-view-to-smartlist-using-designer/

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am stuck on the last two pieces - there are 4 lines in each string and I think I figured out the first 4 bu the last two joins I cannot figure it out. "You will then want to add both the Account Index Master and the Account Master from Microsoft Dynamics GP > Tables > Financial series and relate both the PM Distribution WORK OPEN.Distribution Account Index and the Account Index Master -- this will give you the account number string. Do the same for the PM Distribution History.Distribution Account Index and the Account Index Master.Account Index.

So the table names remains the same then what is the second category - and does this go into table 2 and then what is the join link - document type or what?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Can you explain the full string. Meaning the Table is, the Field Name, the Table 2, Field Name 2 ... all 4 fields must be completed and I have tried every which way ...

Also, the same for under finally - what is the full 4 strings. Thank you so much.

Was this reply helpful? Yes No