Using Calculated Fields to Create Sequential Fields

Recently, a client of ours came to us with a customization request. They wanted all of their checks address fields together as a one single block, regardless of weather the fields contained data.  Many businesses don’t have a full address set, and this can leave a gap between the end of a business’ address and the City/State/Zip Code. Solving this is possible in Dynamics GP2013, and it takes the form of Calculated Fields.

Calculated Fields are great for allowing very specific things to happen within Report Writer.  In this example, we will be creating four calculated fields; Address Line 1, Address Line 2, Address Line 3, and City/State/Zip Code.  These fields will all interact with each other, and Dynamics GP will ignore lines without data rather than leaving blank space.

1.  In Report Writer, open the Report you would like to edit (in this case, Check with Stub on Top and Bottom – Text).  In the Tools window, find Calculated Fields then click New.

Report Writer

2.  Set the Result Type to String, then click the Functions tab.

Calculated Field Definition

3.  Select the radio button next to User-Defined, then select System and rw_SelectAddrLine.  Click Add, then click on the Constants tab.

GP 2013

 

4.  Select Integer, then type the appropriate line number.  In this example we are creating four lines; Address Line 1, 2, 3, and City/State/Zip.  Address Line 1 will have an integer value of 1, Address Line 2 will have an integer value of 2, and so on.  Once this is selected, click Add.

Calculated Field

5.  Click on the Fields tab.  Select the PM Payment WORK resource, then select the fields that are desired in the calculated fields.  In this case we will be using Address 1, Address 2, Address 3, and City-State-Zip Code.  Select each of these, clicking Add between each one.

Constants Tab

6.  Click on the Constants tab and select String.  Leave the field blank, and click Add five times.  The reason we add these five blank constants is that calculated fields require a total of ten calculated expressions.  We have the initial rw_SelectAddrLine and four address lines, so adding five blank strings brings our total to ten.  This should add five sets of quotation marks in the Expressions Calculated field.  Click OK.

Report Layout

7. Repeat this process for Address Line 2, Address Line 3, and City/State/Zip Code.  The only differences will be the name of the field and the integer value chosen in step 4.

8. Once all four fields are complete, put them on top of each other within a given report.  These calculated fields will always show together on a report, regardless of whether every field is populated within GP!

This article was written by Daniel Gonet, Dynamics GP Application Consultant for Tridea Partners. Tridea is a leading Microsoft Dynamics GP provider in Southern California. www.trideapartners.com