SmartList Builder and creating Calculated Fields with Extender data

July 14, 2011

Just recently, I ran into a case where the partner was creating a SmartList Builder calculated field using data from the RM Open table (RM20101) via an Left Join table operation with the Extender Window Field Numbers. In fact, this is a very typical scenario for a lot of deployments where Extender is used, especially when you cannot use the standard Extender functionality to integrate with out-of-the-box smartlists.

The original SLB calculated field look something like this:

Extender Calculated Field

When the SLB smartlist was deployed, a number of results came back as zero for the records where there was no entry in the Extender Window Field Numbers table, even when the Sales Amount and Current Trx Amount fields had a value in the RM Open File table.

Paying a bit more attention to the issue made me think of how LEFT OUTER JOINs are processed by the Microsoft SQL Server query engine. This is best illustrated with the following example:

RM Open Extender
Customer Number Document Number Sales Amount Current Transaction Amount PT UD Key PT UD Number Total
AARONFIT001 INV3223 200.00 40.00 INV3223 5.00 5.00
ADAMPARK001 INV1020 100.00 20.00 NULL NULL NULL

Note that if Extender data was not entered for INV1020, a left outer join query would produce a NULL value as a result of the join operation. To overcome this situation, we applied the T-SQL ISNULL() function to the Extender field in SmartList Builder. Since SmartList Builder uses pass-through SQL to build each portion of the SELECT statement used to retrieve the records, then this should work just fine. The final calculated field is as follows:

Remember, Extender is a valuable tool to capture additional data and enhances the value of SmartList Builder when combined together to deliver reports. Pay special attention when creating calculated fields that rely on information from Extender tables in left join scenarios.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Advertisements

Unhandled script exception: Illegal address for field ‘[Not Found]’ in script ‘Unregister_Triggers’

May 17, 2011

If you have recently upgraded to Microsoft Dynamics GP 2010 R2, a few days aback, a partner reported receiving the following errors when returning from Report Writer:

Unhandled script exception:
Illegal address for field ‘[Not Found]’ in script ‘Unregister_Triggers’. Script terminated.

EXCEPTION_CLASS_SCRIPT_ADDRESSING
SCRIPT_CMD_LOAD_ATSI7

Unhandled script exception: Illegal address for field ‘[Not Found]’

The error is followed closely by:

Unhandled script exception:
Object has no reference.

EXCEPTION_CLASS_SCRIPT_BAD_PARAM
SCRIPT_CMD_DEREF_TABLE

Upon review, the error appears to be an issue with Extender and also happens when returning from Modifier. This issue has been written up as a bug and is scheduled to be fixed. To workaround the issue, exit Microsoft Dynamics GP 2010 R2 and log back into the system.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


>Opening a Microsoft Dynamics GP Extender window conditionally

March 23, 2011

>Today I came across an interesting request on the Microsoft Dynamics GP Partner Online Technical Community – here on referred to as the Partner forum. The partner wanted to know if it was possible to open an Extender window based on a specific Sales Transaction Entry document type being selected. In particular, the partner wanted to have the Extender window open only when the document type was an invoice.

Sales Transaction Entry window – Invoice

The first thing that comes to mind for most hardcore technical resources is to write some VBA or Visual Studio Tools code to do this. While not impossible, recurring to this type of customization to achieve the partner’s request, quickly becomes cumbersome and inflexible as a solution if more and more conditions are needed throughout the application or even the window.

However Extender has a little known feature called Window Groups.

A window group is made up of one or more windows. The windows that can be added to a Window Group are Windows, Detail Windows, Notes and Inquiries. Conditions can be added to each window to specify when each window will be opened.

Let’s see how this request would then be implemented in Extender:

First, we will need to setup the Extender window and associate the key fields that will be used against the Sales Transaction Entry window. As it stands, this is a very standard procedure. The following example shows two fields added to the Extender window needed:

Extender Window – Additional Invoice Information

