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/

Comments

Anonymous said…
Just got a solution working in a similar situation where we had 250 characters in a field, where we needed that to be printing on several reports. I've used a SQL trigger to split a string into 4 different strings of 80 characters. On every update, I've created another window which has 4 fields and when someone updates the main string, I have it updating the other 4 string fields on a different extender window. With this, we can easily pull data in any report. I parse the string in the reverse order where last space is found.
May said…
Hello,
I had been going through the post on your blog on parsing long Extender fields. I have an Extender field that I am using VBA to bring into the Report Footer. The field is a long string and I wanted to print it in full in the report.

I had created a calculated field called as StmtText1, with type Constant String with value "". I used VBA to move the value to this field.

But, now if I want to add the RW_PARSESTRING function, do I add that function to the same field, i.e. StmtText1 or I have to create another calculated field to Parse StmtText1?

I am confuse because the 1st parameter for RW_ParseString is the field name. Is it possible to have the same field name as the calculated field I am defining?

And for the 2nd parameter, do I put in 255 as the extender field can hold up to 255 characters?

What about the 3rd parameter?

Your kind advise is greatly appreciated.

May
Mariano Gomez said…
May,
You will need a calculated field for each segment parsed. Unfortunately, there's no easy way to this. So, if your main string is parsed into 4 strings, then you will need 4 calculated fields.

MG.-
Mariano Gomez, MVP
Unknown said…
If folks are starting out and know that they will face this they can choose to use muliple Short String fields in Extender, rather than one Long String field, and then concatenate them in RW. The biggest problem is that Short String is limited to 30 characters.

Mark
Mariano Gomez said…
@Mark:

Certainly another valid option. It just sucks that something this trivial needs a workaround to begin with, because of product limitations.

MG.-
Anonymous said…
Hola Mariano, precisamente estoy luchando con esto. debo agregar un texto almacenado en una tabla extender al reporte de orden de compra.
Np logro usar la función que indicas en tu blog. por mi parte yo he creado una función escalar que devuelve cadena de 3 caracteres, tenia la esperanza de poder verla desde el editor de reportes.
esta es la función que cree, podrías ayudarme con esto? el ambiente es GP2018

CREATE FUNCTION getTipoOC (@OCNumber as varchar(15))
RETURNS VARCHAR(3)
BEGIN
DECLARE @TIPO AS VARCHAR(3)
SELECT @TIPO = RTRIM(C.STRNG132) FROM TSTST.DBO.EXT01100 A INNER JOIN TSTST.DBO.EXT01103 B
ON A.Extender_RECORD_ID = B.Extender_RECORD_ID
INNER JOIN TSTST.DBO.EXT20021 C ON B.TOTAL = C.LNITMSEQ
WHERE A.Extender_WINDOW_ID = 'OP_COMPRAS'
AND C.EXTENDER_LIST_ID = 29
AND A.Extender_key_Values_1 = @OCNumber
RETURN @TIPO
END
Mariano Gomez said…
Las funciones escalares de SQL no se muestran en Report Writer, pero puedes usar VBA para invocar la funcion.

Popular posts from this blog

DBMS: 12 Microsoft Dynamics GP: 0 error when updating to Microsoft Dynamics GP 2013 R2

Cannot insert the value NULL into column 'CONTACT' error when clicking on Items List in Navigation Pane

#PowerApps: Numeric Up/Down control with persisted button press event using components