Microsoft Dynamics GP (Archived)

Is there a way to find which SQL field/table used by a custom user smartlist?

(0) Share

Report

Posted on by VinhTon

100

Trying to do sql queries based on user created smartlists, thanks ahead!

*This post is locked for comments

All responses (16)

Answers (0)

Community Member on at

Like (0)

Report

RE: Is there a way to find which SQL field/table used by a custom user smartlist?

Does anyone know where the "Display SQL" values for a particular smartlist category are stored in SQL? One of my custom smartlists got messed up and I'm hoping to be able to recreate it from looking at a backup.
VinhTon 100 on at

Like (0)

Report

RE: Is there a way to find which SQL field/table used by a custom user smartlist?

I really like the Apex tools, so useful! Thanks
VinhTon 100 on at

Like (0)

Report

RE: Is there a way to find which SQL field/table used by a custom user smartlist?

Thanks!
Jothi Krishnan N 1,869 on at

Like (1)

Report

RE: Is there a way to find which SQL field/table used by a custom user smartlist?

There are 2 versions of SL Builder. What I explained is on SL Builder from eOne Solutions, but if you dont have license for it or not installed, then you will see the one from MSFT called Smartlist designer. And it may not have everything I explained, but still you can see the query at least.

Modify button will enable only when you select the user created SL.

SL Builder from eOne:

SL Designer from MSFT
Suggested answer

L Vail 65,271 on at

Like (1)

Report

RE: Is there a way to find which SQL field/table used by a custom user smartlist?

VinhTon

If you want to search the database for a value, I highly recommend downloading the free product call ApexSQL Search. It's an addon to Studio manager and a wonderfully robust tool. You can target your search, or search every database in your installation. It's very easy to use and a very valuable tool - yet it's FREE! Apex has other free SQL tools, take a look.

[View:https://www.apexsql.com/sql_tools_search.aspx:750:50]

Here's the Apex menu. You can do way more than just search.

Here's the main screen of the Object Search

Kind regards,

Leslie

Leslie
VinhTon 100 on at

Like (0)

Report

RE: Is there a way to find which SQL field/table used by a custom user smartlist?

Do you mind screenshotting where the Create, modify, create view buttons are?

When I search for it to reference IT request, I only found this:

www.boyerassoc.com/using-smartlist-designer-modify-existing-stock-smartlists-dynamics-gp-2013-sp2
Jothi Krishnan N 1,869 on at

Like (1)

Report

RE: Is there a way to find which SQL field/table used by a custom user smartlist?

Also you can use the "Create View" option in the same area to convert the SQL query behind it to a SQL View and use it. But you may have to drop and recreate it if user make changes to SL.
VinhTon 100 on at

Like (0)

Report

RE: Is there a way to find which SQL field/table used by a custom user smartlist?

This is exactly what I'm looking for.

I do have access to GP but not to create/modify smartlist. Let me request it and confirm this works. Thanks!
Suggested answer

Jothi Krishnan N 1,869 on at

Like (1)

Report

RE: Is there a way to find which SQL field/table used by a custom user smartlist?

Assuming you have access to GP. If it is user created Smartlist, go to the Smartlist report and click Modify button and then go to Options >> Display SQL and mark all the checkboxes in that window to see the complete SQL query used in the smart list.
VinhTon 100 on at

Like (0)

Report

RE: Is there a way to find which SQL field/table used by a custom user smartlist?

Definitely can use this, thanks!