Smartlist Builder

(0) Share

Report

Posted on by Community Member

So I have created a smartlist using smartlist builder consisting of four tables, Sales Transaction History, Sales Transaction Amounts History (linked to Sales Transaction History via SOP Number), Customer Master Address File (linked to Sales Transaction History via Customer Number), and Bank Account Master (linked to Customer Master Address File via Customer Number). I am essentially trying to pull the same information that the Sales Line Item smartlist pulls but with added fields such as Bank Branch, Bank Name, User Defined 1 and 2 (Master Address File). When I run the smartlist by typing a specific invoice # using SOP Number field, the smartlist returns nothing. I am not sure why this is. I don't know if it has to do with the field links. Any help is greatly appreciated.

*This post is locked for comments

All responses (8)

Answers (0)

Suggested answer

KirkLivermont 5,985 on at

Like (0)

Report

RE: Smartlist Builder

Jesus,

That sounds like an equals join between the customer master address table and the bank account master table. If the tables are joined using an equals join there needs to be a 1 to 1 relationship between the two tables. Using a left outer joins allows for a 1 to many (or none) relationship.

Regards,

Kirk
Community Member on at

Like (0)

Report

RE: Smartlist Builder

Thank you all for all your responses, they have definitely helped out a lot. My status is that I was able to pull data but the Bank Name and Bank Branch are blank, it did not pull it from the Customer ID card.
Suggested answer

KirkLivermont 5,985 on at

Like (0)

Report

RE: Smartlist Builder

Jesus,

Knowing the back end SQL is definitely a plus but I don't think it is necessary in this case. I think you are getting close but you need an additional link between the Customer Master Address table and the Sales Transaction History table. I believe you want to link these two on address id and customer number.

Regards,

Kirk
Community Member on at

Like (0)

Report

RE: Smartlist Builder

Can you post your SQL statement, would be a lot easier to help then.
Community Member on at

Like (0)

Report

RE: Smartlist Builder

I changed all links to left outer and it actually pulled information. Only issue is that it pulled 294 line items for an invoice that only has 7 lines. Do you know why this is? I am guessing because it is pulling every User Defined 1 from every Ship To and Bill to? Also the Bank Name and Bank Branch are blank, it did not pull it from the Customer ID card. I guess I will try to find these fields in another table.

Thank you for your help, more feedback is appreciated.
Community Member on at

Like (0)

Report

RE: Smartlist Builder

Thank you for your help. I am not really familiar with SQL but it sounds like I probably should since it seems that it would be easier to pull this data. Thanks again!
Suggested answer

KirkLivermont 5,985 on at

Like (0)

Report

RE: Smartlist Builder

Hi Jesus,

Are you using left outer joins? It sounds like you might have an equals join in there somewhere causing the problem.

Also when dealing the the two SOP tables it is recommended that you join on both SOP Type and SOP Number.

Regards,

Kirk
Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: Smartlist Builder

Well, you must be missing something with the joins

Although you are definitely on the right track, I would consider creating the view on the SQL level, it would be quite easier to manipulate and prepare your data set and then pick the view so simply from the smart list builder level.

In both cases, let us know if you need any further assistance. For the SQL view, you may post back a sample for the report layout and design including the required fields regardless of the tables and sources.

Your feedback is highly appreciated,