From the Newsgroups: FRx XBRL

I am starting a weekly section called From the Newsgroups intended to bring some of the most intriguing questions and informative responses from the online technical community. To keep things relatively anonymous, no names will be given out… just the questions and the responses.

The series open with a question posted on the Partner Online Technical Community forum regarding FRx XBRL capabilities:

“I have a client who is working on converting FRx statements to the XBRL format. With service pack 11, we are able to use the 2009 taxonomies, which is great. She needs to submit her statements in XBRL format. There are a few questions that I cannot answer for her and am hoping someone can point me in the right direction.

1. XBRL format will not include calculated columns; XML format will. Is this a limitation of XBRL or FRx?

2. If FRx, can we use XML and then convert to XBRL? Can we export to Excel and convert to XBRL from there?

3. Any suggestions on the best way to view what the result will be from an XML/XBRL format before she actually sends the statements off.“

The following is the answer given by a Microsoft support engineer:

“FRx Reporter can print basic XBRL reports and instance documents. Since the time XBRL support was added to FRx, the XBRL specifications have evolved and all required features are not available in FRx Reporter. The following list describes potential incompatibilities:

• FRx only supports monetary items. You cannot generate decimal, pure (ratio/percentage), or string/text data.

• FRx does not support footnotes.

• FRx does not provide a way to enter the entity scheme and identifier that are a required part of the contexts in an instance document. Generic information is generated, and these items will have to be manually changed afterward.

• FRx generates an instance document for each unit of the reporting tree. This may not be what you want. Frequently, all reports should be combined into a single document.

FRx does not support CALC columns when you use the XBRL output. Only GL and Forecaster column types will export data. I wasn’t able to find a way to convert an Excel file to XBRL. Since FRx cannot export CALC columns to XBRL format, you’d have to generate the report to another format, like Excel. It may be possible to convert that to XBRL, but that would be something outside of FRx support.“

For more information on XBRL take a look at the following resources:

XBRL.org – Click here
XBRL Compliance Goes Worldwide: Get a Head Start with Microsoft Dynamics GP Analytical Accounting and Microsoft FRx – Click here (registration required)

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Changing the Hyperspace property for a lookup button added with Modifier

It sounds like some word out of Captain Kirk’s vocabulary, but for Dexterity developers Hyperspace is a well known property that allows a Dexterity-based application to run the change script for a push button, i.e., a lookup button, without moving the focus from the currently-selected field. For example, you won’t want the ‘Customer Number’ field on the Customer Maintenance window to execute it’s change script before you have selected a customer from the Customer Lookup window as the lookup button is clicked… that’s what Hyperspace does, it prevents such situations by keeping the focus on the ‘Customer Number’ field until a customer ID is returned from the lookup window into the field.

The problem is, this property is locked down for push button controls added with Modifier, and while you cannot edit the push button change script with Modifier, you may still want to trigger off such controls with Dexterity code. This is pretty common for customizations where you don’t want to create an alternate versions of a GP form, but require a control placed directly on the GP form in order to call your own Dexterity code (see my article Developing Microsoft Dynamics GP hybrid integrating applications).

So, here is the workaround for changing the Hyperspace property of a push button control added with Modifier:

1. Add the push button control to your Modified window. In my example I will be using the sopCustomerItemMnt form. The following illustrates the changes made:

2. Save your modified window and return to Microsoft Dynamics GP. Open the Customization Maintenance window and locate the sopCustomerItemMnt. Click the Export button to export the modified window to a package file. The following is the content of exported package file:

