Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to create calculated fields so that you can filter Payables Management documents into aging buckets according to the document date in Microsoft Dynamics GP

How to create calculated fields so that you can filter Payables Management documents into aging buckets according to the document date in Microsoft Dynamics GP

INTRODUCTION

In Microsoft Dynamics GP, you can create calculated fields to filter Payables Management documents into aging buckets according to the document date. This article describes how to perform this procedure.

↑ Back to the top

More Information

You may want to filter Payables Management documents into aging buckets by using the date on which the document was created. To do this, you can create calculated fields to filter documents into aging buckets according to the document date. To perform this procedure, follow these steps:
  1. Use the appropriate step:
    • In Microsoft Dynamics GP 10.0, point to Tools on the Microsoft Dynamics GP menu, point to SmartList Builder, and then click SmartList Builder.
    • In Microsoft Dynamics GP 9.0, point to SmartList Builder on the Tools menu, and then click SmartList Builder.
  2. In the SmartList Builder window, type an ID in the SmartList ID field, type a name in the SmartList Name field, and then type an item name in the Item Name field.



    Note The SmartList name and the item name will be displayed in the SmartList window when you open the SmartList window.
  3. In Microsoft Dynamics GP 10.0, click Purchasing in the Series list.

    Note This step is not required in Microsoft Dynamics GP 9.0.
  4. Click the plus sign in the Tables area, and then click Microsoft Dynamics GP Table.
  5. In the Add Table window, specify the following settings:

    • Product: Microsoft Dynamics GP
    • Series: Purchasing
    • Table: PM Transaction Open File
  6. Click Save, and then click Calculations.
  7. In the Calculated Fields window, click the plus sign in the Calculated Fields area.

  8. In the Add Calculated Field window, type the name of the calculated field in the Fields Name field. For example, type Days Old.
  9. In the Field Type list, click Integer.
  10. In the Calculation box, type the following statement. Or, copy the following statement, and then paste the statement into the Calculation box.
    DATEDIFF ( day , {PM Transaction OPEN File:Document Date}  , GETDATE() )
  11. Click Save.
  12. In the Calculated Fields window, click the plus sign in the Calculated Fields area.

  13. In the Add Calculated Field window, type the name of the calculated field in the Fields Name field. For example, type Aging Bucket.
  14. In the Field Type list, click String.
  15. In the Calculation box, type the following statement. Or, copy the following statement, and then paste the statement in the Calculation box.
    CASE 
    WHEN DATEDIFF ( day , {PM Transaction OPEN File:Document Date}  , GETDATE()  ) < '31' THEN 'CURRENT'
    WHEN DATEDIFF ( day , {PM Transaction OPEN File:Document Date}  , GETDATE()  ) < '61' THEN '31 to 60'
    WHEN DATEDIFF ( day , {PM Transaction OPEN File:Document Date}  , GETDATE()  ) < '91' THEN '61 to 90'
    WHEN DATEDIFF ( day , {PM Transaction OPEN File:Document Date}  , GETDATE()  ) < '9999' THEN 'OVER 90 days'

    ELSE 'other'
    END
    Note Microsoft Dynamics GP uses this statement to filter the documents into the aging buckets according to the document date, as follows:
    • If the document was created in the last 30 days, the document is filtered into the Current aging bucket.

    • If the document was created in the period from 31 days ago to 60 days ago, the document is filtered into the "31 to 60" aging bucket.
    • If the document was created in the period from 61 days ago to 90 days ago, the document is filtered into the "61 to 90" aging bucket.
    • If the document was created more than 90 days ago, the document is filtered into the "Over 90 days" aging bucket.
    If you want to use buckets that have different aging criteria, modify the statement accordingly.
  16. Click Save.

  17. Click OK.

  18. Click Calculated Fields, and then click to select the Default check box for each calculated field that you created in steps 7 through 16.
  19. Click PM Transaction Open File, and then click to select the Default check box for each column that you want to be displayed in the default SmartList object.
  20. Click Save.
  21. To open the SmartList window, use the appropriate step:
    • In Microsoft Dynamics GP 10.0, click SmartList on the Microsoft Dynamics GP menu.
    • In Microsoft Dynamics GP 9.0, click SmartList on the View menu.
    When you are prompted to make changes, click Yes.

    Note If another user wants to view the SmartList object that you created in Microsoft Dynamics GP 9.0, you must grant security permissions to the user. To do this, follow these steps:
    1. On the Tools menu, point to Setup, point to System, and then click SmartList Security.
    2. Type a system password if you are prompted to type a system password.


    3. If you want to grant the security permissions to a user, click User Security. If you want to grant the security permissions to a user class, click User Class Security.
    4. Enter the values in the following fields:
      • Company
      • User ID
      • User Name
    5. Click to select the check box for the SmartList object that you created.
    6. Click OK.
  22. Use the appropriate step:

    • In Microsoft Dynamics GP 10.0, expand Purchasing, and then click the SmartList object that you created.
    • In Microsoft Dynamics GP 9.0, click the SmartList object that you created.

    You can now see the calculated fields that you created in the SmartList object.

↑ Back to the top

Keywords: kbinfo, kbexpertiseadvanced, kbmbspartner, kbexpertisebeginner, kbmbsmigrate, kbhowto, kbexpertiseinter, kb

↑ Back to the top

Article Info
Article ID : 943032
Revision : 1
Created on : 1/7/2017
Published on : 8/2/2011
Exists online : False
Views : 132