BridgeGate™

Tutorial 4: Using Advanced Field Properties, Global Field Types, and Global Substitutions

Table of Contents

Introduction

This sample walks you through creating another simple Inbound Template mapped to a Simple Outbound Template, but this time the Outbound Templates will contain Fields that have had some extensive 'data massaging' or 'manipulation' done to them. We will learn how to manipulate the Inbound Data, including taking substrings or 'portions' of a Fields value instead of the whole thing. Also, replacing and stripping characters or groups of characters (words, perhaps, or codes, etc) from a Field will be demonstrated. Simple Alpha Masks can be used on Fields will be shown. A number of other Field features, such as using Fill Characters, Converting Case, Quoting data, Trimming Data are shown. One last piece, which acts 'Globally' to all Fields will be introduced, setting default values for the different Field Types, and globally substituting a specific value in the inbound data.

To continue in our series of Samples, and in an attempt to focus our Tutorials without making them overly complex, we will use a variation of the data format from the previous Sample. This data format has the First and Last Names combined into 1 Field, and has a social security number for each:

Fred Flintstone,Male,Flintstones,Black,40,347178451
Betty Rubble,Female,Flintstones,Black,30,330529785
Elroy Jetson,Male,The Jetsons,Bonde,10,771512154
Judy Jetson,Female,The Jetsons,White,16,900151245
Barney Rubble,Male,Flintstones,Blonde,33,445123456
Wilma Flintstone,Female,Flintstones,Red,35,881001042
George Jetson,Male,The Jetsons,Red,40,881008412
Jane Jetson,Female,The Jetsons,Orange,35,771559821

Creating an Inbound Template

You will now define the Inbound Template. As you may recall, the Inbound Template is where you tell BridgeGate™ how the data coming in to it will look; that is, the format--such as fields, lengths, data separators (delimiters), etc. The Inbound Template describes your company's data structure. To open our Inbound Template, click on the Manage Inbound Templates button in the toolbar:

Now we are going to create a new Workflow group in which to place our Inbound Template. Pick the drop down arrow in the Inbound Templates tree, and use the Wizard to create a New Workflow Group, calling it: sample4test.

Again, select the Wizard from the drop down arrow in the Inbound Templates tree. Use the Wizard to create a New Inbound Template.

Name this Template 'MySample4Inbound', named after the completed Inbound Template for this Tutorial, 'Sample4Inbound'.

As you remember from our sample data above, we have discovered we have a single Record, consisting of 6 Fields. Our Records are delimited with a Carriage Return (CRLF) and the Fields within the Record are delimited by a Comma (,). We can use the Comma Separated Values (CSV) for these data, down in the Template Properties (as seen below). By default, the first data type in the drop down says "CSV (Comma Separated Value)". This is close to what we want, but CSV formats also have double quotes "" around each Field in addition to the commas to separate Fields. Our data format does not specify this, so we have to set our Properties to Strip the quotes, and Trim the data. When finished, the Template Properties section will look like the following:

Now you will create a new record as you have done before. Select the record and name it 'PERSON',

Now add 6 Fields (all Char).

Select the first Field named UNNAMED1. Change the Field Name to 'Name' and the description to 'Name' as well, and complete the following:

2nd Field: Sex
3rd Field: Cartoon
4th Field: Hair Color
5th Field: Age
6th Field: SSN

The following screen shows the finished Record:

That's it for out Inbound Template. We now have all the information we need to define out original data. Click the save button in the Toolbar at the top of the screen.

Troubleshooting the Inbound Template

If you see the following error when trying to save: "Record Mapping for Record Type PERSON has no Comparator Value," you need to delete the Record Mapping from the PERSON Record. Follow the directions on the page defining the Record PERSON on how to delete the Record Mapping.

Creating the First Outbound Template: Using Advanced Field Properties

From the Toolbar, click on the Outbound Template Builder:

We will use the Wizard to 'Create a new Outbound Template'. Make sure the sample4test Workflow Group is selected.

Click the 'New Template Wizard', expand Basic Options and select 'Create an empty Template' and click 'Next'.

Name the template 'MySample4AdvancedPropertiesOutbound'.

The newly created Outbound Template is now available in the Outbound Templates tree.

