Lookup in Microsoft® Excel® Documents
The Lookup in Microsoft® Excel® Documents action task is used to complement your job file's metadata by retrieving data from a Microsoft® Excel® spreadsheet on your system. The data retrieved is based on existing data in your metadata, and it will either be added to your metadata or will append or replace your existing metadata if it exists.
Fields on any level (Page, Datapage, Document, Group, Job) can be used, and the result field will be added on the same level as the lookup field.
This task will automatically "loop" through the metadata and repeat its action for each of your metadata's datapages. This task should not be placed after a Metadata Sequencer task, otherwise it will run as many times as there are metadata sequences, which will result in decreased performance.
Input
Any compatible data file, requires metadata to be present.
Processing
The task parses each level of the metadata and, for each field of the specified name it finds, a lookup is made. If a field of the same name appears on multiple levels, the lookup will happen for all fields, on all levels, individually.
Output
The original data file is unchanged. Metadata is updated according to the specified criteria.
Task Properties
General Tab
- Excel group
- Excel workbook: The full path and filename of a Microsoft® Excel® workbook (.xls or .xslx file). You can use the Browse button on the right to browse to the file on your computer.
- Excel worksheet: The name of the worksheet you want to use. Once a workbook is open, this drop-down will automatically list all the available worksheets.
- Refresh button: If you have modified the original Microsoft® Excel® workbook to add a sheet, click this button to refresh the list of worksheets.
- Metadata group
- Lookup Field: The name of the metadata field that will be used to determine which row should be returned. The Metadata field can be on any level.
- Lookup Column: The name of the column in the Microsoft® Excel® worksheet that corresponds to the contents of the Lookup Field.
- Action: What to do with the resulting data from the Microsoft® Excel® worksheet. This can be:
- Add Field: Creates a new field with the data. This may cause multiple fields to be created.
- Replace field value: Replaces any existing field with the new content. Only the last result will be displayed. If the field does not exist, it will create it.
- Append field value: Ads the data to the existing field within the same one. No "separator" is added. If the field does not exist, it will create it.
- Result Field: The metadata field name in which the result should be stored. This field will appear in the same metadata level as the Lookup Field.
- Result Column: The name of the column where the information you want to retrieve is located. For example, this could be a client email or full name.
Button: Ads a new lookup line. You can have as many lines as you want. The lines will be executed in order from top to bottom, so you can rely on a previous line to bring additional information.
Button: Removes the currently selected (highlighted) line.
Button: Moves the currently selected line up one place.
Button: Moves the currently selected line down one place.
- Search option group
- Match case: Will force the lookup column names to be in exactly the same case as the Lookup column name. This means if you type in "CustomerID" in the lookup column and the actual column is named "customerid", it would not return any result.
On Error Tab
By default, any action task, branch, splitter or condition that generates an error will simply be ignored, and the task just under it (not within a branch) will be given control of the job file without any modification. Any initial input task that generates an error will stop the process from running as a whole, and output tasks will not generate output. The On Error tab can be used to overwrite the default behaviors.
- Send to Process: Check this option to send the job file to an error management process.
- Error Process drop-down: Enabled only when the Send to Process option is checked. Lists any process of which the initial input task is the Input Error Bin task.
- Action Group: This group is disabled in the initial input tasks and defaults to Stop Process. In all other tasks where the On Error tab is present, the following options are available:
- Ignore: The task is ignored as if it did not exist, and the job file is passed on to the next task in the process.
- Stop Branch: If the task is in a branch of the process, the branch is stopped and the job file is returned to the process after the branch. The branch will not produce any output.
- Stop Process: The process is stopped and no more processing is done. No further output is produced.
- Log Message: Check this option to enable logging a custom error message in the PlanetPress Suite Workflow Tools' log file.
- Message: Enabled only when the Log Message option is checked. Enter a message that will be logged in the PlanetPress Suite Workflow Tools' log file. You can use any variables available in PlanetPress Workflow to customize the message.
- Store the message in variable: Select in which jobinfo, local or global variable you want to store the message content.
- ID: Enter an error ID. This ID will be visible in the Windows Event Viewer. However, the ID is not visible in the PlanetPress Suite Workflow Tools' log file.
- Store the ID in variable: Select in which jobinfo, local or global variable you want to store the error ID.
- Reset to defaults: Resets all options in this tab to their default values.
If storing the message or ID, if they are store in a jobinfo they will be available in any error handling process where errors are being forwarded. In all cases, if your process continues after the error, the contents of the variables selected in this window will be available for the rest of your process, or whenever they are overwritten.
Common Errors
Though some error messages are specific to a task in particular, others may apply to any and all tasks because they are related more to the system than to PlanetPress itself. Some examples would be W3813, W3830, W3991, W4005. These correspond to issues such as not having any space to write files, permission errors on folders or files, etc.
Comments Tab
The Comments tab, added in PlanetPress Suite 7.5, is common to all tasks. It contains a single text area (Task comments) that lets you write comments about the task. These comments are saved when the dialog is closed with the OK button, and are displayed in The Task Comments Pane.
Use Cases
Use Case 1: Send Personalized Emails with Promotional Document Attached
A PlanetPress Design document takes as input a PDF file as the input data file, and reproduces it exactly as it enters. The document also contains a custom data selection set to hold an email address. The data selection's value is given by a Metadata Field called 'Email'. The value of this Email metadata field is a region from the sample data file representing the customer number. At production time, the Lookup in Microsoft® Excel® Documents Action task will replace the value of this metadata field with the corresponding customer email.
Use Case 2: Translate a list of line items descriptions into a given language
A PlanetPress Design document takes as input a transactional PDF file, and reproduces it exactly as it enters. Metadata fields called ItemDesc are created, one for each line item description, at the datapage level. Each ItemDesc metadata field is given the value of a line item description as found on a region of the current data page. The line item descriptions appearing on the resulting page produced by the design tool are custom data selections whose value come from the corresponding ItemDesc metadata fields. The Lookup in Microsoft® Excel® Documents Action task updates the value of all 'ItemDesc' metadata fields with their corresponding foreign language descriptions.