Wednesday, January 27, 2010

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/

Tuesday, January 26, 2010

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/

Thursday, January 21, 2010

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/

Wednesday, January 20, 2010

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/

Tuesday, January 19, 2010

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/

Sunday, January 17, 2010

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/

Saturday, January 16, 2010

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/

Thursday, January 14, 2010

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/

Tuesday, January 12, 2010

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

New article on MSDynamicsWorld: MVP Mark Polino and his Nostradamus predictions for 2010

"2010 will be a milestone year for Microsoft Dynamics GP..."

Microsoft Dynamics GP has a new Nostradamus in MVP Mark Polino. Mark looks into his crystal ball and predicts the fortune of Microsoft Dynamics GP 11, BI, Management Reporter and FRx, and the Microsoft Dynamics GP Community as a whole in his latest MSDynamicsWorld article... heck, the guy even talks politics!

Don't miss out on this great article.

Until next post!

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

Sunday, January 10, 2010

Building a COM Interop Assembly to use with Microsoft Dexterity

I am currently building some customizations for a customer of mine in the aerospace industry. My customer required a library of trigonometric functions that could be used to extend their Dexterity integrating applications.

To solve this problem, we turned to .NET to create COM interop assembly. The idea was to take advantage of the standard Math class methods available with the System namespace - System.Math . The following is an excerpt of the code we created:

TrigonometricFunctions.cs

//Created by Mariano Gomez, MVP
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;

namespace TrigonometricFunctions
{
public class TrigonometricFunctions
{
[GuidAttribute("8268A95E-6FCB-4FB2-88A1-1E38F49F4FB8"), ClassInterface(ClassInterfaceType.AutoDual)]
public class TrigFn
{
// dx in degrees
public double fSin(double dx)
{
double angle = Math.PI * dx / 180.0;
// returns sin(dx)
return Math.Sin(angle);
}

// dx in degrees
public double fCos(double dx)
{
double angle = Math.PI * dx / 180.0;
// returns cos(dx)
return Math.Cos(angle);
}

// dx in degrees
public double fTan(double dx)
{
double angle = Math.PI * dx / 180.0;
// returns tan(dx)
return Math.Tan(angle);
}

}
}
}


Once the functions in the TrigFn class were in place, we set up the assembly information in Visual Studio's marking the option to Make assembly COM visible.


So we did not have to register the assembly manually, we took advantage of Visual Studio's ability to register the assembly for COM interop under the Build settings. For purposes of demostration, I created a simple Dexterity form, as shown below:



The following is the code added to the '(L) Sine' button:

Sine button CHG script

{ script: l_Sine_CHG }

local TrigonometricFunctions oTrig;
local currency angle, sine;

oTrig = new TrigonometricFunctions.TrigFn();

'(L) Prompt' = "The Sine value is ";
'(L) Conversion' = oTrig.fSin('(L) Angle');

In the code above, TrigonometricFunctions is a data type created after adding the TrigonometricFunctions COM interop assembly as a library to our Dexterity application. The data type references the TrigonometricFunctions.TrigFn class.

