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.
Thanks for the tip. It may come in handy. Perhaps M2M will read your post and upgrade thier code. Somehow I doubt it though.
Maybe a stupid question, but what’s with the RCI and RCM stuff?
Well, those are called aliases. There are actually 2 kinds of aliases shown there. I am aliasing the field names in brackets and I am aliasing the table names as well.
The field names make the output look better as the user will see “Description” instead of “fdescript.” The table name aliasing makes the query easier to read and understand.
[...] I mentioned in a previous post, if you are creating a VFP report for M2M only the fields called out in your prg file will export [...]