Once the window is in place, we can proceed to create the Window Group and setup the condition as shown below:

Extender Window Group – condition for SOP Type = Invoice (4)

Note that in the Windows section we chose the previously created Extender Window. Next, in the Conditions section, we created a condition for SOP Type = 4 (Invoice). Now, in my books, that’s what you call simplicity!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


Parsing Long String fields in Extender for using with Report Writer

January 14, 2010

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

January 12, 2010

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


Transferring Extender customizations from one company database to another

April 28, 2009

The Microsoft Dynamics GP public newsgroup is always an amazing source of inspiration for new articles, this time, along the lines of a topic that I have been following closely since the announcements of the additions in Dynamics GP 10.0 Service Pack 4: Extender.

Today, we will explore two methods for transferring Extender customizations between company databases.


Method 1: Extender’s Export/Import functionality

Extender offers a native method for transferring customizations between company databases with the following steps:

1. Open Extender‘s Export window. Go to Microsoft Dynamics GP > Tools > Extender > Export.

a) Click on the Folder button to select the path and enter the file name for your export. Hint: try to use the name of the customization being exported as the file name.

b) Select the customization(s) to be exported by clicking on each of the objects.

2. Click Save to continue. This will create the XML export file with the customizations.

3. Switch companies in Dynamics GP. Click the Company Name on the Status bar and select the company where you would like to import the customizations into from the Company drop-down list. Click the OK button to continue.

4. Open Extender‘s Import window. Go to Microsoft Dynamics GP > Tools > Extender > Import.

a) Click on the Folder button and select the file previously exported in steps 1 and 2.

b) Click on the Import button to continue. This action will read the XML file and build the customizations in your target company database.


Method 2: Support Debugging Tool

That’s right! You can also use Support Debugging Tools’ XML Table Export and XML Table Import capabilities. With the aid of the sampling scripts presented in my previous post you should be well on your way to identify the tables that contain data. Once you have identified them, you can now use Support Debugging Tool to extract the data into an XML file from your source company database.

1. Open Support Debugging Tool’s XML Table Export window. Click on the Options button, then select XML Table Export.

2. In the Export window, select the tables to be exported.

3. Switch companies in Dynamics GP. Click the Company Name on the Status bar and select the company where you would like to import the customizations into from the Company drop-down list. Click the OK button to continue.

4. Open Support Debugging Tool’s XML Table Import window. Click on the Options button, then select XML Table Import.

5. In the Import window, select the file to be imported.

Now, click on the import button and you are done!

Extender allows users to perform easy customizations in their Dynamics GP system with just a few clicks or with minimum assistance from a developer. It’s time to harness the power of the tool. I will continue to explore other functions available to Extender user throughout the coming days.

Until next post!

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


Sampling data from Extender tables

April 25, 2009

Lets be honest! One of the most confusing aspects of working with Extender is being able to tell where things get stored. Even though all table names are pretty clear and need not too much explanation, the bottom line is people still get confused when trying to figure out what is stored where.

I for once, have spent some time working on a T-SQL script that will show all the data stored in Extender tables. I constantly use this method, because I can clearly see where things are when I need to look around for some way data got stored, without having to go executing SELECT statements one by one on each table.

The method is broken down into two steps: the script that retrieves all the extender tables, producing a formatted SELECT statement, and the script (a result of the former script) which actually displays the data in each table.

GetExtenderTables.sql