For a change of pace, we will leave the data type as CSV (Comma Separated Value), which is a convenience data type for DELIMITED, using CRLF for Record Delimiter, and Comma (,) for Field Delimiter, and selecting "Quote All," which quotes all Fields (See picture below).

For Trim Data, uncheck the box (FALSE). For Record Delimiter, select CRLF. For the Inbound Template Dependencies section at the bottom, select the Inbound Template we just created.

Click on the 'Records' tab at the top and let's get started on the Outbound Field Mappings. Because there is so much ground to cover for the Field Properties, we will only be including specific Fields that we will be manipulating into the outbound data, which will greatly simplify the overall tasks we are focusing on for each Outbound Template.

Now, click on the Record and give it a description, and set it to loop over Person.

Notice you will specify the Record Delimiter at the end of the Record.

Select 'Mapping' to enable Drag and Drop.

Drag across the Name Field, Cartoon Field, and the SSN Field ONLY.

Create a new field from a copy of the NAME field and place it immediately the existing NAME field. Select the field named NAME and Press CTRL+C to copy the Name Field, then immediately press CTRL+V to paste a copy of it directly after it:

For the two Name Fields, we will be using examples of Field Portioning to grab the First Name and the Last Name, one for each Field.

For the Cartoon Field, we will demonstrate Stripping characters to remove the Word 'The' from 'The Jetsons', allowing both cartoons to not have this word. Notice 'Flintstones' is already without it.

For the SSN Field, we will demonstrate using a simple Alpha Mask to format it.

Click on the First 'Name' Field, and click the 'Advanced Properties...' button located at the bottom of the screen. The Advanced Properties Dialog screen is displayed:

Using Field Portioning

The default when the Advanced Field Properties screen is shown is the Field Portioning screen, which we will be using to 'split' the name apart. For this first 'Name' Field, will be grabbing just that- the First Name from the Name itself. Since it will not be known how long the First Name will be, we cannot use Exact Portioning. Select instead the radio button that says Relative Portioning.

We do not need to enter a Start Value, we want to start from the very first character itself. To do this, enter a 'space' for the Start Value, and enter a 0 (zero) for the Start Occurrence. This tells BridgeGate™ to do just that- start from the first character of the Field. Not trying to complicate this, you could actually enter anything you want for the Start Value; because the 0 (zero) is entered for Start Occurrence, the Start Value is ignored. Now, we want to grab every character until the first 'Space' we find. To do this, click in the End Value text box, and hit the space bar once to add a 'space' as the value. For End Occurrence, enter a 1 to denote the first occurrence of this value. Fortunately, BridgeGate™ allows us to test this before actually running a scenario through the Test screen. For the Test Data, enter 'Fred Flintstone' and click Test. The Result should show 'Fred' as below (Note: There is a single space in both the Start Value and the End Value textboxes).

Interestingly enough, if you select RIGHT from the 'From:' drop down, you will reverse this logic, and start from the last character of the Field. Start Occurrence of 0 (zero) would then denote starting at the LAST character, and go until the first occurrence of a 'space' from the End. The Result of this test would be 'Flintstone'.

Select the second 'Name' Field and click on its Advanced Properties Screen button to open the same screen. While we could do what the last paragraph foreshadowed and select 'RIGHT' using the same values, there is still another way to obtain the value. To demonstrate this, we will start from the first occurrence of 'space' and continue to the end of the Field. This isn't normally recommended for this type of requirement, since a Middle Name or Initial can sneak into the Field, but is shown only for additional demonstration. For Start Value, enter a 'space'. For Start Occurrence, enter a 1 (one). For end occurrence, we need nothing, but a value is required so enter a 'space' here as well. For End Occurrence, enter a 0 (zero) denoting the end of the Field. Enter 'Fred Flintstone' again and click test. Now, if 'From:' is changed to RIGHT, we would actually be reading from the LEFT now and the result would be Fred. It's good to have a Test button here to allow you to get familiar with the functionality of the Portioning Functionality.

Field Portioning Leniency

For situations in which there is no guarantee that a character is positioned in a specific position BridgeGate™ introduces the concept of 'leniency'.

For example, assume that we expect a character to exist in a specific position within a string of characters and that the 'Leniency' option has been set. If the character is not found the character immediately preceding or succeeding the expected position is substituted.

