Tuesday, August 5, 2014

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

Sunday, August 3, 2014

Use Tax due because Inventory was used by the company

SNAGHTML17b46016I had a question recently asking me if there was an easy way to record Use Tax that becomes due when inventory is used by the company.

There are several existing methods to record Use Tax if you know it is due at the time of purchase, but this question applied to an existing inventory item, so those techniques are not useable.

Using the procedure below will allow you to record your use tax, reduce your inventory as well as tie it back to the inventory items you put in use. The ‘in use’ item should no longer appear on your Stock Status report.

Here's what I came up with:

  1. Create a site for inventory put in use. I called mine USE TAX.
  2. Create a GL account to use as the ‘Use tax inventory clearing account’.
  3. Create a Tax Schedule and the appropriate Tax Details as a Purchases tax that will represent the Use Tax.
  4. Identify the inventory put in use and create an inventory transfer to the USE TAX warehouse. Also change the Quantity Type from On Hand to In Use. Set the default GL account for In Use to the clearing account created in step 2.
  5. In the Company Setup Options window, check the box next to ‘Calculate Taxes in General Ledger’.
  6. Create a GL entry and push the ‘Tax Entry’ button that should now exist in the lower left-hand corner of the Transaction Entry window.
  7. On the Tax Entry window, select the Credit Transaction radio button.
  8. Use the ‘Use tax inventory clearing account’ created in step 2 as the account on the Tax Entry window.
  9. Select the Tax Schedule created in Step 3 and then push the ‘Create’ button.
  10. The Transaction Entry window for GL will open. You will need to provide the debit account to make the entry balance. This is the account you want the expense (or whatever) to land in as a result of putting this inventory item in use. This is normally an expense account (this might also turn into a fixed asset – that would be a different set of procedures).
  11. Enter the vendor invoice for the balance in the Sales Tax Payable account (the amount was credited to the account in step 10). The PURCH amount should credit the account or accounts you set up with each of the tax details. The PURCH debit should clear that account out. The credit will, of course, be to accounts payable.
  12. Pay the Vendor.

Summary:

Transferring the inventory to a USE TAX site will be an easy way to track the specific items on which you have remitted use tax. It also will remove it from the Stock Status report, and the GL inventory account.

The tax entry to the GL will calculate the appropriate tax amount and credit the taxes payable account you set up on the Tax Details. The Debit account for this transaction should be the account you want the inventory cost plus the sales tax to land in. This is usually an expense account.

The Vendor invoice transaction will clear out (debit) the Taxes Payable account – which should be the PURCH account. The credit side of the entry goes to your regular A/P account.

Here’s what the T-Accounts look like using the Supplies Expense account as the ultimate landing place for the item put in use (plus the UseTax),

image

Enjoy!
Leslie Vail