Data transfer utilities

Do the following to load import template files in the Import Utility:

1. In the Advanced Accounting Console, go to Utilities > Data Transfer Utilities > Import Utility to open the Import Utility window.
2. Click New.
3. Enter the Import Name (description of the import task). For this example, use CONTACT.
4. Select an Import Type:
   • Select (C)omma for comma-delimited (.csv) files.
   • (NRDS only) Select (F)ixed Field when working with NRDS imports.
5. Select Name for the File to Modify.
6. (NRDS only) Select the NRDS Import (Skip Header Records) checkbox when performing a NRDS import. This instructs the import utility to skip the header records.
7. (NRDS only) Select the Member Import or the Office Import checkbox to specify whether you are importing the NRDS Member file or the NRDS Office file.
8. Click Save.
9. Click Edit.
10. Click Load and select the template.txt file.
11. Edit the template as needed, if necessary:
    1. In the template area, click Add to append a record row to the end of the template, or click Insert to add a row within the template. You can highlight a record row and click Delete to remove the record row.
    2. Click Ctrl+Z to open an edit window.
    3. Specify the fields to be edited:
       1. Enter the Field Name in the conversion table.
       2. (NRDS only) Enter the record Position from the NRDS file for that field.
       3. (NRDS only) Enter the Length of the field.
       4. Enter the Processing Code option, if used. This code identifies which record to update. The Processing Codes table on the window lists the options and their descriptions.

       If the field is a Name_Address field, for example, ADDRESS_1, CITY, STATE_PROVINCE, and so forth, you will need to specify which address purpose to use. The #1, #2, #3 processing codes specify the first, second or third address purpose. When importing address information, you need only specify the ADDRESS_1, CITY, STATE_PROVINCE, COUNTRY, and ZIP fields in Name_Address. iMIS will build the FULL_ADDRESS field and populate the corresponding fields in the Name table. The same is true for Name information. If you populate the LAST_NAME and FIRST_NAME, iMIS will automatically build the FULL_NAME field.

       (NRDS only) The following table defines some NRDS-related processing codes used in the import templates:

Code	Definition
#1, #2, #3	Address format. Used with one of the three PURPOSEs used in iMIS. Note: This must match the address exported to the National Association of REALTORS® (NAR). #1 – Updates the member’s current main address #2 – Updates the member’s second address #3 – Updates the member’s third address
N	Numeric format, removes leading zeroes. Do not use N for importing international phone numbers.
P	Phone format.
L	Instructs the import utility to insert a new Name.ID and Name.MAJOR_KEY if no match is found for that field (must have both).
D	Date format. Note: The NRDS import file contains two fields for zip code. To convert these fields to the iMIS zip code format, enter two zip code fields in the table. Specify Length = 5 for the first field. For the second code, specify Length = 4 and specify A- processing code (Append Data to Field, followed by a hyphen). Note: Use the A- processing code (Append Data to Field, followed by a hyphen) for importing Social Security numbers in the standard format (999-99-9999).

The Member import template uses the following additional Processing Codes:

Code	Definition
@	Takes the NRDS ID number in the import file and finds the Name record in iMIS where this number is populated in the MAJOR_KEY field to get the iMIS ID. The utility then populates the Name.CO_ID field with this iMIS ID for the member record to be imported. Any necessary company information flowdown is then processed.
%	Used to populate the preferred mail checkbox. The import file contains a field consisting of H, M, or O. The Import Utility maps each letter to a specific address. H = 1 – Main Address in the Name Maintenance window M = 2 – Second Address in the Address window O = 3 – Third Address in the Address window In the Name_Address.PREFERRED_MAIL field, enter a % processing code with a 3-digit code representing each of your three iMIS addresses. For example, using %312 means the third address in iMIS corresponds to the Home Address, the Main Address corresponds to the mailing address, and the Second Address corresponds to the office address.

Click to view image



12. Click Save to save the changes to the template.

When you want to access or reference a field in iMIS, you can often select from a field list. To export iMIS fields, format the field names exactly, including precise capitalization and punctuation. Table (file) names appear with initial capitals (with underscores as needed), field names appear in all capitals (with underscores as needed), and the two parts are connected with a period.

The following are the recommended iMIS tables to use to export data:

• Name
• Activity
• Subscription
• Orders
• Order_Lines
• Trans
• Invoice
• Invoice_Lines

Avoid using other iMIS tables for export, because data in the other tables might not be easily linked to the primary tables.

The first step to export data with the Export Utility is to create the basic export definition. The export definition specifies and formats the data to be exported.

Do the following to create the export definition:

