Skip to main content

Lookup Tables

While creating data mappings you may run into the need to store some variables in a table in a Key \ Value Pair.  This way you can call the variable by its Key to get the Value and pass it to the other system.  This is helpful when the 2 systems store data in different ways.  An example of this would be a table that stores United States abbreviations as the key and the full state name as the value.  This is helpful when the source stores the full state name and the target stores the abbreviation or visa versa.  So how do we set this up?

1) In the navigation bar click on "MORE".
2) Click on "Lookup Tables".
3) Click on the "+" sign on the right hand side.
4) In the pop-up input a Name.
5) On the ellipses button on the right under Description you will get a dropdown with "Create", "Append" and "Export".
     5.1) Create - Click this to create a new Key / Value Pair in the table.
     5.2) Append - If you have a CSV file already created this is how you import it.  I do want to point              out that is needs to be a CSV that is separated by "," and nothing else or import will fail.
     5.3) Export - Exports the table

Now that we have out table built, we need to consume it.  To do this we will use the functions found in the Lookup Functions.  Here are the specific ones:
  • LOOKUPTABLEVALUE1() - Looks in column 2 for the value we pass it and returns the value in column 1 in the same row.
  • LOOKUPTABLEVALUE1_DEFAULT() -Looks in column 2 for the value we pass it and returns the value in column 1 in the same row. If nothing is found in the table it returns the default value we pass it.
  • LOOKUPTABLEVALUE2() - Looks in column 1 for the value we pass it and returns the value in column 2 in the same row.
  • LOOKUPTABLEVALUE2_DEFAULT() - If nothing is found in the table it returns the default value we pass it. If nothing is found in the table it returns the default value we pass it.
SCRIBE provides some detailed documentation around lookup tables that I encourage you to check out.  Here is a link to the help documentation.  Make sure to check out the "See Also section at the bottom of the link to get more details on each of the functions we can use for the Lookup Tables."

Comments

Popular posts from this blog

Azure Blob Storage For Storing Microsoft Dynamics 365 Attachments

In my Living In SCRIBE Online Blog I talked about work I did to migrate CRM 4.0 on-premise to Dynamics 365 using SCRIBE Online.  In that blog I mentioned how there are about 80 GB worth of attachments that had to be moved into the cloud.  Well this could quickly get expensive if we stored them directly in CRM.  Also, this client wasn't using SharePoint.  So what option do we have to store this data so end users can access the data?  Why not use Azure Blob Storage?

Microsoft has an app in AppSource to move attachments from CRM to Azure blob storage.  Not only does it all for this, but it also has a web resource in it so you can allow for bulk uploads.  Here is how to set it up:

1) Go To AppSource
2) Install the Attachment Management App by clicking the "Get It Now" button under the image.
3) While its installing go to Azure and setup your blob storage account.
4) For each CRM instance you will need to setup 2 containers in your blob storage.  One is for email attachments …

XrmToolBox Bulk Attachment Manager - Version 2018.2.2.7 Released!

Today I have published version 2018.2.2.7 of my XrmToolBox plugin.  The purpose of this plugin is to make it easy to download and backup attachments in CRM.  This release is a major release as it is the first built version with all pieces working.  The first version only had note downloads working.

Overview of Plugin:
Current Version: 2018.2.2.7Purpose: Download Attachments and E-Mail attachmentsGitHub Link.GitHub Wiki Link.Nuget Package Link.How To Use The Tool: Launch XRMToolBox.Connect to organization.Step 1, choose what you want to download. (Once a choice is made, Step 2 becomes usable)Notes - Download attachments from note entity.E-Mail - Download attachments from emails.Both - Downloads attachments from notes and emails entities.Step 2, choose if you want to download all attachments in the system or specific attachments.  All Attachments - This will search the chosen entity to find all records that have an attachment and download them.Click on "Browse" and choose where t…

Updated To Version 1.7 - Azure Blob Storage For Storing Microsoft Dynamics 365 Attachments

In another post I talked about using Azure Blob Storage to store CRM attachments in Azure rather then in CRM.  Today I installed the app in a clients CRM system and found that Microsoft Labs updated the App to version 1.7.  This change had a lot of updates from UI to how to setup notes attachment movement.  Also they now allow for moving of existing attachments.  Here is the updated steps:

1) Go To AppSource
2) Install the Attachment Management App by clicking the "Get It Now" button under the image.
3) While its installing go to Azure and setup your blob storage account.
4) For each CRM instance you will need to setup 2 containers in your blob storage.  One is for email attachments and the other is for notes attachments.  So if you have a production instance and sandbox instance you will need to have 4 containers (DevEmails, DevNotes, ProdEmails, ProdNotes).  If you want to separate note attachments by entity, then create one container per entity.  The DevNotes or ProdNote…