Technical Support Hours

M-F 8am to 8pm (EST)

Start a conversation

Custom Customer Card Banner Alerts

Overview

Users may use SQL to add a customized banner to the Customer Card to alert the user of a particular characteristic of the customer, defined in a user defined field. For example, the banner can be used to indicate the customer’s discount percentage, or to indicate that a customer is assigned to a particular dealer. A yellow banner will appear across the top of a Customer Card if the user field called on in the SQL script is populated:

Note: Completing a database update will clear the customizations made to the views. You must reapply changes to the views after any SalesPad database updates.

Use

You will need to modify the (spvCustomer_base) and change the line ( [Message] = '', ). As an example, we are going to look at a customer and see if they have a discount set up in the customer user field. So our spvCustomer_base has been changed to this:

,[Message] = CASE
WHEN (Len(x.[xCustomerDiscount]) > 0)
THEN 'This customer discount is ' + x.[xCustomerDiscount] 
ELSE ''
END 

We then also had to join to the spxCustomer table to pull this field, so we added this line:

left join spxCustomer as x (nolock) on x.Customer_Num=RM00101.CUSTNMBR

When added to the view from v.4.6.3.4:

USE [TWO]
GO
/****** Object:  View [dbo].[spvCustomer_base]    Script Date: 9/20/2018 3:57:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* View -spvCustomer_base */
ALTER view [dbo].[spvCustomer_base]
AS
SELECT [Customer_Num] = RM00101.CUSTNMBR
    ,[Customer_Name] = CUSTNAME
    ,[Customer_Class] = CUSTCLAS
    ,[Corporate_Customer_Num] = CAST(CPRCSTNM AS VARCHAR(25))
    ,[Short_Name] = SHRTNAME
    ,[Statement_Name] = STMTNAME
    ,[Primary_Addr_Code] = UPPER(RM00101.ADRSCODE)
    ,[Primary_Bill_To_Addr_Code] = UPPER(PRBTADCD)
    ,[Primary_Ship_To_Addr_Code] = UPPER(PRSTADCD)
    ,[Statement_To_Addr_Code] = UPPER(STADDRCD)
    ,[Sales_Person_ID] = SLPRSNID
    ,[Sales_Territory] = SALSTERR
    ,[Payment_Terms] = upper(PYMTRMID)
    ,[Shipping_Method] = SHIPMTHD
    ,[Price_Level] = isnull(PRCLEVEL, '')
    ,[User_Def_1] = USERDEF1
    ,[User_Def_2] = USERDEF2
    ,[Tax_Exempt_1] = TAXEXMT1
    ,[Tax_Exempt_2] = TAXEXMT2
    ,[Tax_Registration_Num] = TXRGNNUM
    ,[Comment_1] = COMMENT1
    ,[Comment_2] = COMMENT2
    ,[IntegrationSource] = INTEGRATIONSOURCE
    ,[Inactive] = cast(INACTIVE AS BIT)
    ,[On_Hold] = cast(HOLD AS BIT)
    ,[Note] = ISNULL(TXTFIELD, '')
    ,[Currency_ID] = isnull(isnull(custCurr.CURNCYID, funcCurr.CURNCYID), '')
    ,[Currency_Dec] = isnull(isnull(custCurr.DECPLCUR, funcCurr.DECPLCUR), 3) -1
    ,Last_Aged = LASTAGED
    ,Balance = CUSTBLNC
    ,Unapplied_Amount = (
        SELECT sum(curtrxam)
        FROM RM20101 AS ar(NOLOCK)
        WHERE ar.CUSTNMBR = RM00101.CUSTNMBR
            AND RMDTYPAL = 9
            AND VOIDSTTS = 0
            AND curtrxam > 0
        )
    ,Customer_Credit_Limit = CRLMTAMT
    ,Credit_Limit_Type = CRLMTTYP
    ,Finance_Charge_Type = FNCHATYP
    ,Finance_Charge_Amt = FINCHDLR
    ,Finance_Charge_Pct = FNCHPCNT
    ,Min_Pmt_Type = MINPYTYP
    ,Min_Pmt_Amt = MINPYDLR
    ,Min_Pmt_Pct = MINPYPCT
    ,Balance_Type = BALNCTYP
    ,Max_Writeoff_Type = MXWOFTYP
    ,Max_Writeoff_Amt = MXWROFAM
    ,Last_Pay_Date = LASTPYDT
    ,Last_Pay_Amt = LPYMTAMT
    ,First_Invoice_Date = cs.FRSTINDT
    ,Last_Invoice_Date = LSTTRXDT
    ,Last_Invoice_Amt = LSTTRXAM
    ,Last_Stmt_Date = LASTSTDT
    ,Last_Stmt_Amt = LSTSTAMT
    ,Life_Avg_Days = AVDTPLIF
    ,Year_Avg_Days = AVGDTPYR
    ,Total_Amt_NSF_Checks_YTD = cast(TNSFCYTD AS NUMERIC(19, 5))
    ,Num_NSF_Checks_YTD = cast(NONSFYTD AS INT)
    ,[Tax_Schedule] = TAXSCHID
    ,[Ship_Complete] = Cast(SHIPCOMPLETE AS BIT)
    ,[Stmt_Email_To] = dbo.spfGetCustomerEmail([RM00101].CUSTNMBR, 1)
    ,[Stmt_Email_CC] = dbo.spfGetCustomerEmail([RM00101].CUSTNMBR, 2)
    ,[Stmt_Email_BCC] = dbo.spfGetCustomerEmail([RM00101].CUSTNMBR, 3)
    ,[Email_To] = email.EmailToAddress
    ,[Email_CC] = email.EmailCcAddress
    ,[Email_BCC] = email.EmailBccAddress
    ,[Message] = CASE
