Show Hide

 

Feedback

  • Contents
 

Create an Export File Layout Using a Stored Procedure

Use the Export Files tab to create an export file layout using a stored procedure that exists in your Latitude database.  

To create an export file layout using a stored procedure

  1. In the Navigation pane, click Clients.

Clients pane

  1. In the Clients pane, expand the appropriate folder and then double-click the client. The tab for the specified client appears in the Information pane.

General tab

  1. Click the Export Files tab.

Export Files tab

  1. In the toolbar, click the Add icon. The New Export Name dialog box appears.

New Export Name dialog box

  1. In the Name box, type a descriptive name for the export.

  2. For the export type, click Standard and then click OK. The system adds the export as a tab on the Export Files tab.

Export Files tab - new export

Name: Name of the export.

Output File: Name to assign to the export file. You can name the file using the following methods:

    • Use a literal name, such as "PaymentFile".

    • Include dynamic information, such as a date and time stamp. For example, "PAYMENTFILE_@Date(MMddyyyy_hhmmss).txt," names the export file as "PAYMENTFILE," followed by the current date and time in the format indicated.

    • Include an aggregate value from the resultant dataset. For example, "Payment_@Value(sum(Amount)|Batchtype = ‘PU’|0)_PAYMENT.txt," names the export file as "PAYMENT_[sum of the payments in the “Amount” column, where the type of payment is "PU," which is in the “Batchtype” column]_PAYMENT.txt. This aggregate value function takes three parameters @Value(expression|filter|tableIndex) separated by the | (pipe) character. TableIndex is the name of the export you defined.

Description: Description of the export file.

Parameters: Parameters to use to pass values to the stored procedure. If you export data using Exchange Manager, Exchange Manager requests the values from the user. If you export data using Job Manager and you predefine the parameters, Exchange Manager uses the predefined values.

Stored Procedure: Name of the stored procedure to use to retrieve the data to export.

Note: If you use a stored procedure for post export processing (Stored Procedure Post Export), include the parameters that the post export stored procedure uses in this stored procedure.    

Excel: If selected, exports the data to a Microsoft Excel file.

Fixed: If selected, exports the data to a fixed-length flat file.

CSV: If selected, exports the data to a Comma-Separated Value file.

Transformation Details: Allows you to specify the XSLT translation to transform XML into other formats such as HTML, plain text, and XLSX.

Stored Procedure Post Export: Stored procedure to use for post-export processing (for example, flagging payments or changing account statuses).

Note: Ensure that this stored procedure uses the same account selection criteria as the stored procedure for exporting the data.

Pre-define Parameters: Opens a dialog box to allow you to specify the values to use when exporting data using Job Manager instead of requesting the values from the user.

  1. Complete the information.

  2. To add a parameter to request data from a user and pass it to the stored procedure, do the following:

    1. In the data grid, click in the Name box in the first blank row and type the parameter name that the stored procedure uses.

    2. In the Type box, click a data type and then, in the Name box, click the Edit icon.

    1. In the Prompt box, type a prompt to display to request input from the user when running the export.

  1. To transform XML into another format, do the following:

    1. Click Transformation Details and then do one of the following:

      • To open the file that contains the translation code, click From File..., click the name of the file, and then click Open.

      • To type the translation code, click New...

The Edit File window appears.

    1. Modify the code as necessary and then click OK.

Note: When exporting to CSV, provide the transformation details using the following example. "ReportData" is a reference to your data set. The "xsl:value-of select" tags specify the exact field names your stored procedure provides as your result set. The "xsl:text" tags are your delimiters. Notice that sometimes a space is used to concatenate strings. Ensure that you specify "<xsl:text>&#13;&#10;</xsl:text>" at the end as that is your carriage return line return at the end of each row.

Edit File dialog box

  1. To specify the values to use when exporting data using Job Manager, do the following:

    1. In the data grid, click the parameter row to predefine.

Export Files tab - predefine parameters

    1. Click Pre-define Parameters. The Parameters dialog box appears.

Parameters dialog box

    1. Specify the values to use to retrieve the data to export and then click OK.

    2. Repeat these steps for each parameter in the data grid that you want to predefine.

  1. In the toolbar, click Save. The Update Comment dialog box appears.

  2. In the Update Comment box, type a comment regarding your changes and then click OK.

Related Topics

Specify a String Parameter

Specify a Date and Time Parameter

Specify an Integer Parameter

Specify a Query Parameter

Export Files