For another example, consider a string of characters that is 10 characters in length, and that this is the normal case. If we needed to extract characters in positions 7 through 10, and the length was only 8 characters, then BridgeGate™ will extract the 2 characters in position 7 and 8.

To use the 'Lenient' feature, click the checkbox control on the right side of the screen, immediately below the 'From:' drop-down control.

Note that the option to use 'Leniency' is not available for 'Date' type data.

Here is another example of the use of 'Leniency'. This deals with a numeric field for which the user can enter either a whole number or a number containing a decimal point and a mantissa. Whatever the type of value entered the ultimate value must return at least the whole number.

Take for example a number such as 123456.789. Let's say that in the Advanced Field Properties dialog the 'Start Value' and 'End Value' are set to the 'decimal point' and that the 'Lenient' check-box is not checked, as shown in the next screen-shot, and the user has not entered the decimal point and the mantissa.

This will result in a failure to capture any part of the value. However, to ensure that at least a value is captured irrespective of the entry of a decimal point, the 'Leniency' check-box must be checked. This is shown in the following screen-shot.

As shown the value is captured.

Using Field Stripping and Replacing

While this example will not use Replacing, it is virtually identical to the Stripping functionality we will be showing here, with the exception of adding a value to Replace. The Replace functionality is similar to the Global Substitution functionality that will be demonstrated further on in this very Sample. Select the Cartoon Field, and click on its 'Advanced Properties Screen' button. Select the Stripping tab. We will be only adding a single item to strip from this Cartoon Field. The word 'The' will be stripped, causing the value 'The Jetsons' to turn into 'Jetsons'. Single characters can be entered as well, and as many items as you like can be added to the list. Enter the word 'The' in 'Value to Strip' as shown below. Make sure 'Use Verbatim' is selected. Regular Expressions can also be used here.

Using Alpha Masks

Select the final Field in out simple Outbound Template, SSN, and click on its Advanced Properties Screen button. Select the 'Alpha Mask' tab as shown below. Since our SSN is coming in as an exactly 9 character long number, we can easily add a mask to it. For 'Alpha Mask To Use' enter XXX-XX-XXXX. For test data, enter 123456789 and hit Test:

Since this produces the Alpha Mask we want, select Ok, and close this Advanced Properties Screen. One last thing- because this format is not XML like most of the Sample Outbound Templates so far, we need to manually enter a Record Delimiter into our data, or else it will run across as one giant line. Make sure the SSN Field is currently selected, and click on 'New Field'. Change this Field's Field Type to CONSTANT, and from the 'Constant Value' drop down arrow, click and select %RECORD_DELIMITER%:

Click save from the Toolbar at the top of the screen:

Testing the Variable Outbound Template

To test this Outbound Template we just created, click on the Test button in the Toolbar.

Copy and paste the following data into the area of the Screen that asks for it, or type it in manually.

Fred Flintstone,Male,Flintstones,Black,40,347178451
Betty Rubble,Female,Flintstones,Black,30,330529785
Elroy Jetson,Male,The Jetsons,Bonde,10,771512154
Judy Jetson,Female,The Jetsons,White,16,900151245
Barney Rubble,Male,Flintstones,Blonde,33,445123456
Wilma Flintstone,Female,Flintstones,Red,35,881001042
George Jetson,Male,The Jetsons,Red,40,881008412
Jane Jetson,Female,The Jetsons,Orange,35,771559821

If you type this data in manually, and the Translation does not work, copy and paste this data in. Click the 'Test Data Translation' button and your browser will open showing the tested translation:

Trim Data

Notice 2 Fields have been created out of the single Name Field from the inbound data. 'The Jetsons' has been turned into 'Jetsons', and the SSN has had the appropriate Alpha Mask applied. There is one problem, however. Notice when we removed 'The' from the Jetsons, the leading 'space' was left on:

" Jetson" instead of "Jetson"

To fix this, we need to open our Outbound Template again, and for the Cartoon Field, change the 'Trim Data' setting to true. Alternatively, we could also set Trim Data 'Globally'.

Quote Data

Quote Data is another Field property like Trim Data, that is available both locally on each individual Field, and 'globally' as shown above the Trim Data in the image above.

