Open Forum

Expand all | Collapse all

Editing a Database View for Smartlist

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

    Posted 5 days ago
    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
    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: Editing a Database View for Smartlist

    GPUG ALL STAR
    Posted 4 days ago
    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.​

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

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

    Conference-GPUG_200x200


  • 3.  RE: Editing a Database View for Smartlist

    GOLD CONTRIBUTOR
    Posted 4 days ago
    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
    ------------------------------

    Conference-GPUG_200x200


  • 4.  RE: Editing a Database View for Smartlist

    TOP CONTRIBUTOR
    Posted 4 days ago
    Edited by John Arnold 4 days ago
    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
    ------------------------------

    Conference-GPUG_200x200


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

    TOP CONTRIBUTOR
    Posted 4 days ago
    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
    ------------------------------

    Conference-GPUG_200x200


  • 6.  RE: Editing a Database View for Smartlist

    Posted 3 days ago
    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
    ------------------------------

    Conference-GPUG_200x200


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