-- Created by Mariano Gomez, MVP
-- Code is "AS IS". No warranties expressed or implied and conferes no rights
SELECT '/* ' + RTRIM(TABLE_NAME)
+ '*/ SELECT ''' + RTRIM(TABLE_NAME) + ''' AS Extender_Table, * FROM '
+ RTRIM(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'EXT%'

NOTE: This query is only supported on Microsoft SQL Server 2005 and 2008.

When executed, the above query will produce the following results:

RetrieveExtenderData.sql


-- Created by Mariano Gomez, MVP
-- Code is "AS IS". No warranties expressed or implied and conferes no rights
/* EXT00100*/ SELECT 'EXT00100' as Extender_Table, * FROM EXT00100
/* EXT00101*/ SELECT 'EXT00101' as Extender_Table, * FROM EXT00101
/* EXT00102*/ SELECT 'EXT00102' as Extender_Table, * FROM EXT00102
/* EXT00103*/ SELECT 'EXT00103' as Extender_Table, * FROM EXT00103
/* EXT00104*/ SELECT 'EXT00104' as Extender_Table, * FROM EXT00104
/* EXT00150*/ SELECT 'EXT00150' as Extender_Table, * FROM EXT00150
/* EXT00151*/ SELECT 'EXT00151' as Extender_Table, * FROM EXT00151
/* EXT00180*/ SELECT 'EXT00180' as Extender_Table, * FROM EXT00180
/* EXT00181*/ SELECT 'EXT00181' as Extender_Table, * FROM EXT00181
/* EXT00182*/ SELECT 'EXT00182' as Extender_Table, * FROM EXT00182
/* EXT00183*/ SELECT 'EXT00183' as Extender_Table, * FROM EXT00183
/* EXT00184*/ SELECT 'EXT00184' as Extender_Table, * FROM EXT00184
/* EXT00185*/ SELECT 'EXT00185' as Extender_Table, * FROM EXT00185
/* EXT00900*/ SELECT 'EXT00900' as Extender_Table, * FROM EXT00900
/* EXT10200*/ SELECT 'EXT10200' as Extender_Table, * FROM EXT10200
/* EXT30200*/ SELECT 'EXT30200' as Extender_Table, * FROM EXT30200
/* EXT40010*/ SELECT 'EXT40010' as Extender_Table, * FROM EXT40010
/* EXT40011*/ SELECT 'EXT40011' as Extender_Table, * FROM EXT40011
/* EXT40012*/ SELECT 'EXT40012' as Extender_Table, * FROM EXT40012
/* EXT40013*/ SELECT 'EXT40013' as Extender_Table, * FROM EXT40013
/* EXT40040*/ SELECT 'EXT40040' as Extender_Table, * FROM EXT40040
/* EXT40100*/ SELECT 'EXT40100' as Extender_Table, * FROM EXT40100
/* EXT40101*/ SELECT 'EXT40101' as Extender_Table, * FROM EXT40101
/* EXT40102*/ SELECT 'EXT40102' as Extender_Table, * FROM EXT40102
/* EXT40103*/ SELECT 'EXT40103' as Extender_Table, * FROM EXT40103
/* EXT40104*/ SELECT 'EXT40104' as Extender_Table, * FROM EXT40104
/* EXT40105*/ SELECT 'EXT40105' as Extender_Table, * FROM EXT40105
/* EXT40106*/ SELECT 'EXT40106' as Extender_Table, * FROM EXT40106
/* EXT40107*/ SELECT 'EXT40107' as Extender_Table, * FROM EXT40107
/* EXT40108*/ SELECT 'EXT40108' as Extender_Table, * FROM EXT40108
/* EXT40109*/ SELECT 'EXT40109' as Extender_Table, * FROM EXT40109
/* EXT40110*/ SELECT 'EXT40110' as Extender_Table, * FROM EXT40110
/* EXT40111*/ SELECT 'EXT40111' as Extender_Table, * FROM EXT40111
/* EXT40117*/ SELECT 'EXT40117' as Extender_Table, * FROM EXT40117
/* EXT40150*/ SELECT 'EXT40150' as Extender_Table, * FROM EXT40150
/* EXT40151*/ SELECT 'EXT40151' as Extender_Table, * FROM EXT40151
/* EXT40152*/ SELECT 'EXT40152' as Extender_Table, * FROM EXT40152
/* EXT40153*/ SELECT 'EXT40153' as Extender_Table, * FROM EXT40153
/* EXT40154*/ SELECT 'EXT40154' as Extender_Table, * FROM EXT40154
/* EXT40157*/ SELECT 'EXT40157' as Extender_Table, * FROM EXT40157
/* EXT40160*/ SELECT 'EXT40160' as Extender_Table, * FROM EXT40160
/* EXT40162*/ SELECT 'EXT40162' as Extender_Table, * FROM EXT40162
/* EXT40163*/ SELECT 'EXT40163' as Extender_Table, * FROM EXT40163
/* EXT40164*/ SELECT 'EXT40164' as Extender_Table, * FROM EXT40164
/* EXT40170*/ SELECT 'EXT40170' as Extender_Table, * FROM EXT40170
/* EXT40200*/ SELECT 'EXT40200' as Extender_Table, * FROM EXT40200
/* EXT40201*/ SELECT 'EXT40201' as Extender_Table, * FROM EXT40201
/* EXT40400*/ SELECT 'EXT40400' as Extender_Table, * FROM EXT40400
/* EXT40600*/ SELECT 'EXT40600' as Extender_Table, * FROM EXT40600
/* EXT40601*/ SELECT 'EXT40601' as Extender_Table, * FROM EXT40601
/* EXT40602*/ SELECT 'EXT40602' as Extender_Table, * FROM EXT40602
/* EXT40700*/ SELECT 'EXT40700' as Extender_Table, * FROM EXT40700
/* EXT40701*/ SELECT 'EXT40701' as Extender_Table, * FROM EXT40701
/* EXT40702*/ SELECT 'EXT40702' as Extender_Table, * FROM EXT40702
/* EXT40703*/ SELECT 'EXT40703' as Extender_Table, * FROM EXT40703
/* EXT40900*/ SELECT 'EXT40900' as Extender_Table, * FROM EXT40900
/* EXT40901*/ SELECT 'EXT40901' as Extender_Table, * FROM EXT40901
/* EXT43200*/ SELECT 'EXT43200' as Extender_Table, * FROM EXT43200
/* EXT43201*/ SELECT 'EXT43201' as Extender_Table, * FROM EXT43201
/* EXT43202*/ SELECT 'EXT43202' as Extender_Table, * FROM EXT43202
/* EXT43204*/ SELECT 'EXT43204' as Extender_Table, * FROM EXT43204
/* EXT43205*/ SELECT 'EXT43205' as Extender_Table, * FROM EXT43205
/* EXT43400*/ SELECT 'EXT43400' as Extender_Table, * FROM EXT43400
/* EXT43900*/ SELECT 'EXT43900' as Extender_Table, * FROM EXT43900
/* EXT43901*/ SELECT 'EXT43901' as Extender_Table, * FROM EXT43901
/* EXT43902*/ SELECT 'EXT43902' as Extender_Table, * FROM EXT43902
/* EXT44100*/ SELECT 'EXT44100' as Extender_Table, * FROM EXT44100
/* EXT44200*/ SELECT 'EXT44200' as Extender_Table, * FROM EXT44200
/* EXT44300*/ SELECT 'EXT44300' as Extender_Table, * FROM EXT44300
/* EXT45000*/ SELECT 'EXT45000' as Extender_Table, * FROM EXT45000
/* EXT50100*/ SELECT 'EXT50100' as Extender_Table, * FROM EXT50100
/* EXT60100*/ SELECT 'EXT60100' as Extender_Table, * FROM EXT60100

Now, you can now copy and paste the above results in a new query window and once again execute against your company database. Now you should be able to see how the data is stored. What I appreciate about this query (and not because I wrote it 🙂 ) is the fact that it displays the table name as part of the result set. This allows you to know directly where a row came from.

If your goal is to produce a SQL Server view that will link this data, then check David Musgrave‘s article Creating SQL Views of Extender Data for more information. However, if you are seeking to do this via Extender itself, then you need to check MVP Victoria Yudin‘s post on Creating Extender Views.

Until next post!

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