Thursday, June 13, 2013
Printing a different company name and address on your checks based on the location assigned to the checkbook – report writer fun
Posted by Armanino Dynamics Team
The default check stub form in GP assumes that you have purchased pre-printed check stock with your company’s information such as name and address pre-printed on the checks. But, let’s say the check stock your bank sent you doesn’t have this information? And let’s, for a moment, also say that you have multiple checking accounts and you want to print different information on each check based on which account you are printing checks from. For example, one account might be for your LLC, and another might be for a trust account, and you want that information to print on the check, i.e. “Fabrikam LLC Operating Account” and another say “Fabrikam LLC Trust Account”
This seems like a simple idea, but it can get a little complicated. GP assumes that you purchased pre-printed check stock with that information printed on the check, and so you have to actually customize the check form to print the company name and address information assigned to a specific checking account.
I’m going to show you how, so roll up your sleeves and take a deep breath as I walk you through the process. There are a lot of moving pieces here, so pay close attention, if you miss a step then it may not work as intended.
First, we’ll go through the setup part on the GP side. You’ll go into your checkbook in GP and assign an address to it, by going to Cards > Financial > Checkbook. Open up the checkbook you want to assign an address to. Notice the Company Address ID is missing.
Click on the hourglass next to company address ID, and select an address ID that you want to assign to this checkbook. If you do not have one setup yet, you will click on the blue underlined company address ID label, which will take you to the address window.
Fill in the new address information, note that this information will be how it prints on the check so make sure you name the company name and address exactly how you want it to appear on your new checks and click Save, and close the window. Then, select that new account in the Company Address ID field on the checkbook maintenance screen to assign it to your checkbook, and click Save on the Checkbook Maintenance screen to save your changes.
Phew! Now we’re done what next? It’s time to go to Report Writer and customize that report!
Click on the Microsoft Dynamics GP Logo and click on “Tools” then “Customize” then “Report Writer” (you can also press Alt-F9 to open Report Writer from GP).
Once Report Writer Launches you will see a product window pop up, it should default to Microsoft Dynamics GP, so click OK.
Once report writer opens, it’s like a blank canvas, you have 4 buttons at the top. To start customizing a report, click on the Reports Button:
Once you click on the reports button, you will see all your reports that you use in GP. If you’ve never customized any reports, the modified reports side should be blank. Scroll down through original reports, and locate the “Check With Stub on Top and Bottom – Graphical” and click on the “Insert” button to copy it over to Modified Reports.
Now, you’ve copied the form over you can edit it by clicking on the form and clicking open. When you open it, you still don’t see the report, you see this report definition box pop up.
Before we go look at the layout, we need to add the company address fields to the check report. To do this, we need to link the table since the company address master table is not normally part of the check report, so click on “Tables” to open up the table window.
This window shows you the tables that are included by default on the report. We need to add a few more tables so we can get that address information we added to the checkbook earlier. So, click on New, and you will add the CM Checkbook Master table by clicking highlighting the CM Checkbook Master and clicking OK.
Now you’ll see we have the checkbook on our report which has the “Address ID” field in it, but we will also need that Company Location Master to get the name and address to print on the check. Click on CM Checkbook Master and click “New” which will open up the related tables again:
Click on Company Location Master, and click OK. If you did everything correctly, your Report Table Relationships Window now looks like this:
You can go ahead and close this window by clicking close and we’ll move on to the next step of the process, the exciting part! Adding these new fields to your report! To do this you will click on the “Layout” button on Report Definition to pull up the layout of your check form.
Oh no! It’s a big scary window with all this stuff, what do I do? Don’t panic! You’ve made it this far, we’re getting closer. If you look at the report layout screen you will see this is the default layout for printing a check, with the various fields that go on the check stub as well as the check. Off to the right of that, we have our “Toolbox” which is where we can add fields to the report, or add a text box to the report by clicking on the “A” font button. Behind the toolbox there is a properties window. When you click on a field on your report, the properties window pops up which allows you to edit your fonts various attributes of the field.
Before we go ahead and add that company name and address to the report, there is one more housekeeping item. Because you’re going to add a customized address to the report, rather than the standard one, you will need to setup a City-State-Zip “Calculated field” so that the City, State and Zip can print perfectly on the form, otherwise you’ll have a bunch of blank spaces in between the 3 fields because the system will interpret whatever field size, so if City has a max size of 40, and your city is only 20 characters, you would have 20 blank characters. To fix this we do as follows.
In the toolbox section, where it shows “PM Payment Work” you’re going to click on that, and change it to “Calculated Fields”:
When you click New, the following window pops up.
- Give it a name such as “Calc City-State-Zip”
- Change the Result type to “String”
- Expression type “Calculated”
- You need to trim the address, so first you will go to the functions tab, select system-defined, and locate the STRIP function, click add
- Now go back to the fields tab, select the Resource “Company Location Master”, and select the “City” field and click Add
- Then in the calculated box where it’s adding your fields, click the mouse pointer just off to the right of the ) so it looks like this:
- Then click on CAT to add the CAT symbol (#) which will tell it to concatenate the fields together into one
- Then click on the constants tab, change type to String, and in the constant box type , and a space and click Add
- Then click on the CAT to add another (#)
- Then add the strip function again under the functions tab, and click on fields and select the State field and click Add
- Click your mouse pointer just to the right of the ) like we did in step 6
- Click on the CAT to add another #
- Click on Constants tab and select type of string and type a space in the constant box and click add
- Click on the CAT to add another #
- Click on the Functions tab, and add the strip function again, then go to the fields tab and add the field Zip Code from Company Location Master. When you are done it should look like this:
In the Report Layout box, you’re going to look at section F2 – Check & Hdr – this is where you want your company name and address to print, probably in the top left corner of the check, much like a return address on an envelope. Locate your toolbox with all your fields, and you’re going to click on the little down arrow next to the list of tables, and select “Company Location master”.
Scroll down the list until you see “Location Name” , and drag it onto the report under check & hdr:
Go back to the toolbox, and select the field “Address 1” and drag it under location name…
Lastly, go back to the toolbox, and click on the drop down where company location is and change it to “calculated fields”, and select the Calc Company City-State-Zip field that we created earlier and drag it onto the report. When you are finished it should look like this:
Note: You may need to click on the Calc Company City-State-Zip field on the right side and drag it out to make it wider like this if it’s cutting off on your report.
Now that you are done editing your report, you’re going to return back to GP by going to “File – Microsoft Dynamics GP” … Important NOTE: Make sure when prompted to save the report layout that you click Save or you will lose all this work! It may ask you twice, if so, click save both times…
Back in GP, you will now need to give your users access to the report. You will need your system password for that. Click on Microsoft Dynamics GP > Tools > Setup > System > User Security and enter your system password.
In the security setup window, select user sa, and select your company from the company drop down, and then at the bottom, click on the “Alternate/Modified Forms and Reports ID” blue underlined text to bring up the report screen. You will need to enter your system password again.
In this window, you will select Reports Under type and it will bring up a list of reports. Expand the Purchasing by clicking the Plus next to Purchasing. Then go to the “Check With Stub on Top and Bottom – Graphical” and click on the plus next to it. From there, you will see there is a modified report, so click the radio button next to the modified one so it’s selected, and click save at the top.
Once you click save, the window clears, go ahead and close the Alternate/Modified forms and reports window, and also close the security setup window.
You are officially done! Now you can go to print your checks. NOTE: Make sure when you print your checks that the stub you just customized is the one that is selected in the Check Format section, it should say Stub Top/Bottom – Graphical like this:
And here is my pretty check with the company location name and address printed on it! 🙂
I hope you have enjoyed this lesson in Report Writer and customizing the checks!
We enable an organization by supporting each stage of your company’s growth by implementing scalable Microsoft Dynamics solutions. As Microsoft Certified solutions experts and Inner Circle Partners, we work to eliminate redundant systems and leverage existing software investments, while keeping your goals for the future in mind.