Skip to content
+1-888-319-3663

COMMUNITY FORUM

HOW TO PICK UP DATA FROM TWO DIFFERENT COLUMN (TABLE FIELDS) BASED ON CONDITION IN SMARTLIST BUILDER???

Pervez Iqbal asked 3 years ago
Hi,
 
I have problem in Great Plains Smart List report  name” CASH RECEIPTS ENTERED TODAY”. The problem is, I want to add Sales Territory  on this smartlist. so I added column SALES TERRITORY In this SmartList but it does not show the accurate data , specially for those transactions that are coming from Sales Order Processing Module. This Sales Territory field shows correct data, if the transaction was entered in Receivable Module, but it does not show the correct sales Territory codes for those transactions , which were entered in Sales Order Processing (SOP) Module, like sales Invoices or Return Transactions. so I  created a SQL View, got the correct SALES TERRITORY codes for SOP module based transactions, but the problem is,  now I have 2 different fields in SMartList, One field (Column) shows the correct SALES TERRITORY Codes ,  for Sales Order Processing Module based transactions, and the 2nd field (column)  in the smartlist shows Sales Territory codes for Receivable module based transactions. But I want to show 1 column in this smartlist that shows the accurate SALES TERRITORY CODES for both type of transactions . I am using SMARTLIST Builder and not getting , how to overcome this issue? Do I use Calculation, but SMARTLIST BUILDER is not showing  (IF THEN ELSE) Command. Can you please help me out? will appreciate your help.  
Answers
Nicole Albertson Staff answered 3 years ago
Combining the two fields is definitely possible.  You are going to want to use a Case statement.  It is under the System functions list.  Not knowing the exact tables you are using, we cannot give anything real specific, but if the Sales Territory is empty in the first field you had when it is from RM, you could do something like this.
CASE
WHEN {RM History File:Sales Territory Code} <> ”  THEN {RM History File:Sales Territory Code}
WHEN {Sales Transaction History:Sales Territory Code} <> ” THEN {Sales Transaction History:Sales Territory Code}
ELSE ”
END
 
If you have a different field that you can decipher whether you want to use the RM or SOP Sales Territory, you would just need to change the when clauses to match that criteria.
 
Hope this helps.

If you would like to submit an answer or comment, please sign in to the eOne portal.