Archive

Archive for the ‘Receivable Management’ Category

Building Form GoTo’s for Receivables & Customers

March 2, 2011 1 comment

Here are Steps for building the most common GoTo’s for the Receivables module:

Basic Steps (Unless listed differently below):

  1. Press the Go To … button on the middle of the top bar in SmartList Builder (Or Open Drilldown Builder.)
  2. Enter your Description
  3. Product is Microsoft Dynamics GP
  4. Series is Sales
  5. Select Form listed below
  6. Press + on the middle right of the blue bar to add a task
  7. Add task(s) as below, press Save on Add Task window
  8. Press Save on Add Go To – Open Form window

Specific Windows (Forms):

  • Customer Maintenance – “Set a field value and run the field script” on field Customer Number
  • Address Maintenance

    .
  • Receivables Transactions Inquiry Zoom — this is accessible via an Advance Go Tos rather than a Form Go To – use the Receivable Transaction Inquiry (Also will take you to a Cash Receipt Inquiry Zoom for a payment.):   **See note below

 

  • Customer Payment Summary Inquiry  – “Set a field value and run the field script” on field Customer Number
  • Customer Yearly Summary Inquiry  – “Set a field value and run the field script” on field Customer Number  Will open by default to year of your GP date
     
  • Customer Period Summary Inquiry  – “Set a field value and run the field script” on field Customer Number  Will open by default to current period (based upon GP date)
     
  • Customer Inquiry – this uses a calculated field called Integer3 (Type integer, in the calculation area enter the number 3) to select the “Bill to” address (Third value in the dropdown)

  • Customer Transaction Inquiry – this Go To loads the customer, but also sorts by date, unchecks to display History (at least for Active customers) and Shows Details (second line for each transaction) See example screen below:


    .
  • Collections Management Main — Note that the Product and Series is different than the basic steps:

    .
  • Transaction entry (For transactions in Work status)  **See note below
  • Cash Receipts Entry (For transactions in Work status)  **See note below

    .
  • Apply Sales Documents (For Open Transactions, Payment, Return or Credit Memo types only)

    .
  • Edit Transaction (Open only)  (The dropdown for Type doesn’t have #2 Scheduled Payments.  A Calculated field is needed.)
  • Posted Transaction (Open only)  (The dropdown for Type doesn’t have #2 Scheduled Payments.  A Calculated field is needed. See above.)

    .
  • Receivable Batches this uses a calculated field called Integer2 (Type integer, in the calculation area enter the number 2) to select the “Cash receipts entry” (Second value in the dropdown) – Use 1 for Receivable Transaction batches

** Unlike the SmartLists that come from GP – we are not able to build a Go To that will open the Entry window for transactions in Work status and the Inquiry window for transactions in Open or History status.

Hopefully this helps!   Let me know if I have missed a Receivable window.  Next post, Payables!

Happy Building,
Devon

How to get data in separate columns rather than rows in SmartList Builders (Aging, Sales by Months, etc)

November 1, 2010 2 comments

This has been on my list of things to blog about – but when I saw this post in the Forums, I knew its time had come:

I have a client who is attempting to create a Sales report which shows sales by Customer.  They want the report to include a separate column for each month – based on Document Date.  I have attempted to create a calculation for each month (If/Then) but either don’t have the syntax correct or it is truly not going to work.

CASE
WHEN {{Sales Transaction History:Document Date} >1/1/2017…
 
Any ideas?????

This is something that seems to have become a very popular request in the last few years and I have always coded it in SQL. Since it requires a ‘group by’ in SQL code, I can’t imagine being able to do this in SmartList Builder. I would recommend coding this in a SQL view and basing the SmartList on that view.

I would differ :-) … This is easily accomplished in SmartList Builder:

First – Add a calculated field for each column of data 

  • In this scenario, one for each month – use the Month function to test:
    for January:
    CASE WHEN MONTH({Sales Transaction History:Document Date})=1 then {Sales Transaction History:Originating Document Amount} ELSE 0 END
    and so on…
  • For a list with aging in columns (inventory, AR or AP) 
    for example – the calculation for the “31-60 days” bucket for AR by Document Date:
    CASE WHEN
    DATEADD ( d , -30, GETDATE() ) > {RM Open File:Document Date}  AND
    DATEADD ( d , -60, GETDATE() ) <= {RM Open File:Document Date}
    THEN
    (CASE WHEN {RM Open File:RM Document Type-All} > 6
    THEN -{RM Open File:Current Trx Amount}
    ELSE
    {RM Open File:Current Trx Amount}
    END)
    ELSE 0 END
  • For other types of data – like quantities on hand – with Sites in columns, here is the calculation for the North site column:
    CASE WHEN {Item Quantity Master:Location Code}=’NORTH’ then {Item Quantity Master:QTY On Hand}
    ELSE 0 END

Next, unselect as Display any fields you don’t want to group by (or sum).  

In the scenario above we would want Year to be a group by field (or we will get all sales in January in history!).  So add a calculated field for year (Users can do a search filter on this field when running the list):

YEAR({Sales Transaction History:Document Date})

Now use the Options icon on the top to make this into a Summary SmartList – the way to “group by” in Builders.   Select all of your column calculated fields as Summary type “Sum” , your other fields as “Group by” and, in the words of my daughter, “There you go!”  

Learn more about summary SmartLists in my post: Summary SmartLists in Builder (Sales by Item for a date range, etc)

Happy Building!
Devon

PS – My apologies and thanks to the ladies on the original forum post!

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!
    Devon

    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!
    Devon

    Follow

    Get every new post delivered to your Inbox.

    Join 25 other followers