= Form	"sopCustomerItemMnt"
{
	Datatypes
	{
		Datatype	"Lookup"
		{
			Control	"PushButton"
			DefaultDown	"00000"
			DefaultMouseOver	"00000"
			DefaultUp	"00000"
			Prompt	""
			PromptDown	""
			PromptMouseOver	""
			StaticType	"Mixed"
			~ItemImages
			{
				PictItem	"00001"
				{
					Item	"Field_Lookup_PB_Up"
				}
			}
		}
		Datatype	"UPC_Code"
		{
			AllowOddLength	"false"
			Control	"String"
			Format	""
			KeyableLength	"31"
			StorageLength	"32"
		}
	}
	Fields
	{
		Field	"Lookup"
		{
			ArraySize	"0"
			Datatype	"(L) Lookup"
			LookupForm	""
			PhysicalName	""
		}
		Field	"UPC_Code"
		{
			ArraySize	"0"
			Datatype	"(L) UPC_Code"
			LookupForm	""
			PhysicalName	""
		}
	}
	Windows
	{
		= Window	"sopCustomerItemMnt"
		{
			Fields
			{
				WindowControl	"(L) Lookup"
				{
					Alignment	"center"
					Appearance	"2D Border"
					BackColor	"System - Button Face"
					Border	"true"
					ButtonStyle	"GraphicOnly"
					Cancel	"false"
					ControlType	"PushButton"
					Default	"false"
					Editable	"true"
					EndTransaction	"false"
					Field	"(L) Lookup"
					Font	"System (generic)"
					FontBold	"false"
					FontColor	"System - Button Text"
					FontItalic	"false"
					FontUnderline	"false"
					HyperSpace	"false"
					LinkedLookup	"10002"
					LinkedPrompt	"0"
					Pattern	"(none)"
					PatternColor	"White"
					PositionLeft	"567"
					PositionTop	"186"
					ResizeHoriz	"StayLeft"
					ResizeVert	"StayTop"
					SizeHeight	"20"
					SizeWidth	"18"
					TabStop	"true"
					Tag	"10003"
					Tooltip	""
					UseUniqueHelp	"true"
					Visible	"true"
					ZOrder	"81"
					Zoom	"false"
				}
				WindowControl	"(L) UPC_Code"
				{
					Alignment	"left"
					Appearance	"2D Border"
					AutoComplete	"false"
					AutoCopy	"false"
					BackColor	"System - Window Background"
					Border	"true"
					ControlType	"String"
					DisableLookup	"false"
					Editable	"true"
					Field	"(L) UPC_Code"
					Font	"System (generic)"
					FontBold	"false"
					FontColor	"System - Window Text"
					FontItalic	"false"
					FontUnderline	"false"
					LinkedFormat	"0"
					LinkedLookup	"0"
					LinkedPrompt	"10001"
					Password	"false"
					Pattern	"(none)"
					PatternColor	"White"
					PositionLeft	"408"
					PositionTop	"186"
					ReadingOrder	"LeftToRight"
					Required	"false"
					ResizeHoriz	"StayLeft"
					ResizeVert	"StayTop"
					SavedOnRestart	"false"
					SetChangeFlag	"true"
					SizeHeight	"20"
					SizeWidth	"160"
					TabStop	"true"
					Tag	"10002"
					Tooltip	""
					UseUniqueHelp	"true"
					Visible	"true"
					ZOrder	"80"
					Zoom	"false"
				}
				WindowPrompt	"10001"
				{
					Alignment	"left"
					Appearance	"3D Highlight"
					BackColor	"System - Button Face"
					Border	"true"
					Font	"System (generic)"
					FontBold	"false"
					FontColor	"System - Button Text"
					FontItalic	"false"
					FontUnderline	"false"
					Pattern	"(none)"
					PatternColor	"White"
					PositionLeft	"328"
					PositionTop	"186"
					ReadingOrder	"LeftToRight"
					ResizeHoriz	"StayLeft"
					ResizeVert	"StayTop"
					SizeHeight	"20"
					SizeWidth	"80"
					Text	"UPC Code"
					Visible	"true"
					ZOrder	"79"
					Zoom	"false"
				}
			}
			LastTag	"10003"
		}
	}
}

3.Perform a search for “Hyperspace”. Replace the property value from false to true. Save the package file. In the above sample package file content, I have highlighted the Hyperspace property in red.

