We use the Inquiry form Sales - Transactions by Customer on a regular basis. We have quite a few customers that a large wholesalers and they have many different shipto addresses used when we invoice. We want to see this same graph of historical and open transactions but with shipto address info tagged to it so we can identify not just the customer, but where it was sent. I am happy to recreate this in smartlist to avoid modifying GP and using something like the receivable transactions, but when I add the sales transaction table to these reports, I end up losing the payment detail because there is no 'shipto' associated with payments. There appears to be an issue with joining tables properly to see all historical/open transactions and adding this shipto element. Please help, how to I build this in a way that reproduces this inquiry but also shows shipto for each item (or blank when not applicable).
Good morning, afternoon, or evening depending on your location! SmartList Designer
Base SmartList Setup:
Open SmartList Designer.
Create a new SmartList based on the Receivables Transactions table (this provides historical and open transactions data).
Include relevant fields such as Customer ID, Document Type, Document Number, Transaction Amount, and Payment Details.
Join Sales Transaction Table:
Add the Sales Transaction Header table and join it to the Receivables Transactions table using fields like Customer ID and Document Number.
Make sure to include ShipTo Address fields from the Sales Transaction Header table in your SmartList.
Left Join Handling:
Use a LEFT JOIN so that all Receivables Transactions (including payments without ShipTo addresses) remain visible, and ShipTo details are added only for relevant transactions.
Conditional Logic:
Add a custom column to display "N/A" or leave it blank when ShipTo information is not available for a transaction.
Save and Test:
Save the SmartList and test it to ensure all historical/open transactions are displayed along with ShipTo details.
SQL-Based Approach (If SmartList Designer is limiting)
Data Query:
Use SQL Server Management Studio to write a custom query joining the Receivables Transactions and Sales Transaction Header tables.
Use fields like Document Number and Customer ID to link the tables accurately.
Include ShipTo Details:
Include ShipTo Address fields (such as ShipTo Name, Address, City, etc.) from the Sales Transaction Header table in your query.
Use a LEFT JOIN to ensure all payment transactions are included, even when ShipTo details are missing.
Custom Logic for Blank ShipTo:
Add conditional SQL logic to handle null ShipTo values (e.g., display "Not Applicable" or leave blank).
Deploy Report:
Save the query and use tools like SQL Reporting Services (SSRS) or Excel Power Query to display the data in the desired format.
By following these steps, you can create a dynamic report that includes historical and open transactions with ShipTo address information tagged where applicable. Hope this helps some!
Was this reply helpful?YesNo
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.
4
Share
Report
All responses (
Answers (
