web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SDT: SQL Script with U...
Microsoft Dynamics GP (Archived)

SDT: SQL Script with User Input

(0) ShareShare
ReportReport
Posted on by Corrie R Olson 40

Hello!  I saw the Script Debugging Tool session at Convergence and was excited to see more possibilities for the tool than just tracing issues.  We have a process for a client that looks like it could be resolved by using some features shown in the session but I'm unsure if it is possible.

We have a custom SQL table that stores sales order info used for a custom billing process.  Once the client has finished all the reporting from this custom table then I want them to be able to enter in a date to a window and it run a SQL script that says to update the custom table and set the flag = 1 for any transactions <= date entered in the window.

Two questions:

1) Can a window be set for the customer to enter the date into?

2) Would I use the execute SQL script option?  If so, how do you refer to the input window to the captured date?

Thank you for your help! 

*This post is locked for comments

I have the same question (0)
  • David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 14,095 Most Valuable Professional on at

    Hi Corrie

    You would need to create a Runtime Execute script which uses Dexterity commands to ask for the date using the getstring() dialog.

    Then you can read in the SQL script from SQL Execute using the helper functions, add the extra where clause with the date and execute the SQL script.

    Hope this helps

    David

    PS: I will try and post an example of this technique on the blog soon.

  • Brian_DiFi Profile Picture
    Brian_DiFi 2,410 on at

    That would be a very helpful blog post!

  • Brian_DiFi Profile Picture
    Brian_DiFi 2,410 on at

    David, is there a way to create a trigger that you can use on the Home Screen or in a link?

  • David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 14,095 Most Valuable Professional on at

    Hi Brian

    I don't believe that would be possible using the Support Debugging Tool.

    The home screen is HTML code, not Dexterity. The Area pages need commands registered for that to work.

    You could use Menus for Visual Studio Tools to create your own commands:

    http://aka.ms/MVST

  • Brian_DiFi Profile Picture
    Brian_DiFi 2,410 on at

    Ok, thank you very much David.  I've written a snippet that does this in the Runtime Execute window, I'll look into that.  

  • Suggested answer
    Brian_DiFi Profile Picture
    Brian_DiFi 2,410 on at

    We wrote a batch file using SQLCMD that prompts for the date, then updates the table based on the users input and returns the number of rows that were changed.

    I then put a link on the Home page so that the user can run it from GP.  

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans