In this post, I’ll explain an easy way to create an exportable Excel file using ER. Typically, I use Excel for exporting tables and similar files that need to be opened directly in Excel. For business documents like invoices and orders, I generally prefer Word, though Excel can also be used for these documents, as in this example, with the final file easily converted to PDF if needed.
In this tutorial, we’ll create a simple Packing Slip in Excel that includes a header, a logo, and item lines. I’ll be using an extension of the standard waybill model for this example. I’ll add color to different parts—like the header, lines, and fields—for clarity, though this isn’t something I’d typically do in a professional Packing Slip.
Excel Template
Let’s start by creating an Excel template with a custom header, a repeating top section instead of the default header.
Next, I’ll create named cells for the Company Name and Telephone.
For the logo, we’ll insert a placeholder image and add a label as well.
Next, I’ll create the column headers with three named columns: LabelItemId, LabelItemNumber, and LabelQuantity.
Then, I’ll create the repeating lines, selecting multiple cells and naming them Lines.
Finally, inside the Lines, I’ll add three named cells: ItemId, ItemNumber, and Quantity.
Format
In Electronic Reporting configurations, let’s create a new Excel format. In this case, I’ll use an extension of the default waybill model, but you can create it under the default model if you’re not using an extension pattern.
Now, inside Designer, we can import our template.
Our format nodes will be created automatically; now we just need to link them.
For the line headers, it’s a good idea to use labels. Since we’re using the Waybill model, I was able to select existing labels.
Also, let’s make sure the lines are set to Vertical.
Finally, let’s name the file so it can be used in ER destinations. If the file name is left blank, it will fail.
We can always modify and update our original template:
Integration as print management document
The format is now done. Since this is a print management document, we can’t test it directly from the ER Designer, so we’ll need to test it functionally. Before that, on the previous screen, let’s add a packing slip tag so it can be used as a business document.
Let’s go ahead and complete the format.
Packing Slips are configured under Accounts receivable > Setup > Forms > Form Setup and then in General > Print Management.
Now we can test it in the Packing Slip Journal.
If we want it in PDF format, we can set it up through Electronic Reporting Destinations.
The result might not be perfect on the first try. Some adjustments in Excel and in the document layout settings (within ER destinations) may be necessary, but the hard work is done!
Leave a Reply