Finishing up

After changing the Trim Data value, from either of the two locations above (as long as you are aware of the difference between the two), and Save the Template and run it again from the Test Template screen:

This time, "Jetsons" no longer has and leading white space. It is important to note that Trim Data removes any and all leading or trailing white space.

Creating the Second Outbound Template: Global Field Types, Global Substitutions, and other Misc. Field Properties (sample4miscpropertiesoutbound)

From the Toolbar, click on the Template Builder:

For this Outbound Template we are going to create a new Template instead of copying from an existing one. Use the Wizard to 'Create a new Outbound Template'. Name the Outbound Template 'MySample4MiscPropertiesOutbound'.

Set the Data Type to "Fixed Length," set Record Delimiter to "CRLF," and for the Inbound Template Dependency, select our Inbound Template, MySample4Inbound.

For this Example, we will be using the Global Field Types, and then add Global Substitutions to them, but for now, we will only create the Fields and look at some preliminary results of Fixed Length. Click on the 'Enable Drag and Drop' icon. Expand the Inbound Template on the Source Layout tree on the left side:

Instead of dragging the Fields one by one this time, we will drag the entire Record over from the Inbound Template. Select the PERSON Record from the Inbound Template, and drag it onto the Record: BODY on the Target Layout tree. For the 6 Fields now on the Outbound Template, set the Length for them as follows:

1st Field: Name 20
2nd Field: Sex 6
3rd Field: Cartoon 15
4th Field: Hair Color 10
5th Field: Age 2
6th Field: SSN 9

While SSN Field is still selected, click 'New…' and click 'Field' to add a new Field. Let's make this Field the Record Delimiter for the Record, and set it to CRLF. To do this, set the Field Type to CONSTANT, and for Constant Value, click the drop down and select %RECORD_DELIMITER%.

Click Save from the Toolbar, and MINIMIZE the screen (don't close it this time, we'll be back in a second). Open the Test Template screen, and copy the following data into it and run it:

Fred Flintstone,Male,Flintstones,Black,40,347178451
Betty Rubble,Female,Flintstones,Black,30,330529785
Elroy Jetson,Male,The Jetsons,Bonde,10,771512154
Judy Jetson,Female,The Jetsons,White,16,900151245
Barney Rubble,Male,Flintstones,Blonde,33,445123456
Wilma Flintstone,Female,Flintstones,Red,35,881001042
George Jetson,Male,The Jetsons,Red,40,881008412
Jane Jetson,Female,The Jetsons,Orange,35,771559821

The following Outbound data is produced:

As you can see, the individual Field Lengths are all shown above. What we will do now is set the Length for each field with a single Global Length. Return to each of the 6 Fields, and set the Length back to 0 (zero) for each. Now, if we save the Outbound Template and test it with the data again, we get:

Now, when the Length is set to 0 (zero) for Fixed Length, the actual length of the data is used. Let's return to the Template Field Properties and in the Global Field Types section, select 'Char', and then set the Length to 20 for each Field. Set the Convert Case to Upper Case.

Save the Template.

Test again:

As a final test of the Global Field Type settings, finish filling in the properties available for Char with:

Pad Direction: LEFT
Fill Character: - (single dash)
Convert Case: UPPER CASE

Click Save, and test. These changes act as the Default Settings for all Fields, but keep in mind they can be changed on a Field by Field basis, since these same properties are available on each Field as well:

Now we'll take a quick look at Global Substitutions.

These allow for a Condition to be placed on inbound data, and if it exists, a replacement value to be substituted for it. To demonstrate this, go to the 'Global Substitutions' tab in the lower left hand corner, and right mouse click in the text area, then a Replace Global Substitution will be displayed:

We will work with the Record (PERSON). We want to keep the Record Type (it's our only Inbound Record type), but change the Map Name to CARTOON.

We will replace 'Flintstones' with 'Rubbles'. This will search each Inbound Record 'PERSON' found for the Map Name CARTOON EQUAL to 'Flintstones'. If it is found, the value will be replaced with 'Rubbles', and any Field in the Outbound Template that references this Record Type/Map Name will show the value 'Rubbles'. Test your substitution to make sure it works properly.

Click save, and test the Template again.