LWEXPORT – Include data from AUSERFLDS

Issue: Using the LWExport application, when selecting the fields to be included, the AUSERFLD 2 needs to be included. The field identity used is Analysis special info field name 02. The field is added to the export file but the data stored in the field does appear in the export file. The sample number in the database table with the correct data is displayed.

OBJID OBJTYPE HSEC GL_CODE ACCOUNTING_ID TYPE AA00350_|_PHOS-MGL-WW SAMPLE 0 30100 10940 EN

Is this the correct field? Will any other field be exported?

Solution: This workaround requires using a direct access report. Run this query on the desired database:

---Create a splitting function, to split SAMPNO from ACODE.---
create function SplitAUSERFLDS (@string varchar(max), @delimeter char(1)) 
returns table as return
    with a as (
        select
            id = 1,
            len_string = len(@string) + 1,
            b = 1,
            e = coalesce(nullif(charindex(@delimeter, @string, 1), 0), len(@string) + 1),
            item = ltrim(rtrim(substring(@string, 1, coalesce(nullif(charindex(@delimeter, @string, 1), 0), len(@string) + 1)-1)))
        union all
        select
            id + 1,
            len(@string) + 1,
            convert(int, e) + 1,
            coalesce(nullif(charindex(@delimeter, @string, e + 1), 0), len_string), 
            ltrim(rtrim(substring(@string, e + 1, coalesce(nullif(charindex(@delimeter, @string, e + 1), 0), len_string)-e-1)))
        from a where e < len_string)
    select id, item from a 
go

---Create a view with the splited SAMPNO and ACODE, plus all the AUSERFLDS properties---
CREATE VIEW vSplitAUSERFLDS 
as
SELECT  
CAST((select left(item, charindex('|', item) - 2) as SAMPNO from dbo.SplitAUSERFLDS(a.OBJID, '')) as varchar(7)) as SAMPNO, 
CAST((select right(item, charindex('|', reverse(item)) - 2) as ANAM  from dbo.SplitAUSERFLDS(a.OBJID, '')) as varchar(24)) as ACODE,
a.*
FROM AUSERFLDS as a  
WHERE        (OBJTYPE = 'SAMPLE')
Go

Then try running the attached Crystal report using vSplitAUSERFLDS instead of vSplitAUSERFLDS.

You should now be able to export data correctly.