Dynamics GP

August 2007 - Posts

• Dynamics users query needed

  From a recent email:  Yesterday, I extracted two tables from a customer’s DYNAMICS database - SY01500 (User Master) and SY60100 (User-Company Access), so I could show him which users had which access to their various databases (they have several).  It took me quite some time to do it and I know there is a script I could enable in Query Analyzer/Management Studio to cut something like this down to nothing at all from a time perspective.  I DTS’d the data out into two Excel sheets, did a VLOOKUP on CMPANYID, which is the common field for both, and then sorted the data by USERID.  I listed a User ID for each database/company name they had access to as it is pointless to show the customer a CMPANYID and not a company name.  I know I could have written some sort of script in SQL to join the tables on this field so I could have skipped the whole Excel piece completely.   Here is the query: select company.cmpanyid,   company.cmpnynam,   company.interid,   users.userid  from sy01500 company   join sy60100 users on users.cmpanyid = company.cmpanyid  order by 1,4   Posted Aug 30 2007, 12:41 PM by Steve Gray with 2 comment(s)

• Dynamics GP Inventory (IV) Tables

  BM00101 Bill of Materials Header BM00111 Bill of Materials Component BM10200 Assembly Transaction BM10300 Assembly Component BM10400 Assembly Serial Lot BM30100 Assembly Batch History BM30200 Assembly Transaction History BM30300 Assembly Component History BM30400 Assembly Serial Lot History BM40100 Bill of Materials Setup IV00101 Item Master IV00102 Item Quantity Master IV00103 Item Vendor Master IV00104 Item Kit Master IV00105 Item Currency Master IV00106 Item Purchasing IV00107 Item Price List Options IV00108 Item Price List IV00109 Item Serial Number Mask IV00110 Planner Master IV00111 Site Defaults IV00112 Item Site Bin Master IV00113 Item Price List Details IV00114 Inactive Items IV00115 Multiple Manufacture Items Master IV00116 Item Project Phase Options IV00117 Item Site Bin Priorities IV00118 Item Cost Change History IV00200 Item Serial Number Master IV00300 Item Lot Number Master IV00301 Item Lot Attribute Master IV00500 IV_ItemList_View IV10000 Inventory Transaction Work IV10001 Inventory Transaction Amounts Work IV10002 Inventory Serial and Lot Number Work IV10003 Inventory Transaction Bin Quantities Work IV10004 Inventory Bin Quantity Transfer IV10200 Inventory Purchase Receipts Work IV10201 Inventory Purchase Receipts Detail IV10300 Stock Count IV10301 Stock Count Line IV10302 Stock Count Serial Lot IV10303 Stock Count U of M IV10400 Extended Pricing Price Group Work IV10401 Extended Pricing Price Sheet Work IV10402 Extended Pricing Price Sheet UofM Work IV10403 Extended Pricing Promotion Free Item IV30100 Inventory Transaction Batch History IV30101 Inventory Sales Summary History IV30102 Inventory Sales Summary Period History IV30200 Inventory Transaction History IV30300 Inventory Transaction Amounts History IV30301 Inventory Transaction Detail History IV30302 Inventory Transaction Bin Quantities History IV30400 Item Serial and Lot Number History IV30500 Inventory Distribution History IV30600 Item Lot Attribute History IV30700 Stock Count History IV30701 Stock Count Line History IV30702 Stock Count Serial Lot History IV40100 Inventory Control Setup IV40201 Inventory U of M Schedule Setup IV40202 Inventory U of M Schedule Detail Setup IV40400 Item Class Setup IV40401 Item Class Currency Setup IV40500 Item Lot Category Setup IV40600 Item Category Setup IV40700 Site Setup IV40701 Site Bin Master IV40702 Site Intrastat Setup IV40800 Price Level Setup IV40900 Price Group Master IV41000 Stock Calendar IV41001 Stock Calendar Exception Days IV41100 Inventory Landed Cost IV41101 Inventory Landed Cost Group IV41102 Inventory Landed Cost Group Details IV50200 Stock Count Assignment Temp IV50300 ABC Analysis Temp IV70500 Inventory Report Options Posted Aug 16 2007, 01:17 PM by Steve Gray with no comments