Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Mary Mallazzo]

Hello

I want to add the customer internet address to the Aged Trial Balance With Options report.

I have created a view over the customer master and internet address tables. 

How do I add a view to a Microsoft Dynamics Report?

Or is there a better way to do this?  It does not appear that I can link the Rm Customer Master to the Internet Address table…..

Thank you

 

 

——————————
Mary Mallazzo
Fromkin Brothers
Edison NJ
——————————

[Steve Erbach]

Mary,

Sorry to say, GP reports (edited with vanilla Report Writer) only join tables together, not Views. There is a way to use data from SQL Views that involves the use of VBA… but I only know about it from a bit of Googling. Some very good GP consultants (David Musgrave, Mariano Gomez, Leslie Vail) have confirmed that it’s possible; it just isn’t for the faint-of-heart! Also, the information I’ve seen shows examples for older versions of GP (you didn’t say which version you were using). That is, versions 8, 9, and 10.

Here are a couple links that may point you in the right direction:

Using ADO with VBA with Report Writer

Accessing any SQL data from a Report Example

Using the Support Debugging Tool (the precursor to GP Power Tools)

Again, these are last decade examples. You may want to get help from your Partner.

Sincerely,

——————————
“Sparkly” Steve Erbach – Business Analyst & MS Dynamics Platform Administrator
WOW Logistics – Appleton, WI
+-VP for Communication, GPUG WI (Milwaukee) Chapter
| Co-Chair, GPUG WI (Green Bay) Chapter
| GP 2013 R2 (12.00.1801)…go-live on GP 2016: 23-Jan-2017!
+-MR 2012 CU15 (found — and reported — my very own bug!)
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: @serbach
————————————————————————-

[James Hummer]

Hey Mary

Here is the link that I did in BI360 that seems to work.    Replace Customer and Internet Address with table names. 

[Customer].[CUSTNMBR2] = [InternetAddress].[Master_ID] AND [Customer].[ADRSCODE2] = [InternetAddress].[ADRSCODE] AND [InternetAddress].[Master_Type] = ‘CUS’

——————————
Jim Hummer
Consultant
Solver
Los Angeles CA
————————————————————————-

[Alison Guimond]

Hi Mary,

There are Report Writer functions that allow you to pull some of the details from the internet information window, without needing to join tables or use VBA.  David Musgrave has a great post here that discusses them:  https://community.dynamics.com/gp/b/developingforgp/archive/2011/05/10/accessing-internet-information-from-report-writer

Check it out, it might be just what you are looking for.

-Alison

——————————
Alison Guimond
Frank Rimerman Consulting
San Jose CA
————————————————————————-

[Bill Schulz]

Mary

What about using Smartlist to generate the Open AR and add to that the Internet Information? Using Smartlist Designer you can link the Microsoft Dynamics-Company table “Internet Addresses”, and join it to the Receivables Transaction table as shown below:

In your search criteria, select documents with Current TRX Amount >0. You can add as much or as little detail from the tables as you want on the resulting Smartlist.

Of course, this depends on your having a recent enough version of GP to have Smartlist Designer. I believe that is GP 2013 or later. 

——————————
Bill Schulz
Turnkey Technologies, Inc.
St. Louis MO
bschulz@turnkeytec.com
————————————————————————-

[Leslie Vail]

On which window is the field that you wish to add?

——————————
Leslie Vail
Accounting Systems Consulting, Inc.
Dallas TX
——————————
——————————————-

[David Musgrave]

Hi

As Allison mentioned you can use the RW function I added into Dynamics.dic for this purpose.

It is all explained in my blog article.

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2011/05/10/accessing-internet-information-from-report-writer/

David

——————————
David Musgrave MVP, GPUG All-Star

Managing Director
Winthrop Development Consultants

Perth, Western Australia

http://www.winthropdc.com
——————————
——————————————-

[Brenner Klenzman]

Hi-

WilloWare just released a new add-on for GP Report writer that provides the ability to add any field from any table to any GP Report Writer report.

https://willoware.com/products/consulting-toolkit/#rex

Regards,
Brenner

——————————
Brenner Klenzman
Owner/Principle
WilloWare, Inc.
Wapella IL
——————————
——————————————-

[David Musgrave]

In this case there is no need for a solution using an ISV product. I got a suite of report writer functions added into core Dynamics GP and one of them is exactly what you need.

See the blog post I mentioned for how to use the rw_GetInternetInfo() and rw_GetInternetText() functions in calculated fields on a report.

——————————
David Musgrave MVP, GPUG All-Star

Managing Director
Winthrop Development Consultants

Perth, Western Australia

http://www.winthropdc.com
——————————
——————————————-