4. Use the Customization Maintenance window to reimport the package file. This should be all! Now your lookup button’s hyperspace property is set to true.

Warning: this method of changing the Hyperspace property of a push button is not supported by Microsoft and must be performed by a developer who understands the implications.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

What’s new in Microsoft Dexterity 11.0

The excitement around Microsoft Dynamics GP 2010 could not be any higher. Traffic on my site has doubled since I began releasing information about the upcoming version, which is currently being beta tested by a number of partners and have even seen a customer gone live as reported by Inside Microsoft Dynamics GP. For past articles, click on the following links:

Microsoft Dynamics GP 2010 – Navigation Enhancements
Microsoft Dynamics GP 2010 – Security Enhancements

However, none of these application enhancements could have been possible without the hard work of the Microsoft Dexterity team, which brought the following enhancements of their own to version 11.0 of Dexterity:

IDE Enhancements

• Enhancements to Report Writer – While a number of you still dredge the fact that there is still Report Writer, the bottom line is I believe there will be Report Writer for a while. There’s simply too much code tied to Report Writer and way too many reports in the system to simply replace them all. So to compensate, my friends in the Dex team have significantly improved the rendering of report outputs in HTML format and have provided a way to create an XML representation of the report layout.
• Context Menus – While this is nothing new in the Windows world and holds true for many other applications, it was a long overdue feature in Dexterity. Dexterity now supports right-click enabled context menus for windows, window fields, scrolling window lines and scrolling window fields. When a context menu is displayed, a script is run that allows commands to be added to the context menu. Here is a sample script:

local integer result;
local integer command_tag;
local integer view_menu_command_list;
local integer context_menu_command_list;

{Build the View submenu}
view_menu_command_list = Command_GetTag(command View of form RESM_Explorer);

{Only need to build this once. Unlike the context menu, it is not cleared automatically when the context menu action is complete.}
if CommandList_NumCommands(view_menu_command_list) = 0 then
  {Large Icon}
  command_tag = Command_GetTag(command Large_Icon of form RESM_Explorer);
  result = CommandList_Add(view_menu_command_list, command_tag);

  {Small Icon}
  command_tag = Command_GetTag(command Small_Icon of form RESM_Explorer);
  result = CommandList_Add(view_menu_command_list, command_tag);

  {List}
  command_tag = Command_GetTag(command List of form RESM_Explorer);
  result = CommandList_Add(view_menu_command_list, command_tag);

  {Report}
  command_tag = Command_GetTag(command Report of form RESM_Explorer);
  result = CommandList_Add(view_menu_command_list, command_tag);

end if;

{Build the context menu}
context_menu_command_list = Command_GetTag(command cmdListContextMenu);

{Add the View sub-menu}
result = CommandList_Add(context_menu_command_list, view_menu_command_list);

{Separator}
command_tag = Command_GetTag(command cmdSeparator);
result = CommandList_Add(context_menu_command_list, command_tag);

{Print}
command_tag = Command_GetTag(command Print of form RESM_Explorer);
result = CommandList_Add(context_menu_command_list, command_tag);

As you can tell, context menus are based on command lists… same principle used for standard menu items.

• Dexterity now sports a new Watch window, wich can be selected from the Debug menu in test mode.



• The Script Debugger has a context menu in test mode. The truth is, I could not get this feature to work in my current build of Dexterity – 11.00.0206.000

 

SanScript Enhancements

• run report statement now has the option to define a new XML export type. The constant defined for this operation is XMLFILE. The statement syntax is still the same:

