Create export function with a predefined Excel template

VXL can be used to export data from M3 to Excel using a predefined Excel template. This way you can fill the Excel sheet exactly the way you want.

In this example we will create a function to export a purchase agreement of choice and list all agreement lines. The Excel template looks like this.

We will use the configurator in the web application to create the function. Click "+Configurator" on the "Functions" page to create a new function.

Give the function a name and optional description. The configurator contains a search engine for finding APIs. Select an appropriate environment for API metadata. If no environment contains API metadata, this can be uploaded from the desktop client. Click next when you're finished.

Browse your local filesystem and select the Excel template file. Input header row and data start row. Header row is used to fetch header names for the configurator. Data start row is used by the VXL engine to start writing data. Since we are listing lines from row 5, this will be entered as start row. Start and end column are the data area. Values are suggested based on the template.

Select an API program or transaction from the list or use advanced search. Select max number of rows returned from the API. Optionally sort the api output.

Select input values. This can be either a constant value or a input field in the VXL desktop client user interface. In this example we will use input from the user. The labels in the right columns can be changed.

To get the supplier name we need to run a get API. Click "Add new API transaction" to connect to a get API.

Select CRS620Mi.GetBasicData from the list or use advanced API search to find the API you want

Select input values to the API. This can be values from a previously run API, a input field or a constant. If an input value matches the output value of the list API, it will be matched automatically.

Map the "Supplier name" column to the SUNM field of the get API. Click save

The remaining columns will be matched with output fields from the first list API. Header names that matches the API field name or description is matched automatically. Leave the sum column blank, since it will be filled automatically based on the template.

Optionally add extra filtering. Click here for details.

When clicking next, you get the option of adding another task. A function can contain both import and export task. I.e. a function can export some date, the user adds some details and re-imports the data using the same function. In this example we will only use one export task. Click "No".

Input which roles can access the function. Read or write access to the respective environment depends on the roles access in the environment. Click here for details

Select which environments this version of the function will be deployed to. Click here for details.

Save the function and start VXL desktop client. Double click the function to run it.

This user interface is based on a template and can be edited. Input supplier and agreement number. Start the export by clicking "Purchase agreement - Export".

Log on to M3 with your M3 user. Note that this is NOT the same user as used for logging in to VXL web and desktop.

VXL runs the APIs and creates the Excel workbook. It will open automatically