Dynamics GP Support Desk #3: Conditional Fields in Report Writer Based on Unit of Measure

Hello everyone!

I am in the middle of helping a client figure out the best way of dealing with a request from one of their customers. My client records transactions in US (Short) Tons...but their customer wants to see the invoices in Metric Tons.

A little refresher on the types of Tons:

Short Ton (US Ton):

• 1 Short Ton = 2,000 pounds (907.1847 kilograms)
• Commonly used in the United States.

Long Ton (Imperial Ton):

• 1 Long Ton = 2,240 pounds (1,016.0469 kilograms)
• Used primarily in the United Kingdom and some Commonwealth countries.

Metric Ton (Tonne):

• 1 Metric Ton = 1,000 kilograms (2,204.6226 pounds)
• Used worldwide and in most countries that follow the metric system.

Their system has an integration from the scales at their multiple locations. The scales and invoices are in US Tons. They had thought it would need to be done manually with new items setup for the customer...but thankfully they gave me a call. I had 2 ideas...but the 2nd was the best:

Idea #1: Setup an "Equivalent" in the Unit of Measure schedule.

This would allow them to use the same items for all customers.

Why this wasn't the solution: Users would be required to manually update. Also, there could be rounding errors. In the sample below, I included the Quantity and Prices in both US Tons and Metric Tons. Because my test company has the currency decimals set to 2, there was also a rounding issue.

Solution (Part 1): Create Conditional Calculated Fields in Report Writer

I haven't formatted the report. Also, I will be adding these fields to a special Microsoft Word template that will be assigned to only that Customer (or a special class of customers if they have more than one with this request). But I wanted to share how the calculation works. Here is a new sample invoice:

Here is how the fields calculated. It is not formatted (so it looks terrible...) but I can cover that in another post. I did not change the Extended Price. That should prevent any rounding issues. For the sample below, even though GP rounded during the calculation, the extended would have matched. I just think leaving that alone will ensure that the extended price charged to the customer will match the amount posted to AR.

I am still working on the formatting and will be working on the MS Word Template. I just wanted to share what I did so far. This is why calling (me or your partner) to discuss your issues is always a good idea. This prevented the need to create special items or the need for users to manually update integrated invoices. Also, if you are looking to move to Dynamics BC, it is important that you know which of your reports and forms have calculated fields so that this can be reviewed and planned for when you make the switch.

Let me know if you'd like more tips on Report Writer or Microsoft Word Forms. I might move this one over to my website and I might even record a Video! Would that help you?