Archive for the ‘Payables Management’ Category

Matched Tables – Viewing Open & History Documents on the same SmartList in Builder (just like the default Lists!)

August 9, 2010 1 comment

One of the features of the default Lists in SmartList, Excel Reports & Navigation Lists is that you can often see ALL GP transactions for a certain type on one list – Work (unposted), Open (unpaid or not moved to history), and History (moved to history).   Now – don’t get me wrong – sometimes this feature is great, but a lot of my Builder Lists are to isolate just one status of GP transactions.  (For example, most of my clients that use Sales Order Processing have separate Open & History Lists from builder…)

What if you DO want more than one status on a Builder List?    On first glance, it seems difficult or impossible – that data is in separate tables and you can’t just start with the open table and add the work table (there wouldn’t be matching records – so you won’t get anything!)  Don’t worry – there is another way.

For example, you want to build a SmartList for payables transactions for a certain period of time and all of the distributions of these documents.  You really don’t care if the documents are paid or not (How they move to History in PM), you still want the documents to show on your list.

Matched Tables

  • First Start like normal and add your Transaction table for one status (I like to use open, but you could use history – it doesn’t matter).
  • On the Add Table screen – look for the Button “Matched Tables” on the bottom and press this button.

On additional tables (second or more) you will have to save the table and then edit it (paper and pen icon) to see the Matched Tables button:

  • On the Matched tables screen, Hit the plus sign on the left section to add your equivalent table for the other desired GP status (You can add more than one, if desired.)

You will see that on the right, Builder has already matched equivalent fields (If a field isn’t matched, it won’t show in the final list).  You can review and change if desired.

  • Press OK and Save back on the Add Table screen

Now you are done!   It is that easy.  

What is does is the equivalent of a SQL UNION ALL query.  It selects all of the data from the first table and then, as part of the same set of data goes and select all of the data from the second or Matched table.

Happy Building,


Adding Internet Information (Email addresses) to Lists in SmartList Builder, etc

August 6, 2010 1 comment

There are certain tables that you really should not link directly to in the Builders.  Internet Addresses (MS Dynamics > Company > Internet Addresses   In SQL:  SY01200 )  is one such table. 

Now, until recently this wasn’t a big deal since not many users took advantage of this table. BUT now in GP2010 this table is storing the Email addresses for the new Email functionality for Purchase orders, Invoices, etc.  Since this data is NOT shown on any stock SmartList or Navigation List, there is going to be a HUGE demand to view this data in the Builders.  

Below I will give you some SQL views to use to add this data to your Lists, but first let me explain why you shouldn’t link directly to this table.  It is a combination of a few factors:

  • First this table stores ALL of the Internet information for Customers, Vendors, Items, etc. 
  • When you link in the Builders there is no way to just select part of the data, just Customers for example.   (You could in SQL)  (Note to the MS programmers – being able to add a constant to the link section would help with these issues! 🙂 )
  • Not all Master records (Customer for example) will have records in the Internet Addresses table – so you will normally want to do a Left Outer link to this table (so that you can get all Customers and Internet addresses, if there are any.)
  • Adding a restriction to Builder to filter on the Master Type for the Internet Addresses table will not work for two reasons:
    • Adding a restriction directly to table with a Left Outer link will remove all records that aren’t in this table – EG any records without Internet Addresses  (basically turning it into an equal link)
    • Also, the restriction will apply AFTER the join and may remove legitimate Master records. 

