Smartlist 2

[bradth]

Does anyone have any idea how to find out what tables a smartlist is using to pull the columns from? I'm trying to figure out what SQL tables the smartlist Item Quantities is using and create a SQL query from it.

Brad

 

[Dukester0122]

I don't have access to GP right now but it should be IV00102 and IV00101.

IV00102 - Item Quantity Master incluldes the info in Item Resource Planning window.

IV00101 - Item Master

 

[bradth]

That's what I had figured and used, but still was curious if there was a way to find out what tables the original GP smartlists had used. If anyone know how to do this, please let me know, it would be much appreciated. Thanks.

Brad

 

[pichapie]

I don't remember on top of my mind if Anyview can do it but let me check tomorrow.

 

[Dukester0122]

We also use Anyview, have you implemented Anyview Browser?

 

[bradth]

No I haven't, where do you get the Anyview Browser from? Is there a trial version or is it available to download, and if it is could you please send me the URL. Thanks.

Brad

 

[pichapie]

I just checked and it doesn't show the tables just the fields.

We have the Browser but stop using it because Business Portal gives us the same information.

 

[bradth]

One more favour to ask from the both of you, I need to know what tables are included in the smartlist "SALES LINE TRANSACTIONS" so I can recreate this smartlist with a query statement. Thanks.

Brad

 

[jonbarr]

Brad
I'm pretty sure you will get all the data you need from SOP30200 and SOP30300, but you may need to use IV30200 and IV30300 for Item details. Using the first two tables I link them by SOP Type and SOP Number, as we find the same number is sometimes used more than once, e.g. it could be possible to have an order and an invoice with the same number but from two entirely different orders. Remember to also make sure that Void Status is 0 for all your Orders and invoices to exclude data from voided documents.

Most of the data you have asked for can be found under Tools/Resource Descriptions/Tables. Hope this is helpful.

 

[jazgeek]

sales line items in smartlist actually use a union of both open and closed sales order lines

so

along with the inventory table IV10100 you would need a
union between

SOP10100 sales header open
SOP10200 sales detail open
and
SOP30200 sales header history
SOP30300 sales detail history



-----------
and they wonder why they call it Great Pains!

jaz

 

[bradth]

Perfect thanks a lot guys, that helps me out much. Now off to write a query...

Brad

 

[bradth]

Jazgeek and jonbarr, question for the both of you:

I'm trying to write the query for this, but I am unable to union these tables together. For whatever reason I can't get all of these tables to link together. How would I go about unioning all of these tables together, and if you had an example for me, it would be much apprecaited. Thanks again in advance.

Brad

 

[stef315]

Do something like:

select DOCNUMBR, DOCDATE from SOP10100
UNION ALL
select DOCNUMBR, DOCDATE from SOP30200

those are just excample fields. but you can only select those fields they have in common i believe. Union the header table with the header table. then a seperate detail with detail. you then join the header to the detail the same as you would before.

I tend to do this stuff in views. So, I (although they can tell you a better way) do the above as one "Header" view then another view of detail, then link them.

 

[jazgeek]

I too also use views a lot

I also rename the view's fields to more "english" field names to make it easier to work with in crystal.

select SOPNUMBR as "SALESORDERNUMBER", DOCDATE as "DOCUMENTDATE" from SOP10100
union
select SOPNUMBR as "SALESORDERNUMBER", DOCDATE as "DOCUMENTDATE" from SOP30200

using only the fields that I want to report on. the union for the entire table would be a nightmare to write.



-----------
and they wonder why they call it Great Pains!

jaz

 

[winthropdc]

My SnapShot tool has the ability to create GenView SQL scripts which can be used create views for all the GP tables which use the "nice" display names.

You could use this to create union views. Also, did you know that SmartList Builder can take any SQL view and show it as a Smartlist.

You can download SnapShot for free from the Development page of

http://www.winthropdc.com.

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer
MBS Support - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[bradth]

winthropdc, one question. After I install this program, is there a way to remove it from GP? I had just wanted to use it on one of our test servers, but it is showing up on the palettes of the live server as well. Is there a way to remove it from the palette or uninstall the program all together? Thanks.

Brad

 

[stef315]

winthropdc, why didnt you tell me about your tool when I was writing all my views this week!! I know you've told me before but you should have just telepathicaly known I was creating views without it and posted an open suggestion here. heehee...sometimes I wish I'd listen better.

 

[winthropdc]

Change the SS_Wizard and SS_Development Dex.ini settings to FALSE and login again.

This should remove the palette entries. Then you can remove the dictionary from the DYNAMICS.SET Launch file.

All the tables are stored as local ctree tables in the SnapShot folder.

This does not remove all menu entries on a v8.0 system, it will not show on the menus, but will still be in the syMenuMstr table. I will have to look at making the un-install cleaner. I already have un-install code in other products which can remove all the menu entries and even remove itself from the Dynamics.set.

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer
MBS Support - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[winthropdc]

Brad

New builds for v7.50 and v8.00 of SnapShot are now available.

If you display the About SnapShot window (Help >> About Great Plains >> Extras >> About SnapShot), you can use the Un-install button to remove the menu items and adjust the Dynamics.set.

The ctree files can be kept for later use or removed by deleting the SnapShot folder.

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer
MBS Support - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[bradth]

Great, thanks a lot winthropdc, I'm going ot give that a try and see if it works for me.

Brad