Open Forum

Expand all | Collapse all

Smartlist Builder String field showing as Integer

  • 1.  Smartlist Builder String field showing as Integer

    GOLD CONTRIBUTOR
    Posted 15 days ago
    Hello,
    Has anyone ever had the following situation and if so, how do you fix it?  I added the Vendor Class ID to a SQL View that was the foundation of a Smartlist Builder smartlist.  I rebuilt the view, reran Grant.SQL and Refreshed Lists in Smartlist Builder but the field is displaying a 1 or a 2 when the class ID is actually a word (see the screenshots).  Also, if I run a Select statement in SQL, the values in the Class ID field are the correct values and not the numbers.  Also, if I open the Smartlist and click on Options > Preview Data, it shows the correct values as well.  I can't figure out where to go to fix this.  Any ideas?  Thank you!


    Smartlist (see Vendor Class ID)


    Vendor Card-see Vendor Class ID


    ------------------------------
    Melanie Knight
    System Administrator
    NetJets Inc.
    Columbus OH
    ------------------------------


  • 2.  RE: Smartlist Builder String field showing as Integer

    TOP CONTRIBUTOR
    Posted 14 days ago
    Melanie,

    I have a couple of questions for you.
    1.  Which View did you modify and what did you exactly add to it so I can test it out?
    2.  When you are creating the setup in SmartList Builder, are you adding the View as a SQL Table or using the existing SmartList option?
    3.  If you look at the field in the main SmartList Builder window, what data type is it picking up there?  Is it a string, integer, or something else?

    We generally don't recommend modifying the default SLB... views in your company and Dynamics databases as they may get recreated with any update you apply for SmartList Builder and then your changes would be gone.  We would recommend either creating a new view from it with your changes or adding the default one into SmartList Builder and then adding the tables you need to it to get the data you need.


    ------------------------------
    Nicole Albertson
    Product Manager
    eOne Solutions
    ------------------------------



  • 3.  RE: Smartlist Builder String field showing as Integer

    GOLD CONTRIBUTOR
    Posted 14 days ago
    @Nicole Albertson,
    The View I modified was one I created for this report. I added the Vendor Class ID field from the Vendor Master table.  The join already existed from the original view.

    SELECT dbo.PM10300.BACHNUMB, dbo.PM20100.DOCDATE, dbo.PM20100.VENDORID, dbo.PM00200.VNDCLSID, dbo.PM20100.APTODCDT, dbo.PM20100.APTODCNM, dbo.PM20100.APPLDAMT,
    dbo.SY06000.EFTBankAcct, dbo.SY06000.EFTTransitRoutingNo, DATEDIFF(day, dbo.PM20100.APTODCDT, dbo.PM20100.DOCDATE) AS DPO,
    dbo.PM00200.VENDNAME, dbo.PM00200.PYMTRMID, dbo.PM00200.PYMNTPRI, dbo.PM20100.DISTKNAM
    FROM dbo.PM20100 LEFT OUTER JOIN
    dbo.PM00200 ON dbo.PM20100.VENDORID = dbo.PM00200.VENDORID LEFT OUTER JOIN
    dbo.SY06000 ON dbo.PM20100.VENDORID = dbo.SY06000.VENDORID FULL OUTER JOIN
    dbo.PM10300 ON dbo.PM20100.VCHRNMBR = dbo.PM10300.PMNTNMBR

    I added the view as a SQL table in SLB and the field is a String field (see screenshot).

    Screenshot


    Thanks,
    Melanie



    ------------------------------
    Melanie Knight
    System Administrator
    NetJets Inc.
    Columbus OH
    ------------------------------



  • 4.  RE: Smartlist Builder String field showing as Integer

    TOP CONTRIBUTOR
    Posted 14 days ago
    Melanie,

    I created a new view from your select statement and added it to a new SmartList Builder setup.  The Vendor Class ID is pulling in just fine. If you create a new list in SLB and add the view, does it display properly?  I wonder if it is just this one that you added it to after the fact that we need to try to figure out.

    If that does work, you could try removing the table from the setup and reading it to your existing setup if you have favorites you are trying to save.  If you don't have any favorites to save, you could either use a new one or try to re-add the view to the setup.

    ------------------------------
    Nicole Albertson
    Product Manager
    eOne Solutions
    ------------------------------



  • 5.  RE: Smartlist Builder String field showing as Integer

    GOLD CONTRIBUTOR
    Posted 14 days ago
    Thank you @Nicole Albertson!

    That did the trick!​

    ------------------------------
    Melanie Knight
    System Administrator
    NetJets Inc.
    Columbus OH
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role