Tuesday, August 5, 2014

Modified RM Aged Trial Balance–Summary

The RM summary aged trial balance is too busy. Too many lines per customer. If you just want one line per customer, this is the report for you.

The modified AGED TRIAL BALANCE WITH OPTIONS – SUMMARY:

image

The original AGED TRIAL BALANCE WITH OPTIONS – SUMMARY:

image

I know it’s hard (perhaps impossible) to read the images, but you can clearly see how much cleaner the modified report looks.

You can download the modified version of this report here:
Modified RM Summary ATB-1 line per customer

Until next post

Leslie

Modified Payables Transaction Entry Distribution

I modified the Payables Transaction Entry Distribution window so the Account Number appears on the first line of the scrolling window. I also added the Currency ID field to the second line of the scrolling window because the second line looked pretty desolate and  needed something else. There were no other fields in the distribution table (PM10100) that looked interesting.

image

This modification was done on GP 2013 R2. You can download a package file for this modified form here: Modified Payables Transaction Entry Distribution

Enjoy!

Leslie

Modified GL Transaction Entry Window - Account Description on First Line

Forever I’ve wanted the account description on the first line of the GL Transaction Entry window. Well now I have it! It took me all day to create, but at least now it exists. I shouldn’t be the only one who benefits from my labor, so I’m going to make it available to you.

NEW GL Transaction Entry window:

image

Changes Made to GL Transaction Entry window:

Fields added to the upper right-hand quadrant:

      • Last User ID
      • Last Date Edited
      • Originating Document Information
      • Series
      • Originating Posting Date
      • User Who Posted

Fields added to the third line of the scrolling window: 

      • Originating Master ID
      • Originating Master Name
      • Transaction Type
      • Original Voucher/Payment Number
      • Original Document Number

The screenshot below shows the new window with the changes highlighted:

image

This modification was done on GP 2013 R2. If you want a copy of this window for yourself, you can download the package file here: Modified GL Transaction Entry window

Until next post!

Leslie Vail

Using a different name on the 1099 forms

I like clients who plan ahead! This company wants to use a different name on the 1099s other than the ‘Check Name’. You could use this technique on any modern version of Dynamics, but GP 2013 actually has a dedicated address ID for 1099s. That’s where the 1099 address comes from. Sadly, the Vendor Contact is not a field included in the 1099s to Print Temporary File table.

There was one more twist to this request, they didn’t want to use this alternate name for all 1099 vendors, just for some exceptions. No problem. Here’s what we did:


First, we need a way to determine whether the alternate name or the checkbook name should be printed. There are several ways to do this. The easiest way is to pick a field on the vendor master and use it as a switch that will tell us if the alternate name should be used. You could use any field, but we normally claim a field not being used for something else. Some likely candidates are:
    • Payment Priority
    • FOB
    • Country
    • Phone 3
    • UPS Zone
    • Tax Registration Number
    • Comment 1
    • Comment 2
    • User Defined 1
    • User Defined 2
    • Payment Priority
    • Revalue Vendor (this only works if you will NEVER use Multicurrency)
    • Country Code

You can use any field on the Vendor Maintenance or Vendor Address Maintenance windows, but the above fields are some of the fields I’ve hijacked over the years.

For this example, I’m going to use the Revalue Vendor checkbox field. Often, I use this field because it’s a checkbox and it’s easy to work with. I think it’s easier because it can only have two values, 0 or 1. You must be sure you’re never going to want to use multicurrency, but that’s easily managed by switching fields. As seen above, there are many you can choose from.

You need to use Modifier and change the prompt, but it’s an obvious choice if multicurrency is out. I changed the checkbox prompt to Use Default 1099 Check Name. We need to word it that way because we need to uncheck the box when the alternate name is desired.


Once you’ve decided on the field, it’s time to decide on the criteria. I’m going to keep this example easy, but it can be complicated, so long as it’s something the Report Writer can calculate. Revalue Vendor is a checkbox, so it’s value is 0 if false and 1 if true. This checkbox defaults to checked, so we need to use ‘unchecked’ to mark an exception vendor. I also make the following window fields Invisible (use the Visual tab on the Properties window):

  • [Text] Post Results To: (the field prompt)
  • (L) Payables/Discount Acct (a radio button)
  • (L) Purchasing Offset Act (the other radio button)
  • Post Results to (the radio group)

I do not remove those fields from the window. I may need to bring these fields back to life in the future, so I leave them on the window and hide them. My Vendor Maintenance Options window looks like this:

Preview of Modified Vendor Options


We now need to create a new relationship in Report Writer between the 1099s To Print Temporary File and the PM Vendor Master File.