I think an example will highlight the problem:

  • You are building a customer list
  • You have a customer ABC001  and a vendor ABC001  – only the Vendor has internet addresses
  • when you link the table you will get the vendors internet data in the customer record
  • adding a filter to the type will then completely remove this record from the list
  • I agree that this may be rare – but I like to build lists that will ALWAYS work correctly.

    OK – so here are the SQL views – (NOTE – I am having a font problem on this blog and can’t get the ‘&’ to work correctly – after you cut & paste these you will need to retype the ‘and’ in the Where clause in SQL – sorry!):

    First you will need to replace the constant in the Where clause with the correct code as well as change the name of the view on the first line:

    • CUS – Customer
    • VEN – Vendor
    • ITM – Item    (only one key field – Address ID is not used, obviously)
    • EMP – Employee
    • SLP – Salesperson
    • CMP – Company

    Basic SQL view:

    CREATE VIEW CustomerInternet AS
    SELECT     SY01200.*
    FROM         SY01200
    WHERE     (Master_Type = ‘CUS’)

    View in GP10 (or earlier) with nice field names (remember that these are changeable in the company set-up so you will want to modify to match yours, if different):

    CREATE VIEW CustomerInternet AS
    SELECT     Master_ID AS [Master ID], ADRSCODE AS [Address ID], INET1 AS Email, INET2 AS [Home Page], INET3 AS [FTP Site], INET4 AS Image, INET5 AS Login, INET6 AS Password, INET7 AS [User Defined 1], INET8 AS [User Defined 2], INETINFO AS [Additional Information]
    FROM         SY01200
    WHERE     (Master_Type = ‘CUS’)

    View for GP2010 with the new fields and nice field names (remember that these are changeable in the company set-up so you will want to modify to match yours, if different):

    CREATE VIEW CustomerInternet AS
    SELECT     Master_ID AS [Master ID], ADRSCODE AS [Address ID], EmailToAddress AS [Email To Address], EmailCcAddress AS [Email CC Address], EmailBccAddress AS [Email BCC Address], INET1 AS Email, INET2 AS [Home Page], INET3 AS [FTP Site], INET4 AS Image, INET5 AS Login, INET6 AS Password, INET7 AS [User Defined 1], INET8 AS [User Defined 2], Messenger_Address AS [Messenger Address], INETINFO AS [Additional Information]
    FROM         SY01200
    WHERE     (Master_Type = ‘CUS’)

    Don’t forget to run the grant script when you are done:

    GRANT SELECT ON CustomerInternet TO DYNGRP

    You last step is to go to Security and give access to your new view (MS Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security)

    One last hint – this data is NOT stored per customer or vendor, but stored by ADDRESS ID on the customer or vendor.  So you will need to use both the Master ID field and the Address ID field as your link fields.

    Happy Building!

    Distribution Tables – adding the GL accounts to Builder Lists

    August 5, 2010 1 comment

    As Part two of my response to a question on my Suggestions Page, I thought I would discuss the Distributions tables from GP transactions.

    Couple points to remember when thinking about adding Distribution tables to your lists in Builder:

    • Transaction Distribution tables are different then the actual Journal Entries stored in the Financial Tables. 
      • Distribution tables store data per transaction and do not contain JE numbers. 
      • Depending on the posting setting in GP the JE in Financial tables could be for one or many transactions. (Or could not make a JE at all.)  If your posting setting are per transaction, your could try to link the Financial tables – but this is not a good link via SmartList Builder – most of the time you will need to do a SQL view. (Issue with key fields.. whole different post…)
    • There will be at least two entries for each Transaction and may have many more.   IE – Taxes, Misc, COGS, etc —  all of the entries on the Distribution Button on the transaction. (Important point – since this will cause transactions to be listed several times on your final list.)

    • There is a Distribution type field that can be used to limit what parts of the entry are shown on your list. (For example, you may not want to have the Accounts Payable accounts show on a Payables transaction report.)  This is a Dropdown type field – unfortunately Builder generally doesn’t know the values for these by default – but typically they are in order on the dropdown on the transaction entry screen.  See my post: Drop Down Lists in SmartList Builder (Document Type on Transactions, etc) for more info on this type of field.

    • Most Distribution tables store records for various GP status (WORK & OPEN)  or (WORK & HIST).   Avoid the tables with the word TEMP in them – they don’t have what you want!   Some common tables:
      • PM Distribution WORK OPEN File  / PM Distribution History File
      • RM Distribution Work File (Don’t be confused by the name – this has Open too!) / Receivables GL Distribution History File
      • Sales Distribution Work and History
    • Generally, you will link to the Transaction table with the key fields from the Transaction table – often the GP assigned unique transaction ID (Voucher number, SOP Number, or Document Number) and the Document Type field. To find these – see my post: Adding additional tables to your SmartList (Successfully)

    Good luck & Happy Building!

    Transaction Amounts with correct signs (What you want Credit memos to be negative?)

    Isn’t it irritating that for most Transactions (all in Payables Management & Receivables Management) are a “positive” number even Credit Memos and Payments?   Don’t you wish you could export GP SmartList to Excel, run the Subtotal feature and get a REAL total?  

    Here is how to “fix” this in the Builders (SmartList, Excel & Navigational Lists):

    • Determine what field you can use to decide if the value should be negative or positive – your “type field” (Document Type for Payables; RM Document Type-ALL for Receivables; SOP Type for Sales Order Processing) 
    • Verify the field data type   (Press the Show Details (down chevrons) on the far left of the field header line, Now you should be seeing the Data Type on the second row under each field name)
      • If the Data Type is not Dropdown list (or Yes/No)  you will need to do a calculated field – scroll down… for Option #2
      • If the Date Type is Dropdown list (or Yes/No), then this field will be available in the next step.  But only if you have set the dropdown field options. See my post Drop Down Lists in SmartList Builder for details
    • Select the amount field that you want to correct the sign by clicking on the Display Name of that field
    • Press the Blue Arrow between Display Name and Display on the Field header line to launch the Set Field Options window. (This window changes its appearance based upon the data type for the field.)
    • Press the Negative values tab
    • Select your “type field” from above
    • Check Mark the boxes for the type values that should be negative

    Note – these values are only used for displaying in your List.  If you want to do a Summary list – these sign corrections will not be used – you will need to do a Calculated Field.

    Option #2 – A Calculated Field to correct the sign of a field:

    Here we are going to use our handy-dandy Case Statement:  (For more info see Try CASE )

    CASE {type field}
    WHEN 4 THEN – {amount field}
    WHEN 5 THEN – {amount field}
    ELSE {amount field}


    WHEN {type field} >=7 THEN – {amount field}
    ELSE {amount field}

    Uncheck the Default & Display boxes for the original amount field and check the Default & Display boxes for your new Calculated field so that users see this new field instead.

    Works like a charm.

    Happy building,