Does anyone know of a way to combine all the detail values for a grouping in a single field on an export scenario or generic inquiry?
For a shipment export, the 3rd party software requires a single line for each shipment, with details about each individual package sent in the column & row, but separated by a ‘|’ character. So, if the shipment included 3 boxes, our “Shipping Weight” column might show “5.0|2.5|10.0” to export each value.
Best answer by lauraj46
View original
+7
Hi
One thought would be to use variables in the Report Designer to build these outputs. The idea would be to group by shipment number, concatenate the necessary values in the detail section, and output the results in the group footer. You can also export reports to Excel.
Hope this helps!
Laura
Another easy way would be to do a SQL View, and then expose that view using it’s own DAC. You will then be able to use this DAC in a GI. It’s relatively quick & easy to setup.
e.g. You could do a view with the following SQL (Sorry, we use MySQL)
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `SOPackageDetailCombined`
AS
SELECT
`CompanyID`,
`ShipmentNbr`,
GROUP_CONCAT(`BoxID` SEPARATOR '|') AS 'BoxIDs',
GROUP_CONCAT(`Weight` SEPARATOR '|') AS 'BoxWeights'
FROM `SOPackageDetail`
GROUP BY `SOPackageDetail`.`CompanyID`, `SOPackageDetail`.`ShipmentNbr`
;
And then follow these steps to get the DAC going for this newly created view, so that you can then finally use it in a GI.
Bonus points, since the view is exposed as a DAC, you can also use this GI as source for an export scenario.
I hope this helps.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.