run report report_name {with restriction boolexp}{sort by field{nocase}{descend}}  {by key_name  by number expr}{legends [string_expr_list  array_index_list]  {destination screen_boolean, printer_boolean  {, export_type, export_name}}{  printer printer_settings}

• Similar enhancements have been implemented for the run report with name statement. You can also specify an XML export type.

run report with name report_name {with restriction boolexp}{sort by field{nocase}{descend}}  {by key_name  by number expr}{legends [string_expr_list  array_index_list]  {destination screen_boolean, printer_boolean  {, export_type, export_name}}{  printer printer_settings}{in dictionary product_ID}

• The savefile() function has also been given the ability to save documents to XML format by using the XMLFILE constant.

local string path;local integer file_type;

if savefile("Export file", file_type, path, "XML file (*.xml)*.xml) then    call Export_XML_File(file_type, path);end if;

 

New Functions

File
File_GetSize()
File_GetTempDirectory()

File list
FileList_Add()
FileList_Count()
FileList_Create()
FileList_Destroy()
FileList_Get()
FileList_Remove()
FileList_ShowDialog()

File type
FileType_CanAppend()
FileType_FillList()
FileType_GetExtension()
FileType_IsValid()

MAPI
MAPI_DisplayReplyToDialog()
MAPI_GetAddress()
MAPI_IsLoggedOn()
MAPI_IsMailEnabled()
MAPI_LogOff()
MAPI_LogOn()
MAPI_ProfileGetAddress()
MAPI_PropertyListCount()
MAPI_PropertyListCreate()
MAPI_PropertyListDestroy()
MAPI_PropertyListGetValue()
MAPI_PropertyListGetValueByIndex()
MAPI_PropertyListSetValue()
MAPI_ResolveAddress()
*MAPI_Send()
*MAPI_SendDialog()

* Enhanced to support attachments, reply to lists, and properties not exposed through the MAPI function.

Path
Path_ParsePathFromPath()

Registry
Registry_DeleteKey()
Registry_DeleteValue()
Registry_GetProtectedString()
Registry_GetValue()
Registry_SetKeyValue()
Registry_SetProtectedKeyString()

Trigger
*Trigger_RegisterFocus()
*Trigger_RegisterFocusByName()

* Enhanced to support focus triggers on context menu events.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Microsoft Dynamics GP 2010 – Navigation Enhancements

After the Microsoft Dynamics GP 2010 Security Enhancements mentioned in my previous article, I wanted touch base on some of the enhancements to the overall navigation experience.

One of the first things that jump out right at you when you get to the desktop is the new Microsoft Dynamics Online Connect feature. Think of this feature as your window to the Dynamics Community. That’s correct! Behind that window there are hundreds of thousands of other Dynamics GP users just like you. The problem? The Connect link seems to be down since sometimes yesterday, as of the date and time of publishing of this article. By the way, you can find the Connect site link (also known as the GP Role Center site) in the HomePage.xml file stored in your temp folder (%temp%).

You also have the ability to now view reminders as visual cues and multiple metrics on the page. I have to play more around with this feature as I was not quite sure I was able to get all metrics to be displayed at once.

The Navigation Pane will now only show the series buttons and associate lists for modules that are marked in the Registration window. Phew! This will certainly clean up the amount of options you have to sift through when working with your navigation lists.

You can now define and save a default lookup view for your master records by selecting the Set as Default View option from the View menu or using the right-click menu in the lookup window. The default view you select will be the view displayed when you open the lookup window the next time. You can set a default view for the following lookup windows:

• Items lookup window




• Accounts lookup window



• Employees lookup window



• Customers and Prospects lookup window



• Salespeople lookup window



• Vendors lookup window

The default view is saved on a per user, per company basis. The truth is, this functionality is not working as intended at this time. After I changed the sorting options and attempted to set these changes as the default value for my lookup window, the option appeared grayed out. I discussed this with MVP Mohammad R. Daoud who confirmed it in his new article titled Inside Microsoft Dynamics GP 2010 – Issues found so far.

All and all, in the above images you saw the right-click context menu, which leads me into the next navigation enhancement.

“You can right-click in a text or numeric field to see a shortcut menu. The shortcut menu contains options from the Edit menu” — this, per the What’s New help file, but I believe Microsoft felt short in the definition as, right-click enables more of a context sensitive menu, rather than just shortcuts and operates in a number of places. For example, in SOP, you are able to display not only the standard copy/paste options, but also the scrolling window insert and delete options.

There are a few more enhancements in this area that I would like to discuss, but they will be part of a future article as I will need Microsoft SQL Server Reporting Services to demonstrate a few of them.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Microsoft Dynamics GP 2010 – Security Enhancements

This article opens the series on the new features and enhancements available in Microsoft Dynamics GP 2010. I will start out with something dear to the heart of systems administrators and that’s security. So let’s take a look at the first set of enhancements that have been introduced to the new release.

Automatic Login

Now you have the option to log on to Microsoft Dynamics GP and a default — favorite if you will — company automatically. In order for the login to take place automatically, the system administrator must mark the Enable Remember User field in the System Preferences window.

You will notice a new checkmark on the Welcome to Microsoft Dynamics GP window to Remember user and password.

And you will also notice a checkmark on the Company Login window to Remember this company.

As mentioned before, having the ability to access these new enhancements is a system wide administrative option set via the System Preferences window – MSDGP > Tools > Setup > System > System Preferences.

Copy User Security Settings

System administrators lost a lot of the cool features available in version’s 8.0 and 9.0’s Advanced Security module after upgrading to Microsoft Dynamics GP 10.0 role base security model. One feature in particular that put dents in a lot of desks was the fact that security could not be copied across user accounts.

For Microsoft Dynamics GP 2010, you can now use the Copy User Security window and the User Setup window to copy security settings (roles, tasks, and company access) you want to copy from one user to another.

For example, if you hire a new purchasing agent, you can copy the security settings from an existing purchasing agent instead of manually specifying the security settings. If the new purchasing agent is transferring from another position in your company, copying replaces any existing security settings for the user you are copying to.

Security Roles and Tasks Filters

You can now filter out only the security tasks and items to which access has been granted in the Security Task Setup window, the Security Role Setup window, and the User Security Setup window instead of viewing all security tasks, roles, and items. This makes understanding security settings assigned to tasks, roles, and items a breeze.

These enhancements will certainly facilitate security administration for the system administrator, while allowing users the ability to speed up login into the company of their preference. Please let me know what you think about these enhancements.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Microsoft Dynamics GP 2010 Beta Buzz

Folks, if you haven’t been keeping up lately with the news, there’s lot of buzz going on in the Community about the upcoming release of Microsoft Dynamics GP 2010 (Get used to it! GP “11” was just the codename). The Microsoft Dynamics GP Product Management and Marketing team kicked it off big with an exclusive Parner webcast which I attended, demonstrating some of the key features and messaging to be delivered to existing and prospective customers. Can you believe they were more than 500 partners signed on at once?

As I mentioned couple days ago, I got a hold of the Beta version minutes after the link was available and could not wait to install it to start playing around with the new features, but before I start blogging about these, here are a few sources you will want to start checking for Microsoft Dynamics GP 2010 information:

Start Getting Up to Speed on Microsoft Dynamics GP 2010 @ Inside Microsoft Dynamics GP
It is a GREAT Day! @ Inside Microsoft Dynamics GP
Microsoft Announces Beta Release of Dynamics GP 2010 @ MSDynamicsWorld

If you haven’t cast your vote on the new features you like, then you need to do so. Remember, if you don’t vote, you don’t get to cheer (or complain depending on your feelings about voting).

New Facebook Poll: What 1 new feature of Dynamics GP 2010 are you most excited about? @ DynamicAccounting.net

MVP Mark Polino cannot contain his excitement and almost lost his voice in his new podcast about Microsoft Dynamics GP 2010:

New Podcast looking at GP 2010 is up now @ DynamicAccounting.net

MVP Mohammad R. Daoud started playing with 2010 Beta earlier than me (and that’s a lot to say right there!) and has already released two teasers about the new functionality available in 2010:

Inside Dynamics GP 2010: Lookups @ Mohammad R. Daoud
Inside Microsoft Dynamics 2010: Cue Reminders @ Mohammad R. Daoud

For Partners and Consultants: if you (or your company) are not participating in the Beta program, keep in tune with your humble blogger here to find out about the new features. Starting today, I will begin to look at each of these to provide you get you up to speed. If you are in the US, make sure you enroll and be an active participant in some of the upcoming webinars and roadshows. Visit the Microsoft Partner Learning Center for more information.

For Customers: if you are a customer (or prospective customer) looking to learn about Microsoft Dynamics GP 2010 then Convergence Atlanta is the place to be this year. In the mean time, contact your Microsoft Partner and start asking about upgrading options and upcoming promotions. Let’s face it: now that the economy is rebounding, you don’t want to be caught running old technology and looking to ramp up when business picks up again. If you don’t have a partner, simply shoot me an email and I can begin discussing your options.

Stay tuned for a first look at Microsoft Dynamics GP 2010.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Microsoft Dynamics GP 2010 – Coming Soon to a Partner near you!

Well, I really don’t want to make you envious of my fortune (but at the same time I do!) as I just got a hold of the Beta release of Microsoft Dynamics GP 2010, thanks to the folks at Microsoft and some of the perks that come along with being a Microsoft MVP.

For now, I cannot disclose anything about the Beta release due to NDA restrictions, but I am already exploring the new features and enhancements to existing modules. I don’t know when the NDA period will be over, but sure can’t wait to start blogging about these new features.

If you are a customer reading this and you are still running older releases of GP (9.0 or prior), well let me say that NOW is the time to begin planning your upgrade. Why? Well, you pay for a maintenance plan, don’t you? In addition, this is the biggest release EVER! The economy is slowly turning around and you want to have all the tools at your disposal for when things are at peak: better inquiries, better BI capabilities from within GP, better analysis capabilities, better everything. Your business and employees will thank you for the effort. Get in touch with your partner and if you do not have one, well… contact me! I will sure be more than glad to help you clear the ground to welcome Microsoft Dynamics GP 2010.

Related Articles

It is a GREAT Day! – Inside Microsoft Dynamics GP

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

IM – How to filter source query records dynamically

Ever wanted to give users the ability to limit source records dynamically? How about being able to filter records from in a source query by a date range? Well, I encoutered this situation working on my current project.

Out of the box, Integration Manager offers the ability to retrieve records and set static filters to source records. However, in many cases users may want to dynamically (at runtime) establish a date range or any other range parameter for the records, then have these ranges applied to the source query.

This is possible by using some old fashioned VBScript with Integration Manager. Consider the following records:

DOCUMENT_DATE JOURNAL_NUMBER ACCOUNT_NUMBER DEBIT_AMOUNT CREDIT_AMOUNT
12/01/2009 100  000-1200-00 100.00  0.00
12/01/2009 100  000-6620-00 0.00  100.00
12/15/2009 200  000-1201-00 22.50  0.00
12/15/2009 200  000-6630-00 0.00  22.50
01/06/2010 300  000-1200-00 120.00  0.00
01/06/2010 300  000-6620-00 0.00  120.00
01/20/2010 400  000-1201-00 52.50  0.00
01/20/2010 400  000-6630-00 0.00  52.50

In order to make our integration interactive, we must first prompt the user to enter the date restriction in the format required to filter the data:

Before Integration

Dim startDate, endDate

Do
  startDate = InputBox("Enter the start date for your transactions (mm/dd/yyyy).")
  If Not IsDate(startDate) Then
     MsgBox "Invalid date format, please try again."
  End If
Loop Until IsDate(startDate)

Do
  endDate = InputBox("Enter the end date for your transactions (mm/dd/yyyy).")
  If Not IsDate(endDate) Then
     MsgBox "Invalid date format, please try again."
  End If
Loop Until IsDate(endDate)

If CDate(startDate) > CDate(endDate) Then
 MsgBox("The start date must be greater than the end date. Integration will end now.")
 CancelIntegration
End If

' Store the user input in global variables that can be retrieved later on
SetVariable "gblStartDate", startDate
SetVariable "gblEndDate", endDate

Now we can apply the user’s input as restrictions to our source query data by invoking the AdditionalCriteria property of the Query object.

Before Query

Query.AdditionalCriteria = "DOCUMENT_DATE >= '" & CDate(GetVariable("gblStartDate")) & "' AND DOCUMENT_DATE <= '" & CDate(GetVariable("gblEndDate")) & "'" 

Note that the Query.AdditionalCriteria will only work on source queries that use a Text or Simple ODBC DSN. The AdditionalCriteria property will not work on Advanced ODBC queries.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Parsing Long String fields in Extender for using with Report Writer

A few days aback I came across a Partner Forum question where the partner was attempting to add an Extender field to a Report Writer report, not without his share set of challenges.

In their infinite wisdom, the folks at eOne added a trigger to the rw_TableHeaderString Report Writer function which allows them to expose data to Report Writer without having to create alternate versions of a report in their application. In turn, with a few steps outlined in the Extender manual, users can invoke the rw_TableHeaderString as a user-defined function in a string calculated field to retrieve the piece of data needed from an Extender table by passing in the Window ID, the key fields, and the position of the field to retrieve on the Extender window. This is an example from such call:

Calculated Field: EXTENDER_KEY
Expression Type: Calculated
Result Type: String
Expression: STRIP( SOP_HDR_WORK.SOP Number )

Calculated Field: (C) AdditionalShippingInfo
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 )

This is all good! But here comes the issue… Extender Long String fields are 255 characters long and Report Writer string calculated fields support up to 80 characters. The partner tried to use the rw_ParseString Report Writer function to parse the Extender string in various lines as follows:

Calculated Field: (C) AdditionalShippingInfo_Line1
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_ParseString FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 ) 50 1)

