Change the Structure of Your Data

Group Your Data

It’s recommended that you configure your integration as much as possible before you enable grouping. This includes configuring the destination structure, destination field formats, and mappings.

When grouping is enabled, Integration Studio marks all child fields and children of immediate object parents as Keys or Totals. By default, all nonnumeric fields default to Keys, and all numeric fields that use Aggregation as their source default to Totals. All other numeric fields are defaulted to Keys.

These designations are shown in the application for your review and can be changed, if needed. You can easily change a field from a Key to a Total by clicking the action icon beside the field and then Mark as Total. Similarly, you can change a Total to a Key by selecting Mark as Key.

Create a Custom Format for Strings

Integration Studio provides you with the ability to define a custom format for fields that are configured to use the String, Date/Time, or Number data types.

Strings

Data such as phone numbers, zip codes, and national IDs are often stored as strings and might require formatting to be properly interpreted between systems. For example, some legacy target systems store a phone number and extension in the same field, while others keep them separate. You must define a custom format string using the hash symbol (#) to represent a character from the computed destination value and any other character for formatting. The following are examples of valid format strings:

• Phone: ###-###-#### Ext: ####
• +## (##) #### ####
• ###-##-####
• ### ###

If the length of the unformatted value is more or less than the length of the format string, the unformatted value is returned. The length of the value is the count of all alpha-numeric characters—whitespace and special characters such as the percentage symbol (%) aren’t included. The format string length is the count of hash symbols.

For example, if the unformatted value is 123456789, 123 45 6789, or 12345-6789 and the format string is ###-##-####, 123-45-6789 is returned. Similarly, the same unformatted value is returned when you use ###-###-#### as the format string. This is because the length of the unformatted value is 9 and the length of the format string is 10.

To define a custom format for a string:

1. Click the action icon beside a field.
2. Click Edit.
3. In the Edit <FieldName> element dialog box that opens, enter your custom alpha-numeric format in the Custom Format field.
4. Click Save.

Date/Time

Custom dates and times must conform to .Net specifications. The following are examples of valid Date/Time format strings:

• MMM dd yy
• “Month”: MMM

Number

Custom numbers mainly conform to .Net specifications with an additional option to omit the separator while defining decimal precision. For example, you can return a number value that has up to seven digits and contains three decimals but doesn’t have a separator, by using the following string format: #######V###. You can return the same value but with a separator by using the following format string: #######.###. The following are examples of valid Numeric format strings:

• #####v###
• #,###,####,#####
• ## “years”

To define a custom format for Date/Time and Number data types:

1. Click the action icon beside a field.
2. Click Edit.
3. Select Custom from the Data Format drop-down list.
4. Enter your custom alpha-numeric format in the Custom Format field.
5. Click Save.