Open Forum

Expand all | Collapse all

Editing a Database View for Smartlist

Jump to Best Answer
  • 1.  Editing a Database View for Smartlist

    Posted Feb 11, 2020 02:27 PM
    Is editing a database View used for Smartlists possible?
    We would like to add the ONEPAYPERVENDINV column to our Vendor Smartlist but under the Smartlist Builder, it's not listed. Database view slbVendors populates the Vendors Smartlist where the ONEPAYPERVENDINV column isn't in the properties.

    Can I safely ALTER VIEW slbVendors by including the ONEPAYPERVENDINV column and have it propagate as expected to our current Smartlists?

    If editing the View isn't possible, how else can this column be added to the Vendors Smartlist?

    Version 18.2.1036​

    #Smartlist

    Thanks again

    ------------------------------
    James Patt
    Programmer
    Western Region OTB Corporation
    Batavia NY
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Editing a Database View for Smartlist

    GPUG ALL STAR
    Posted Feb 12, 2020 07:38 AM
    Hi @James Patt

    I never recommend editing the out of the box GP Views.

    HOWEVER, you can open the view with Script As Create give it a NEW view name and add your column, run it to create the view then add this line "GRANT SELECT ON THE_NAME_OF_YOUR_NEW_VIEW to DYNGRP"  This will give your new view the same permissions as the old.

    You can then create a NEW Smartlist by using either SmartList Designer or SmartList Builder by simply pointing to your new view and adding it to SmartLists.​

    ------------------------------

    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Editing a Database View for Smartlist

    TOP CONTRIBUTOR
    Posted Feb 12, 2020 08:33 AM
    James,

    You got great advice from Jo.  One of the many reasons not to update the out-of-the-box SQL code is it could cause your next upgrade to fail.  Anything new that you add should carry along with the database just fine.

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Editing a Database View for Smartlist

    TOP CONTRIBUTOR
    Posted Feb 12, 2020 09:15 AM
    Edited by John Arnold Feb 12, 2020 09:17 AM
    Hi James,

    I would recommend another step after doing the Script As of the view you want to copy.  Use poorsql.com (or use a similar tool) to format the really really really long/single query into something that is more readable.  :)

    I'd also consider putting a _ in front of your new view name.   That way, your view will appear at the top of the list of view in SQL Management Studio.  It's a great way to easily find your customized views.

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Editing a Database View for Smartlist
    Best Answer

    TOP CONTRIBUTOR
    Posted Feb 12, 2020 09:30 AM
    I would agree with the others that you shouldn't edit the SLBVendors view or any other SLB view.  When we update SLB, it is possible that we would drop and recreate the view or that your modifications could break other SmartList Builder lists you have built off of it.  Even with editing the view, you would still have to either modify the default Vendor SmartList or create a new one as the default Vendors list doesn't pull from the SLBVendors view.

    I have another suggestion for you that doesn't involve having to create any views and manage their security.

    In SmartList Builder, I would either Modify the default Vendor SmartList by selecting Existing and then selecting the Vendors SmartList, or creating a new one and add the Vendors SmartList using the SmartList table type.  Once you have one of the two added, then add on the PM Vendor Master File GP table.  From there you can turn on the "One Day Per Vendor Invoice" field.  If you choose to create a new SmartList, keep in mind that you will also have to add in the Built In GoTo's for the Vendor SmartList.



    Let me know if you have any questions on this option.

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

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Editing a Database View for Smartlist

    Posted Feb 13, 2020 03:20 PM
    Thanks for all the help and feedback. I have a continuing issue with granting permission to a new Smartlist.

    So I took your advice and created a new database view [slbVendorsOnePmnt] based on the existing slbVendors view.
    GRANT DELETE, INSERT, SELECT, UPDATE [slbVendorsOnePmnt] TO [DYNGRP]
    In GP signed in as sa...,
    In MicrosoftDynamics GP/Tools/SmartList Builder/ Security/ SQL Table Security, checkmarked the new database view

    Goto SmartList Builder, created a new Smartlist to use the above View

    In MicrosoftDynamics GP/Tools/Setup/System/SecurityTasks/, created a new Security Task
    Task ID: SMARTLIST_VENDORONEPMNT
    Product: SmartList
    Type: SmartListObjects
    Series: SmartListObjects
    User Type: Full
    Access List: <name of new Smartlist>

    At this point a user with RoleID of POWERUSER can see and run the new SmartList

    However, I want a user not a POWERUSER to run the SmartList. So I went into Security Role Setup, selected Role ID 'Purchasing Inquiry' that's assigned to the User and added the above created Security Task to the Role ID.

    The user can see and select the SmartList but gets a 'You do not have security privileges to view all of the tables used in the Smart List'

    I also created another Security Task
    Task ID: SMARTLIST_VENDOR1PMNT
    Product: SmartList Builder
    Type: SmartList Builder Permissions
    Series: SmartList Builder
    User Type: Full
    Acess List: checkmarked View SmartLists with SQL Tables

    Task ID: SMARTLIST_VENDOR1PMNT was also added to the above RoleID with no difference.

    What am I missing? Is there a specific order to creating and adding a SmartList?​

    thanks again.

    ------------------------------
    James Patt
    Programmer
    Western Region OTB Corporation
    Batavia NY
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Editing a Database View for Smartlist

    Posted Feb 17, 2020 11:54 AM
    There is a little right that trips me up every time and I know it is there.  The users that need to access SmartLists need the following item added to a task that is in their role.

    Product:  SmartList Builder
    Type:  SmartList Builder Permissions
    Series:  SmartList Builder

    And the Operation or security item that needs to be checked is "View SmartLists with SQL Tables".

    ------------------------------
    Bill Jones
    Systems Engineer, Financial Systems
    Universal Service Administrative Company
    Washington DC
    ------------------------------

    Academy - Online Interactive Learning from Experts


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