Calculated Field: (C) AdditionalShippingInfo_Line2
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_ParseString FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 ) 50 2)

Of course, when the report was executed it threw an “Error in Equation” error as Report Writer does not support nesting of user-defined function scripts.

At this point, the only option available in order to be able to retrieve a long string and print it on the report is VBA, ADO, and a SQL Server view. The following is the process with references to articles that will help you with each step:

1. Create an Extender view of your data. You may start by reviewing Creating SQL Views of Extender Data over at Developing for Dynamics GP to get an understanding of this process. David Musgrave also outlines a sample view to get you started.

2. Create string calculated fields on your report that will be used to parse the Extender Long String field.

3. Add your report to VBA and add the string calculated fields created in step 2 to the VBA project. Also, add any key fields on the report needed to retrieve the data, i.e., SOP Number.

4. Use ADO to query the view for the information stored and store the data in the calculated fields. You may want to review Using ADO with VBA with Report Writer over at Developing for Dynamics GP for samples on the technique.

While the workaround might seem a bit lengthy, the results will speak for themselves, so don’t give up on Report Writer just yet :).

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Using Business Alerts with Extender Tables

For those of you looking to create Business Alert events for Extender tables and data, follow these instructions provided by Microsoft’s Dawn Langlie from the Partner Online Technical Community.

“In order to add Extender tables so you can see them in Business Alerts, first go to Tools-Utilities-System-Business Alerts. Here is where you will select the tables. Pick “eXtender” as the Product and Pick “Project” for the series. If you are interested triggering off eXtender windows, the tables you will want to insert are the Extender Window Field Dates, Extender Window Field Strings, Extender Window Field Times and Extender Window Field Numbers. Then click the process button in the lower right corner. This will move the tables in the Tables Available for Alerts section.

Then create your business alert by going to Tools-Setup-System-Business Alerts. Choose to create a new alert. Select your Database and give the Business Alert a name. In “Select Tables” window, you need to change the series dropdown to “Integrating Applications”. This will allow you to see your eXtender tables. Insert the tables and hit next.“

I added the screenshots to enhance Dawn’s instructions, but there you have it! Now you know how to create alerts on your Extender data too!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com