WHEN (Len(x.[xCustomerDiscount]) > 0)
THEN 'This customer discount is ' + x.[xCustomerDiscount]
ELSE ''
END
    ,--Used for FB 2388 ORD011021 -R Jorgenson Banner Custom
    USERDEF1
    ,USERDEF2
    ,[Trade_Discount] = (cast(CUSTDISC AS NUMERIC(19, 5)) / 100)
    ,[Master_Distributor] = ISNULL(c.[Master_Distributor], '')
    ,[Method_Of_Billing] = ISNULL(c.[Method_Of_Billing], 0)
    ,[Send_Email_Statements] = ISNULL(Send_Email_Statements, 0)
    ,[Created_On] = [RM00101].CREATDDT
    ,[Changed_On] = [RM00101].MODIFDT
    ,[Promotions_Applied_Customer]
    ,[DEX_ROW_TS] = [RM00101].DEX_ROW_TS
    ,[On_Order_Amount] = cs.ONORDAMT
    ,[Language_ID] = USERLANG
    ,[Combine_Split_Docs] = c.Combine_Split_Docs
FROM RM00101(NOLOCK)
LEFT JOIN spCustomer(NOLOCK) AS c ON c.[Customer_Num] = RM00101.CUSTNMBR
LEFT JOIN SY03900(NOLOCK) ON SY03900.NOTEINDX = RM00101.NOTEINDX
LEFT JOIN RM00103 AS cs(NOLOCK) ON cs.CUSTNMBR = RM00101.CUSTNMBR
LEFT JOIN DYNAMICS16..MC40200 AS custCurr(NOLOCK) ON custCurr.CURNCYID = RM00101.CURNCYID
LEFT JOIN MC40000 AS currSetup(NOLOCK) ON 1 = 1
LEFT JOIN DYNAMICS16..MC40200 AS funcCurr(NOLOCK) ON funcCurr.CURNCYID = currSetup.FUNLCURR
LEFT JOIN SY01200 AS email(NOLOCK) ON RM00101.CUSTNMBR = email.Master_ID
    AND email.ADRSCODE = RM00101.ADRSCODE
    AND email.Master_Type = 'CUS'
LEFT JOIN spxCustomer AS x(NOLOCK) ON x.Customer_Num = RM00101.CUSTNMBR

GO

After updating the view, when we log in and open a Customer Card that has a customer discount applied to it, the custom banner will appear:

Choose files or drag and drop files
Was this article helpful?
Yes
No
  1. SalesPad Support

  2. Posted
  3. Updated

Comments