Archives

Exporting Memo Fields to Excel. Received Goods (RPREGO)

One of my users came to me with a problem last week, and it’s fairly common. They run a M2M Report that has the data they want, but when they export it, some of the data is missing.

This can happen for a couple of reasons. Tables can be added to a report layout and those changes are never made to the report program file (prg). If a field isn’t listed in the prg file, it cannot be exported.

The user came to me and asked why the part description was not showing up in Received Goods Report (RPREGO). When I looked at the prg file, I saw the field listed.


LCSQL = 'SELECT rcmast.fReceiver AS freceiver_a, rcmast.fcompany';
      + ', rcmast.fpono, rcmast.ftype AS ftype_a, rcmast.fdaterecv';
      + ', rcitem.fitemno, rcitem.fac,rcitem.fpartno, rcitem.fpartrev, rcitem.fcudrev';
      + ', (case when inmast.fluseudrev IS NOT NULL AND inmast.fluseudrev = 1 then  rcitem.fcudrev  else rcitem.fpartrev end) AS fcdisprev' + ;
      + ', rcitem.fdescript';
      + ', rcitem.fqtyrecv, rcitem.fcategory, rcitem.fmeasure, rcitem.fsokey';

So, if rcitem.fdescript is in the program file, why doesn’t it export? Well, according to M2M it’s a Visual Foxpro limitation and memo fields cannot be exported.

How do we fix this? Well, my preferred method would be to extract the SQL statement from the report and deliver it via Crystal Reports or SQL Reporting Services, neither of which have this limitation. For reference, the SQL statement is below:


SELECT RCM.fReceiver AS [Recv No],
                       RCM.fcompany AS [Vendor],
                       RCM.fpono AS [Our PO No],
                       RCM.ftype AS [Recv Type],
                       RCM.fdaterecv AS [Recv Date],
                       RCI.fitemno AS [RCI No],
                       RCI.fac AS [Facility],
                       RCI.fpartno AS [Part No],
                       RCI.fpartrev AS [Part Rev],
                       RCI.fcudrev,
                       (CASE
                           WHEN INM.fluseudrev IS NOT NULL AND INM.fluseudrev = 1
                           THEN
                              RCI.fcudrev
                           ELSE
                              RCI.fpartrev
                        END)
                          AS fcdisprev,
                       RCI.fdescript AS [Description],
                       RCI.fqtyrecv AS [Qty Recvd],
                       RCI.fcategory AS [Category],
                       RCI.fmeasure AS [U/M],
                       RCI.fsokey AS [SO No],
                       RCI.fsoitem AS [SOI No],
                       RCI.fsorelsno AS [SOR No],
                       RCI.fjokey AS [JO No]
                  FROM dbo.rcmast RCM
                       JOIN dbo.rcitem RCI
                       ON RCI.freceiver = RCM.freceiver
                       LEFT OUTER JOIN dbo.inmast INM
                       ON INM.fac + INM.fpartno + INM.frev =
                             RCI.fac + RCI.fpartno + RCI.fpartrev

However, this user really wanted to be able to use the Received Goods Report from directly within Made2Manage. This too can be accommodated by editing the program file with the following:


cast(rcitem.fdescript as varchar(20))

This code simply changes the field type to a character field and those will export to Excel. Keep in mind that the field is only 20 characters in my example so you will need to change the number based on how many characters you need.

4 comments to Exporting Memo Fields to Excel. Received Goods (RPREGO)

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>