1. In the Advanced Accounting Console, go to Utilities > Data Transfer Utilities > Export Utility.
2. Click New.
3. Type a name for the export definition in the Name field.
4. Select a Delimiter (character to be placed between fields):
• Fixed – no delimiter.
• Tab
• Comma
• Other – specifies other delimiter in a field that is displayed.
• Quotes – encloses text in double quotes. This is helpful if a comma delimiter is required and some text fields contain commas.

Note: The Quotes field displays only when a delimiter other than Fixed is selected.

5. (Optional) Select the Create Activity Record option to record each export record as a member activity in the iMIS database.
6. Define the Path for the export file.

Note: You can include an incremental sequence number in the name of the file by including a question mark in the name of the file. For example, if you enter c:\Temp\national?.txt, the file c:\Temp\national1.txt is created the first time you run the export, c:\Temp\national2.txt is created the second time you run the export, and so forth.

7. (Optional) Select the Allow OverWrites option to allow the file to be overwritten each time the export is run.
8. Click Save.
Click to view image

After you create the export definition, select the iMIS tables and fields to be included in the export definition. These Select Files are the basis for the export. You can sort the export records based on a file or field value in the Select Files and you can define search criteria to designate data in the Select Files to be exported.

Do the following to select the iMIS tables and fields to be exported:

1. In the Advanced Accounting Console, go to Utilities > Data Transfer Utilities > Export Utility.
2. Select an export definition from the Available Exports.
3. Click Edit.
4. Click Setup ‘Select’ Files to open the File Export Utility – Select Records window.
Click to view image



5. Select up to three iMIS tables as Select Search Files.
6. (Optional) Select the Sort Fields for these files. This determines the order the records are exported.
7. (Optional) Select the Break option if you want to create an export record each time the value in Sort Fields changes.

Note: To eliminate duplicate member export records, specify the Name.ID field as a Break field. The Export Utility will create only one export record for each unique Name.ID.

8. Define the joins for the Select Search Files in the File Connections fields. For more information, see Defining joins for select files.
9. Click OK to return to the Export Utility window.
10. Click Save.

Specify the general search criteria for the export. The export will contain only the information in the Select Search Files that meets the search criteria.

Do the following to specify search criteria:

1. In the Advanced Accounting Console, go to Utilities > Data Transfer Utilities > Export Utility.
2. Click Edit Search Criteria to open the Select Search Instructions window.
3. Click New to open the Search Instructions window.
Click to view image



4. Enter the Search Name.
5. (Optional) Select the Mode. This option allows you to export only the files or fields that meet defined criteria such as Comparison, Calculation, AND, and OR.

Note: If the Comparison Value is a field value, enter an asterisk (*) before the field name.

6. Click Save.
7. Click OK to return to the Export Utility window.

Note: You can specify search criteria from a record in a file that is not one of the three Select Search Files, or from another record within a Select File. If you are pulling information from a file other than a Select File, you must relate the file to one of the Select Search Files in the search criteria.

Click to view image



You can use SQL statements to specify the Comparison Value. For example, if Name is a Select File and you want to bring in the Home Address record, enter Name_Address.ID to search for a specific address. Enter *[ASISql(Name.ID)] for the Comparison Value, as shown in the following example.

Click to view image

Export field formatting is entered in the Comparison Value section of the Export Utility window. You can specify formatting for constants, iMIS field values, and Omnis calculations. You can also specify if/then processing of the output by formatting the export output with condition statements. This step narrows the general search criteria for specific fields.

Do the following to format export fields:

1. In the Advanced Accounting Console, go to Utilities > Data Transfer Utilities > Export Utility.
2. Select an Available Export.
3. Click Edit. There are three types of values that can be exported: a constant, a value contained in an iMIS field, and an Omnis calculation. Enter the formatting in the Value field.
• To specify the format for a constant (literal) value, type a double-quotation mark followed by the value. For example, if the first four characters of the export record must contain the value 0001, enter "0001 in the Comparison Value field.
• To specify the format for an iMIS field value, type the field name, for example, Name.MEMBER_TYPE.
• To specify the format for an Omnis calculation, type an equal sign followed by the calculation.
  For example, if you want to export the area code of the home telephone number field, type =mid(Name.HOME_PHONE,2,3). This statement exports the second, third, and fourth characters from the Name.HOME_PHONE field.
4. (Optional) Specify the desired Length of the field. This allows you to truncate or extend the field length.
5. (Optional) Enter a code in the Format field.
6. Click Add to save the format.
7. After all formatting instructions are added, click Save to save the formats.

The following table lists the available Format codes.

Formatting output with condition statements

You can format the export output with condition statements by creating a series of IF statements with corresponding output values.

