The Database Query Mapping Provider was released with pasTransfer 4.25.0.0 RTM.  
Head over to our Blogs if you missed the post.
 
The Database Query Mapping Provider is a powerful mapping provider within all wizards which allows you to connect to any SQL Server, OLEDB, or ODBC data source to use for the source of your mapping and lookup data, whether it is a table, stored procedure, view, or a dynamic SQL statement.  The pasTransfer UI has the added capability of being able to edit and maintain mappings and lookups in most products.  The operations you are able to perform against the mappings based on the insert, update, and delete permissions for the provided data source.  After you make changes, pasTransfer generates commands to perform these operations within a transaction, guaranteeing the integrity of the changes.

For the technical user, we will dive head first into how to use this new mapping provider.

Connection Options

You will have the opportunity to choose a mapping provider after all source data is imported.  We have standardized all wizards and they now have the same mapping providers, and you are now able to connect to, literally, almost any data source possible to use for mappings and lookups.

The SQL Server database query is extremely specific with what it can connect to – a SQL Server database.  If you do not have a SQL Server data source to connect to, OLEDB is a great alternative.  OLEDB is capable of connecting to relational AND non-relational databases such as MS Access, AS/400, CSV text files, DBF/FoxPro, Excel spreadsheets, MySQL, and Oracle – just to name a few.  OLEDB really covers almost every data source type imaginable as long as you have the provider to support it.  For all other relational databases that OLEDB cannot connect to, ODBC will more likely than not work.  All connection types also allow the use of global macros if you enable them.

With SQL Server being so specific, we have been able to provide a script to deploy a schema containing generic tables related to mapping within your current wizard.  We were unable to achieve this with other data sources because they vary too much in syntax and methods of deployment.  The script is 100% viewable and editable, meaning if your mapping table needs additional columns for SunSystems T-Codes, for example, you can easily add a new column definition in the mapping table before deploying the script. You will need to be able to provide credentials that will allow the creation of tables when deploying the script.



We are also able to generate a connection string for you with SQL server, but that is not the case with OLEDB and ODBC.  If you are unfamiliar with how connection strings work, our documentation helps explain how pasTransfer can help you generate a connection string, and there are plenty of helpful resources online.

Mapping Options
 
Now that you have created a connection string, you can define your mappings and lookups.  If you are in the Ledger wizard, you will also have this opportunity to define indicators.  They are optional, so you will not need to provide a query type if you do not choose to.  If you are directly pulling your mappings and lookups from a specific table, pasTransfer already has all of the existing tables listed, so you can choose one from the dropdown. 

Sometimes, however, selecting every row of data from a table is not what we are looking for.  At this point, we can provide a dynamic query to retrieve the set of data we are looking for.  This is an incredibly amazing feature for mappings and lookups because they offer you an extraordinary amount of power, such as executing stored procedures, joining multiple tables, applying filters and group-by clauses… and for this mapping provider, mapping precedence is not required because skillfully writing unions will allow you to achieve the exact same results.  Lookups work the same way, you just need to provide a lookup name that you use when creating lookup macros.  Mappings and Lookups are all macro-capable, but it is important to ensure the macro start (left square bracket [ ) and macro stop (right square bracket ] ) are properly escaped using the escape character (backslash \ ).

 If you are unsure which mapping columns are required, you can load a default query to show you which columns are required to be returned, and a syntax check will validate the resultant set of data does contain these columns.  From here, one of the coolest features in this release is the new mapping and lookup editor.

Mapping Editor

The mapping editor is dynamic, and its view will differ whether you are editing ledger mappings, mappings from the other wizards, or lookups.  At the very least, you will see a data grid representing your custom mapping or the lookup you currently had selected.  If you are entering the editor from the Ledger wizard and you have provided an indicator query or table name, you will also see a second data grid to the left of the mappings grid.  If the indicator grid is present, you have the option to hide it as necessary, just as you can on the portal.


You will see some labels below each grid telling you if you can insert, update, or delete.  This is because pasTransfer intelligently infers these capabilities by using the .NET framework and the table name or query you provided.  This is not perfect, but in order to guarantee the integrity of any commands executed against the database, we perform all saves inside of a transaction.  This means that if the database does not support transactions, all saves will fail and you will be restricted to viewing your mappings and lookups instead of editing them.

 

Column headers are color coded in order to show whether this column accepts NULL values.  A dark grey color indicates a column is required to have a value, while light grey indicates null values are acceptable.  The cells are also color coded to indicate whether the field is macro enabled.  If a cell is white, macro values are not resolved, while a yellow cell indicates macros will be resolved.  Pressing F2 while editing any cell will open the macro designer.  Check out the help files if you have any questions relating to macros.  Some columns are not intended to be changed, such as Primary Keys, GUIDs, and Rowversions, so we hide these by default. The VAT, Percentage, and Fixed Amount columns default to 0, while the Remainder column defaults to bit value 1 (Boolean true).



In this example, the SourceAccount, SourceDC, DestinationAccount, DestinationDC, VAT, Percentage, FixedAmount, and Remainder columns do not allow NULL values.  Meanwhile, the DestinationAccount, Condition, and Error columns accept macro values. The MappingID and Rowversion columns are not visible.
To add new lines, simply begin typing in the last row of the grid.  When editing cells which we expect to contain a specific value, we provide a dropdown list to select the value you are looking for.  Delete a row by highlighting the entire row and pressing the delete key.

It is possible that we make errors in our mappings, or someone else has edited the mappings while we are working on them.  Unlike with pasPortal, we are unable to prevent others from editing mappings at the same time as us by checking them in and out.  You have the options to undo your changes, or retrieve the most update-to-date version from the respective data source.  Bear in mind that all changes when undoing, refreshing, and saving are irreversible.

Sometimes, a notification icon will appear next to a row.  Hovering over the icon will provide context as to what has happened within the mapping editor.  Newly inserted rows will contain the message Added, updated rows will contain the message Modified, and rows containing errors will provide the exact error message for the row encountered while editing the row.  If a row contains errors, it is impossible to navigate to another row until you correct the errors or delete the entire row.  Mapping tokens, such as Default (DEF), All (ALL), and Do Not Transfer (DNT) can be defined at the next stage of the wizard just the same as they are on the portal.





Wildcards:

Wildcards may be used in the mapping, indicators and lookup table editors.  Wildcard characters allow accounts to be filtered or specified as long as their account code matches criteria on coordination with the wildcard characters used.  They are a very powerful and flexible way to simplify mapping/lookup/indicator sheets and filter accounts. 

The following examples are valid, practical examples for mapping sheets:
    
Example:
 
Below, you will find an example of Mappings and Lookups used together to transform source data
 
 

• Highlighted in orange below you will find the source account data and then highlighted in yellow is the destination account data based on the mapping:

 
 
Looking at the source data you will see a variety of 3 digit numbers beginning with either a 1,2, or 3 which will be captured by the lines in the mapping table highlighted below:

 

• Account codes beginning with a number 1 were transformed into the same account code with an S in front of it.
• Account codes beginning with the number 2 or 3 were transformed using the Lookup table provided above returning the corresponding ‘Business Unit’ value.

Conclusion:

We are extremely excited to be able to bring you the Database Query Mapping Provider. As mentioned before, the provider is extremely powerful and provides a means of mapping outside of pasPortal. We encourage that if you have any questions, concerns, or comments in relation to this new feature, you direct them to feedback@pasUnity.com.  We look forward to hearing from you!