Notifications
Announcements
No record found.
Currently I'm using a SmartList called "Account Trans by Orig Doc Number" from the Financial series and then manually filling the last 3 columns (Payment #, Type, Payment Date) as and when payment is done to create the below report. The Date is the TRX Date but I could use the Posting Date as well.
I would like to create a SmartList that somehow generates this report without me having to manually enter the payment details. I am new to GP so I would appreciate a somewhat detailed explanation.
Good day,
Question, why do you prefer the GL for this report and not Payables Transactions?
Regards
Hello!
The reason I need the GL is to be able to filter the items coded to one particular branch - Laurier Brantford.
The branch is represented by a specific GL Account?
Yes, the branch is represented by the specific GL Account starting with 02-1-023-0500 & 02-1-023-0501 and another one for 05
If you are not familiar with SQL as I was when I started using GP this is what I did. I printed the AP posting journals and compared it to the entries in the GL. I would search by a specific GL account or branch and compare them. And this helped in creating Smartlist reports.
For example the voucher number in the AP journal is the Original Control Number, I was able to better understand and see what was being updated.
The information is there, do you have Smartlist Builder or Designer?
I have SmartList Designer.
The objective of Smartlist Designer is to create your own reports. You know what you are looking for, so we have to join certain tables where the respective fields are.
Question, have you used this before?
Yes, sorry, I should have mentioned in my initial post that I have tried to use SmartList Designer to accomplish this. However, I am finding it difficult to locate the appropriate tables and join them correctly and how to use the filter section of the designer if necessary.
Thank you for your patience with this. I do appreciate it.
Hello,
If you are posting to GL in detail, then the 'original posting date' and 'original document numbe'r should already be available fields in the GL table. In the Smartlist, you can click on COLUMNS button at the top, click ADD, Select 'Originating document number', and click OK. Then click ADD again, and select 'Originating posting date' and click OK. ( You can add any other fields you want, and see what they do and then remove them later if they are not helpful). There is also an 'originating transaction type' but this will be an indexed value where in payables an invoice is a 1 and a payment is a 6. The GL table also has 'originating master id' which is the vendor ID from purchasing. You can add more fields as you see fit. Use the buttons on the right to move columns up or down in the CHANGE COLUMN DISPLAY window. Click OK. The smartlist will populate. Click on FAVORITES | ADD FAVORITE to save your new smartlist as a new favorite.
Be aware that Smartlists were designed to return a small amount of records. If you have more than 6-8k records or your tables are large, then Smartlist may not work for you and you may want to consider finding a reporting solution designed to handle large amounts of data. You can reach out to your Microsoft Partner for advice on more reporting solutions.
I hope that helps.
Cheryl Waswick
Microsoft Support
Not a problem, it does take a little getting used to, but practicing is necessary.
As Cheryl indicated you may need to just add columns, that's also why I suggested you look at the posting journals and compare the data being posted to the GL, that is if you are posting in detail.
victoriayudin.com/.../
This site has a listing of the tables and the respective fields, check it out you should find what you are looking for. There's also the GL tables from which you can see which field to join the tables.
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
André Arnaud de Cal... 456 Super User 2025 Season 2
Martin Dráb 429 Most Valuable Professional
BillurSamdancioglu 239 Most Valuable Professional