1. In the Advanced Accounting Console, go to Utilities > Data Transfer Utilities > Export Utility.
2. Select an Available Export.
3. Click Search. Highlight an export Value.
4. Click Edit Calculations to display the Export Calculations Utility window.
5. Click New to create a new calculation.
Click to view image



6. Click Insert at the bottom of the window and click in the Name field to add a new name for the calculation.
7. Click Add to add a calculation or condition statement. In the Calculation field, type an equals sign followed by the calculation, for example, =Name.MEMBER_TYPE=‘CM’.
8. In the Output field, type a double quotation mark followed by the desired output value, for example "CO. In this example, every CM Member Type is exported as a CO value.

You can add as many calculations as you need. The utility exports the result of the last True calculation. The result can be an iMIS field name, a constant, or an Omnis calculation.

9. Click Save to save the calculation. The calculation is now listed as an Available Calculation on the Export Calculations Utility window.

Load record formats from other compatible software

To load records from compatible third-party software applications, the records must be comma- delimited or tab-delimited. Each record to be loaded must contain the following fields in this exact sequence: Value, Length, Format, Calculation, and Search.

Do the following to load records from compatible third-party software applications:

1. In the Advanced Accounting Console, go to Utilities > Data Transfer Utilities > Export Utility.
2. Select an Available Export.
3. Click Edit.
4. Click Load to select the file to load into the export definition.
5. Click Open to start the load process.
6. Click Save.

You can run an export on demand. Do the following to run an export on demand:

1. In the Advanced Accounting Console, go to Utilities > Data Transfer Utilities > Export Utility.
2. Select the Available Export to run.
3. Click Go.
4. When prompted to confirm running the export, select Yes.

You can also schedule an export. The export should be scheduled after you have finished work for the day.

Do the following to schedule an export:

1. In the Advanced Accounting Console, go to Utilities > Data Transfer Utilities > Export Utility.
2. Click Schedule to open the Schedule Program window.
3. Select the time to start the export by using the up and down arrows.
4. Click OK. The <remaining time> until Program Start message is displayed.
Click to view image

You can clone an existing export definition and make changes to create a new export definition.

Do the following to clone an export definition:

1. In the Advanced Accounting Console, go to Utilities > Data Transfer Utilities > Export Utility.
2. Select an Available Export.
3. Click Save As on the Export Utility window to display the Export Definition Save As window.
4. Type the New Name for the export definition.
5. Click Save to save the file with a new name.
6. Make changes in the new file and save.

If the Create Activity Record option is selected in the Export Utility window, iMIS records the date the export record was created and saves a copy of each member’s export record.

Do the following to view the activity record:

1. Make sure the Name file is one of the Select Files in the export definition.
2. In the Advanced Accounting Console, go to Configure > Membership > Set up activity types.
3. Create an activity type named EXPORT.

Note: When creating an EXPORT activity type, be sure to define some columns. For example:
UF1 - UF3 – Use these fields to enter prompts for the values entered in the Char 1 - Char 3 fields on the Event System Preferences window.
UF4 - UF5 – Use these fields to enter prompts for the values entered in the Number 1 - Number 2 fields on the Event System Preferences window.
UF6 - UF7 – Use these fields to enter prompts for the values entered in the Date 1 - Date 3 fields on the Event System Preferences window.

These columns will generate data that will be visible in the Panel Editor content item that you will create in order to view the activity records.

4. Click Save.
5. On the Staff site, go to RiSE > Page Builder > Manage content.
6. Select a Content Folder.
7. Go to New > Website Content.
8. Enter a Title and a Publish File Name.
9. Click add content.
10. In the Content Gallery, go to Content Types > Content > Panel Editor.
11. Click OK.
12. In the Panel list, select (New Panel).
13. Name the panel.
14. Select the Show data sources that allow multiple entries per object instance radio button.
15. Double-click Activity-EXPORT. The columns available for the EXPORT activity record are displayed.
16. Click Save & Close and then OK.
17. Click Save & Publish.

The order of your table joins can impact performance. Although the optimal order depends on the report, the key iMIS joins are:

Name.ID = Activity.ID
Name.ID = Subscription.ID
Name.ID = Invoice.BT_ID
Orders.ORDER_NUMBER = Order_Lines.ORDER_NUMBER
Product.PRODUCT_CODE = Order_Lines.PRODUCT_CODE
Name.ID = Orders.BT_ID

If you experience problems such as too much data being returned, multiple records being returned, or endless data retrieval, you may not have joined the files correctly.

If you are experiencing performance problems with a report that uses a join, try switching the order of the files.

Make sure that the files included in the join are Select Files in the export definition.