References:

  • Using a .NET Assembly from a Dexterity-Based Application - Click here
  • Microsoft Dynamics GP 10.0 White Paper: Using a .NET Assembly from a Dexterity-based Application - Click here


  • Downloads:

    TrigonometricFunctions .NET project - Click here
    Dexterity Sample App - Click here

    Until next post!

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

    Thursday, January 7, 2010

    New article on MSDynamicsWorld: "Integrating Microsoft Dynamics CRM with Microsoft Dynamics GP"

    "No, this is not the Chris Tucker and Jackie Chan movie..."

    One of my goals for this year is to become technically and functionally skilled in Microsoft Dynamics GP CRM. However, I never expected this to happen so quickly and abruptly as it did some 3 weeks ago with a "weekender-all nighter-gotta-get-it-done-by-Monday" project I worked on with my business partner. I collected some of the best practices and experiences from that project and put them together in my new MSDynamicsWorld article.

    Until next post!

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

    Wednesday, January 6, 2010

    Register now for Convergence 2010 in Atlanta


    Registration Is Open

    Register now for the premier Microsoft Dynamics event and learn how users like you drive business success by maximizing their Microsoft Dynamics solution. Take advantage of this unique chance to network with peers and Microsoft product experts, share ideas, and get up-to-speed on the latest products and technologies. Learn more on the Convergence Web site, then use the convenient e-mail feature to help tell your boss and colleagues why Convergence is an opportunity not-to-be-missed.

    Convergence 2010 Atlanta is cost-effective and easy to attend. Atlanta offers quality and affordable hotel options and is a major air transit hub, which makes it a convenient destination. And the event starts on a Saturday in order to take less time from your work week.

    Experience an event designed to deliver the most relevant information for your business needs, with content driven by users like you. Be sure to register early and save!

    Best yet, it's right in my back yard! I look forward to seeing you in Atlanta!

    Until next post!

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

    Monday, January 4, 2010

    Congratulations to Queen Victoria!

    This is becoming a new year's habit -- in a very pleasant way! Victoria Yudin has now been crowned with the Microsoft Most Valuable Professional award for the 6th straight year. Keep in mind that the MVP award program has been in place for only 14 years, so quite an accomplishment for Victoria. The truth is the Dynamics GP community has quite a bit to thank Victoria for... how about a whopping 3,450 contributions to the Microsoft Dynamics GP public newsgroup for starters.

    Insider's note about Victoria: she was originally born in Moscow, Russia and move to the US when she was only 10 years old. She is an avid reader and enjoys video games in her free time. She also has a tremendous sense of humor.

    Until next post!

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

    Post through from Microsoft Dynamics GP Manufacturing

    As if Post Through wasn't hard enough to understand for the core financial and distribution modules (take a look at my previous article Why my transactions don't post through GL even when I have the flag selected?) here comes Manufacturing. Take a look at these comments from Microsoft's Becky Berginski:

    "Within Microsoft Dynamics GP there is a place to setup your modules posting options. However, there is not an option specifically for Manufacturing. Manufacturing looks at the Inventory and General Ledger options to determine the MFG posting options. If the transactions you enter in Manufacturing post adjustments to Inventory then the system looks at the settings for your Inventory module. (i.e. To determine if it is stops in GL or posts through GL.) If the transactions don't update inventory (i.e. closing MO), but GL adjustments are made then it looks at your GL settings to determine how it posts.

    Go to Tools Setup Posting Posting. Review the settings for both Inventory and General ledger transaction entry options.The posting journals that print after a posting of an MO Receipt are those associated with Inventory Adjustments. You can turn these off (or have them saved to a file) using the Posting Setup window (Tools - Setup - Posting - Posting). Be sure to choose the Inventory series and the Transaction Entry origin. Keep in mind that these settings are now on those reports for regular Inventory posting also and not just the posting of Manufacturing Components.

    There are certain labor transactions that will not post through the GL. Data Collection transaction never post Through the GL. You would need to locate the Journal Entry and adjust the Transaction Date. Data Collection entries will have a Source Doc code of 'DC_ADJ' and reference to the MO number."

    Becky then goes on to clarify that,

    "There are some transactions that will not post to GL. Most of them do. Below is a listing of what does. There are various postings that will come from Manufacturing. Some of these postings go directly to the GL. Some of the postings go through Inventory first and then update the GL. The transactions that go through Inventory utilize the posting setup for that series. The transactions that post directly from manufacturing to GL utilize the posting setup for the financial series. Here are some examples of the various places that one can post from in Manufacturing:

    1. If they are using the Quick MO Entry window, postings will occur when they click on the Close MO button.

    A. They will have one adjustment posted in Inventory to reduce quantities for the components. This entry will update GL when it finishes posting in IV.

    B. They will have a second adjustment posted in Inventory for the receipt of the finished goods. This also will update GL when it finishes posting in IV.

    C. If there is labor or machine time set up for the finished good, a journal entry for those costs will post directly to the GL.

    D. Also if there are any variances, a journal entry to record those will post directly to the GL.

    2. If they are using the basic MO Entry window, postings may occur a number of different times and from different windows:

    A. Components can be issued from the Component Transaction Entry window. This posts a decrease adjustment in IV and then GL when the posting completes in IV.

    B. Any labor and machine time recorded in any of the WIP windows will post costs directly to the GL.

    C. The posting of MO receipts will post an increase adjustment for the finished good in IV and then will update GL. If components are backflushed a decrease adjustment will be posted to IV and GL will be update after the IV posting is complete. Labor and machine costs may post to GL if they are backflushed.

    D. When closing the MO, variances will be posted directly to GL. Also additional component quantities may be included--this means a decrease adjustment in IV and a related posting to GL.

    One other setting that sometimes prevents users from posting thru GL is in the Security Setup window (Tools - Setup - System - Security). Choose product of Microsoft Dynamics GP, Type of Windows, and Series of Financial. Be sure that the users posting in Mfg have access to the following two windows:

    GL Open Files
    GL Open Financial Files - internal

    As I mentioned transactions stemming from labor or machine data collection will never post through the GL."

    Hope you find this information useful and a good 2010 opening article.

    Until next post!

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

    Friday, January 1, 2010

    Happy New Year!

    A fairly popular New Year's eve tradition is to define a list of resolutions for the incoming year. These resolutions become goals that you try very hard to achieve throughout the year in course. Comes year-end, you evaluate what happened, you praise the hits, and complain about the missed resolutions. You slip some of those unachieved goals into next year's resolutions, and the cycle starts all over again.

    The one issue I see with New Year's resolutions is, a lot of us tend to just set way too many! The consequence? Lots of frustrations at the end of the year. So, with this in mind I have adopted a new strategy: have one single New Year's resolution that will drive a number of other initiatives.

    So here is my one single New Year's resolution: increase readership to this site and all other outlets where you can find The Dynamics GP Blogster. So here is where you come in! In order to accomplish this one goal and all initiatives that derive from it I need your help: a) please keep reading my articles and sending in your comments, and b) spread the word!

    How do these two seemingly simple requests help my initiatives? Very simple! If you keep reading and spreading the word, a) it will be a lot easier for the entire Microsoft Dynamics GP Community to find key pieces of information that makes their lives as easy as yours have become by reading this site, b) it will increase the adoption rate of Dynamics GP. The more users know about all the help outside of CustomerSource and PartnerSource, the less apprehensive they will be towards learning GP, and last but not least, c) it will allow me to continue helping the Community. See, the more I know what people are reading, the better I can address their needs (focused topics, workshops, series, etc). In addition, it increases my chances of coming to a place near you. One of the reasons I made it to the Microsoft Dynamics GP Technical Conference 2009 was due to a fairly successful campaign reaching out to you through various technical articles.

    I know you follow me, I know you like (and sometimes dislike, but that ok too) what you read on this site, I know you stay tuned, but think of the benefits others could receive from the same information you get here. So, the next time you speak to your peers or colleagues just mention this site. It takes two seconds and it goes one step more to achieve my New Year's resolution.

    Until next post!

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