image 


With that relationship set up, you need to add the PM Vendor Master File to the report’s tables, and then add the PM Address Master under the PM Vendor Master File. My Report Table Relationships window looks like this:

image


Next, we’re going to add a restriction to the report such that it only selects records from the PM Address MSTR if the Address Code is equal to ‘1099’, or whatever ID you choose. Just keep it consistent. My report restriction looks like this: 

image


Then, you need to decide which field on the Vendor Maintenance or Vendor Address Maintenance window you want to use as the alternate 1099 name. Typically, I use a field on the Address Maintenance window to be sure it isn’t changed unintentionally. Here, I am going to use Contact Name.

I used Modifier to change the field prompt to read * Contact Name. I also added a note to the bottom of the window that reads * On the 1099 Address ID, this is the Vendor Name on the 1099. You might not want to do this part, because the prompt will then be changed for all Address IDs, not just the 1099 address. My modified window looks like this:

Vendor Address Maintenance


Next, we need the conditional calculated field. I’m going to call it Alternate 1099 Name. We are testing for the value of the Revalue Vendor checkbox. If it is 1, we print the check name, if it is 0, we print the alternate name (which is Vendor Contact). The formula will look like this:

image


Finally, modify the report layout and replace the Vendor Check Name field with your Alternate 1099 Name calculated field.

If the IRS makes any changes to the report, your modified report will no longer print appropriately. In that case, you’ll need to start over with a new copy of the then current 1099 Laser Miscellaneous report.

My modified report is shown below – there’s a red box around the new calculated field. There is a link to this report at the bottom of this post.

1099 Report LayourI

I welcome any other workarounds you all have come up with. Also, please let me know if you have any trouble following this article and I’ll modify it for the better.


This is a link to the package file containing the modified 1099 Laser Miscellaneous report: https://app.box.com/s/3yvjfgdwb652jdlno4w1

This is a link to the package file containing the modified Vendor Address Maintenance window:https://app.box.com/s/dcdtkg13q9z2qr84ykvc

Until next post!

Leslie Vail

Monday, August 4, 2014

Applying Security to Record Notes

Quite often, users want to apply Field Level Security on the Notes windows. Since the whole company shares the same four Notes windows, you really can’t apply security to the windows themselves. You can’t specify a particular Notes window on a particular field on a particular window.

You can still use Field Level Security by putting your security on the Notes buttons. The buttons are usually part of an array, and their index tells you exactly which button it is. You can tell if your field is an array by performing the following:

  1. Open your target window in Modifer
  2. Select the window object you’re interested  in
  3. Open the Properties window in Modifier (CTRL+M)
  4. Select the Object tab in the Properties window
  5. Double click on the field that says Field
  6. The Field Definition window will open
  7. Look at the Array Size field on that window
  8. If the number is greater than zero, you have an array
  9. The number tells you how many elements the array can hold

A.  Here’s the SOP Entry window in Modifier:

SOP Entry window in modifier

B. Here’s the Properties window with the fields referred to above highlighted:

Properties window

C. Below is the Field Definition window. We can see this field has an Array Size of 11. That means we can have up to 11 Notes buttons on the window using the ‘Note Array Absent - Window Area’ field.

Field Definition window

D. In order to apply security to the correct note button, you need to determine the index number of your target button. There are a variety of ways to do that. I’ve set security on every element in the array, and then tried them one at a time. I’ve also exported the form to a text file using Dexterity and just looked on the report for the location of the field. What you can’t do, is figure it out using Modifier. I do not know of a way to do that anyhow.

E. For the SOP Entry window, I’ve taken the time to identify the array indexes for the Note buttons on that window. Below you’ll find a screenshot of the SOP entry window with the indexes identified:

Note Array fields on SOP entry window

F. Remember, when you are setting security on the Notes buttons, you need to include both the Note Present and Note Absent fields. For the ‘Type ID’ note button, here are what the field would look like:

Note Array Absent – Window Area[1]
Note Array Present – Window Area[1]

G. For the ‘Batch ID’ note button, it would look like this:

Note Array Absent – Window Area[4]
Note Array Present – Window Area[4]

The only thing that changes is the index number.

H. The array index numbers will typically match for the Absent and Present fields, but that may not be true 100% of the time.

I. The table below lists the indexes and matching fields for the SOP Entry window.

Index

Field

[1]

Type ID

[2]

Document Number

[3]

Default Site ID

[4]

Batch ID

[5]

Customer ID

[6]

Currency ID

[7]

Item Number

[8]

Comment ID

Indexes 9 -11 were not used on the SOP Entry window.

Until next time,

Leslie Vail