Tables

  • Tables

    Posted by DSC Communities on April 26, 2017 at 1:56 pm
    • Paul Poulos

      Member

      April 26, 2017 at 1:56 PM

      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

      Member

      April 26, 2017 at 1:59 PM

      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

      Member

      April 27, 2017 at 9:52 AM

      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

      Member

      April 28, 2017 at 9:54 AM

      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

      Member

      April 27, 2017 at 10:43 AM

      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

      Member

      April 28, 2017 at 5:44 AM

      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

      Member

      April 27, 2017 at 10:46 AM

      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

      Member

      April 27, 2017 at 11:33 AM

      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

      Member

      April 28, 2017 at 2:37 AM

      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

      Member

      April 28, 2017 at 9:05 AM

      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

      Member

      May 1, 2017 at 2:47 AM

      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

      Member

      April 27, 2017 at 11:53 AM

      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

      Member

      April 28, 2017 at 5:55 AM

      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
      ——————————
      ——————————————-

    DSC Communities replied 7 years, 1 month ago 1 Member · 0 Replies
  • 0 Replies

Sorry, there were no replies found.

The discussion ‘Tables’ is closed to new replies.

Start of Discussion
0 of 0 replies June 2018
Now

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!