[Paul Poulos]

I am trying to build a report in Excel that is refresh-able that will show if a Vendor Is Active or Inactive along with other information. I can’t seem to find the field name used in the PM table. I assume it should be located in the PM200 table But I can’t find it. Is there a cross reference showing the tables and field names? Is there a way to see within GP the associated Table and Field name from the maintenance screens?

——————————
Paul Poulos
Hicks Nurseries Inc.
Westbury NY
——————————

[Matthew Arp]

http://dyndeveloper.com/DynModule.aspx

One of the best references out there. You’d probably want the VENDSTTS & HOLD columns in PM00200.

——————————
Matthew Arp
Business Systems Developer
Hunton Group
Houston TX
——————————
——————————————-

[Blair Christensen]

The only shortcoming of the dyndeveloper site is that it requires a membership fee.  Victoria Yudin’s site is free and contains excellent information as well – even if it isn’t quite as detailed as dyndeveloper’s in all respects.

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-

[Wendy Kennedy]

I just took the GPUG class on SQL and they had a lot of great tips on finding tables.  Victoria Yudin was a great resource mentioned as well as Leslie Vail.  I found Victorias more useful as it also included tables to explain the detail of codes if they were represented as a number in the database ( like 1=invoice, 2=payment, etc.)

He also showed me how to use Table finder in GP if you own smartlist builder…was a great class.  Feel free to email me if I can give you more details.

——————————
Wendy Kennedy
Financial Affairs system & Budget Mngr
Wendy.l.kennedy@wilmu.edu
Wilmington University
New Castle DE
——————————
——————————————-

[Beat Bucher]

Hi Paul,
Rather than trying to grab the various tables from the DB, make your life easier and tap into the out-of-the-box SQL views that Microsoft provides already with GP :
– Vendors
– VendorAddress
– VendorItems

Those views are serving already as a base for the built-in Excel reports and the SmartLists.
Victoria Yudin’s blog is also a treasure trove of SQL views for all kinds of needs.

——————————
Beat Bucher
Business Analyst, Dynamics GP MVP
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
+1-514-489-4267
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
——————————
——————————————-

[Jonathan Cox]

I’m going to 2nd what Beat Bucher advised. This would be the quickest way to get the information you are needing. 

——————————
Jonathan Cox
Frontier Tubular Solutions, LLC
Oklahoma City OK
——————————
——————————————-

[Diana Doane]

I’m not sure when this particular feature was added – – Table Finder.  It can be found in the Ribbon Bar under Tools for any/most GP window.  There has been the Resource Descriptions tool as well which is under the same Tools button.  These may not get you all the way there but are helpful in tracking down connections and names.  Best thing, it’s right there in GP.

——————————
Diana Doane
Ascend Learning, LLC
Leawood KS
——————————
——————————————-

[Frank Heslin]

GP Power Tools GPPT (Available from Mekorma) can also give you this info.  PLUS a whole lot more!!!

Mark Polino has an Excel workbook that has all tables and fields for GP.  His latest version is only GP2013, but I’m on GP 2015R2 & I still use it all the time.  I haven’t run into a situation where what I was looking for in 2015 wasn’t included in his 2013 file.  Here is a link to Mark’s site: Just go to Free Stuff > Downloads

DynamicAccounting.net

DynamicAccounting.net		remove preview
DynamicAccounting.net Find out more about Microsoft Dynamics GP than you ever knew existed with one of the longest running GP blogs. The DynamicAccounting.net blog started in 2005 and has thousands of entries to help you get things done with Dynamics GP. Each week has links to helpful posts from around the world. View this on DynamicAccounting.net >

 

——————————
Frank Heslin
Financial Systems Administrator
ExamWorks, Inc.
Atlanta GA

Member, GPUG Advisory Committee
Chairman, Georgia Regional GPUG Chapter
Summit 2016 Track Leader
GPUG All Star 2016

GP2015 R2
MR CU14

Follow me on Twitter: @Automate_It

GPUGGA – http://www.gpug.com/Atlanta

*** Help Us, Help You…provide enough info so that we don’t have to ask for more:) ***
*** If you solve your issue please share the solution to assist others! ***
——————————
——————————————-

[David Musgrave]

Hey Frank

You know that the excel spreadsheet that Mark published was created from Winthrop’s GP Power Tools (or its predecessor, The Support Debugging Tool).

I published an article that will provide that information, but also the Table Explorer in GP Power Tools can export its data as tab delimited which can be loaded into Excel.

GP Power Tools Portal: http://WinthropDC.com/GPPT

David

——————————
David Musgrave MVP, GPUG All-Star

Managing Director
Winthrop Development Consultants

Perth, Western Australia

http://www.winthropdc.com
——————————
——————————————-

[Frank Heslin]

Hi David,

Yes, Mark explained that to me (or wrote an explanation on his blog…memory, you can’t trust it!) way back when I found the one he did for GP 2010.  I wasn’t sure the OP, Paul, had GPPT or not.  But since Mark already did the work…

I couldn’t live without GPPT, or previously SDT, and thank you for creating it.  It is so helpful and has so many features!!  The only thing it’s missing is predicting user behavior;-)

G’day, Mate!!

Looking forward to seeing you at Summit!

——————————
Frank Heslin
Financial Systems Administrator
ExamWorks, Inc.
Atlanta GA

Member, GPUG Advisory Committee
Chairman, Georgia Regional GPUG Chapter
Summit 2016 Track Leader
GPUG All Star 2016

GP2015 R2
MR CU14

Follow me on Twitter: @Automate_It

GPUGGA – http://www.gpug.com/Atlanta

*** Help Us, Help You…provide enough info so that we don’t have to ask for more:) ***
*** If you solve your issue please share the solution to assist others! ***
——————————
——————————————-

[David Musgrave]

Frank

I will work on my Dexterity AI (Artificial Intelligence) algorithms for the next build of GP Power Tools…. 🙂

David

——————————
David Musgrave MVP, GPUG All-Star

Managing Director
Winthrop Development Consultants

Perth, Western Australia

http://www.winthropdc.com
——————————
——————————————-

[Shawn Dorward]

Just a note about ‘Table Finder’ – If I am understanding correctly, you are referring to a component of Eone’s SmartList builder product (SmartList Builder since January 2013).  It’s an excellent tool, I use it and GPPT ALL the time!

——————————
Shawn Dorward
Twitter: @ShawnMDorward
Implementation Consultant
InterDyn Artis
——————————
——————————————-

[Victoria Yudin]

Paul,

I believe you’re looking for VENDSTTS (Vendor Status) in the PM00200 table. The values will be: 
1 – Active
2 – Inactive
3 – Temporary

I found this on my blog here: PM Tables

Victoria Yudin		remove preview
PM Tables Commonly Used Tables: PM00200 – Vendor Master PM00201 – Vendor Master Summary PM00202 – Vendor Period Summary PM00203 – Vendor Accounts PM00204 – 1099 Period Detail(only in GP 10.0 and higher) PM00300 – Vendor Address Master PM00400 – PM Keys Master PM10000 – Work/Unposted Transactions PM10100 – GL Distributions for View this on Victoria Yudin >

Hope that helps.

——————————
Victoria Yudin
Microsoft Dynamics GP MVP 2005-2017
Flexible Solutions, Inc.
http://www.GPReportsViewer.com
http://victoriayudin.com
——————————
——————————————-