Lookup Extender

The Lookup Extender allows you to improve and customize any searches you plan to run on the various SQL databases connecting to your eCopy ShareScan Manager. You can use any connected MFP to run SQL queries on the servers connected to the particular Manager the device is attached to. The extender also allows you to get input data by selecting from options coming from a database or CSV file, and to enter data manually. In general, the purpose of the extender is to enable the configuration of complex business automation workflows, in most cases in conjunction with the usage of extenders (typically, eCopy Forms Processing Extender, or similar) and document services (for example, Image Control).

To be able to use this extender with the Email and folder watching service, you need to configure it in such a way that it does not involve end-user interaction (express). To ensure this, clear the Show Validation Screens check box in the configuration page.

If ShareScan has a previously configured database then the Recent data source window appears when you click New.

You can select a previously configured database and accept it with the OK button (only available when a source is selected). Clicking the New button closes the Recent data sources window and the Configure data source window appears. Click Remove to delete a source form the list (it will be deleted only from Recent data sources).

Access this window by clicking New on the Databases tab, and selecting the New option on the Recent data sources screen, if there is no previously created database connection. You can set the following options:

• Database type: use the menu to select your database.
  Since Microsoft deprecated the Microsoft Jet 4.0 OLE DB Provider (https://msdn.microsoft.com/en-us/library/ms810810.aspx), and future Windows update may remove it from the system. Configuring a 'Microsoft Excel 97-2003' data source on machines without a Microsoft Jet 4.0 OLE DB Provider results in an error message. In case you still wish to use the old format .xls files, install 32 bit version of Microsoft Access Database Engine 2016 Redistributable (https://www.microsoft.com/en-us/download/details.aspx?id=54920) or later and configure Database type for Microsoft Excel 97-2003 in the Configure data source dialog by selecting old format .xls files via setting the filter to All files (*.*) on the Windows File Open dialog.

• Settings: Depending on the selected database, the specific setting options vary.
  • Microsoft Access, Microsoft Excel 97-2003, Microsoft Excel, Comma Separated Text (CSV) (the following settings are common for these database types, with only the displayed pathname changing):
    • Path to <database type> database: Type the folder path or click the ... (browse) button to navigate to the location of the database file.
    • Blank username and password: Select this option if you do not want to enter a specific set of credentials for accessing the database. If the option is checked, the user is prompted to enter credentials to access the database (usually this is not the preferred way of setting up the access, as administrators do not want to put the burden of an additional authentication on the user).
  • Microsoft SQL:
    • SQL Server: Use the menu to select the available SQL database instance.
    • Catalog/Database: Use the menu to select the catalog you want to use.
  • Other:
    • Data Source Connection String: Enter the connection string of your database here.
    • Build String: Click this button to open the Data Link Properties dialog and put together a database-specific connection string.
    • Click through the options of this short Wizard to put together a connection string for your database:
      • On the Provider tab, select your OLE DB provider and click Next.
      • On the Connection tab (default view), enter the data source and login credentials into the appropriate fields, and click Test Connection to check the data.
      • On the Advanced tab, you can set the relevant network settings, connection timeout and access permissions to the database.
      • On the All tab, you can check all the data link properties once more, and if so inclined, you can edit them here manually, by simply clicking the row you want to edit and filling out the Property Value.
      • Click OK to finish setting up the data link properties.
• Test: After specifying the connection settings, you must click Test to check if the connection works. A notification is shown about the actual state: Not yet tested, Test failed or Test succeeded. If the connection test is successful, you can click OK to create the database connection.

Make database file password protected

To make a database file password protected, you have to configure Data Link Properties the same way as described above; the only difference in setup is on the All tab. Select the Database Password row and double-click on it or click the Edit Value… button.

The Edit Property Value window pops up. Enter the password in the Property Value field and click OK to confirm the password. Click Reset Value to delete the password and provide a new one.

When you are done, go back to the Connection tab and click the Test connection button. Click OK to save your settings.

The Lookup Editor window has four main sections, allowing you to create and modify queries.

1. "Naming section"
   • Lookup result key: Enter the name of the data publishing key you want to use for finding the results. This key will be the output of the lookup operation. If left empty and the rest of the dialog is filled, this field is populated automatically, based on the values of different fields; it can be edited any time if the dialog populates it automatically.
   • Display label: Enter a label of the text field that is displayed on the MFP UI for the specific field. If left blank, the field is populated automatically based on the value of the Lookup result key field.
2. "Lookup type section"
   • Lookup type: Allows you to select any of three lookup query types to be used; each type features different settings (see step 3 below).
   • Lookup evaluation condition:
   • When the always evaluated option is selected, the lookup specified is always performed.
   • If the Evaluated if condition is fulfilled option is selected, the specified condition is evaluated – a selected data publishing key is checked if it is not blank, equal to a specified value, or matching a regular expression; the lookup operation is performed only when the result is true.
3. "Lookup section"
   • Database lookup: Select it to use the simple, user friendly editor to set up the lookup criteria.
   • Custom SQL query: You can use complex SQL queries (specified in a text editor window):
     • Custom SQL query – With skeleton You can get a SQL “skeleton” if you first select Database lookup and provide some relevant data for the empty fields and then switch Lookup type to Custom SQL query. Once you switch over, the custom SQL query field is populated with the data you entered previously for the Database lookup. This makes it easier to edit any custom SQL query.
   • Expression: Allows you to specify C# expressions, ensuring full flexibility in comparison and working logic.
     • Expression templates: Use the menu to select specific C# expression templates. When selected, you can click the pencil icon to copy the expression into the editor window. Clicking the msdn button opens a browser and displays the expression-specific MSDN article (if you have an active Internet connection).
4. "Behavior section"
5. Lookup behavior: Use this menu to set the number of results the query should consider. Select One value only if you are looking for a specific database entry; otherwise, select Multiple values or not required.
   • When One value only is specified, and the result of the lookup was a single value, the UI interaction is initiated (at the MFP). If the lookup was not successful (value not found), the user has to enter a value into a text box at the MFP. If there are multiple values returned by the lookup, the user is prompted to select from the available values.
   • When the Multiple values or not required option is selected and the result of the lookup is a single value or no matching item is found, that single value or an empty value is used. If there are multiple values in the result, all are included in the result data publishing key, separated by a separator character. The separator character can be set on the Settings tab of the Configure Document Service (Lookup Extender).
   • Always evaluated: Controls how empty values affect lookup evaluation; checked: lookup is always evaluated even if there are empty variables; unchecked (default): lookup is evaluated only when all variables are set.
   • Visible: Controls query visibility.
   • User modify: Controls whether users can modify lookup values of the query.
   • Preserve value: If checked, selected lookup values are populated for the next query; if unchecked, new values must be provided for the next query.

Test: Click this button to test your query.

Lookup Test Dialog

Before you can finalize your lookup, you have to test your settings by clicking the Test button. The Lookup test dialog appears.

Enter a string in the Value field and click Execute. The result is displayed and the OK button becomes active.

After a test is executed, the bottom information line displays the query execution details.

After test execution, the Store test values check box appears: select this check box to automatically fill the form on subsequent tests.

Click the OK button to close the Lookup test dialog. Now the OK button on the Lookup editor window is also active, click it to have the new lookup appear in the Lookup result keys list.