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."
Labels:

Comments

Post a Comment

Popular posts from this blog

Power Pages Update Last Successful Login Using JavaScript and Power Pages API

 Recently while working on a Power Pages implementation for a client, I had the requirement to show the last time a user logged in on their profile page.  I thought this would be easy to do as there is already a field on the contact record for "Last Successful Login" (      adx_identity_lastsuccessfullogin).  This use to update when a user logged in, but it appears Microsoft has removed that automation. While searching I came across a few different ways of achieving this task.  One used application insights in Azure and another one used an HTTP endpoint setup in Power Automate.  I thought, this needs to be simpler.  What I came up with is to use Liquid with JavaScript to tell if a user is logged in or not.  Then use the new Power Pages api to update the logged in users contact record to mark the last time they logged in. Here is the approach I setup: 1) Make sure you turn on the api for contact in Site Settings. 1) Link to Microsoft Do...
102 comments
Read more

Validating User Input In CRM Portals With JavaScript

When we are setting up CRM Portals to allow customers to update their information, open cases, fill out an applications, etc. We want to make sure that we are validating their input before it is committed to CRM.  This way we ensure that our data is clean and meaningful to us and the customer. CRM Portals already has a lot validation checks built into it. But, on occasion we need to add our own.  To do this we will use JavaScript to run the validation and also to output a message to the user to tell them there is an issue they need to fix. Before we can do any JavaScript, we need to check and see if we are using JavaScript on an Entity Form or Web Page.  This is because the JavaScript, while similar, will be different.  First, we will go over the JavaScript for Entity Forms.  Then, we will go over the JavaScript for Web Pages.  Finally, we will look at the notification JavaScript. Entity Form: if (window.jQuery) { (function ($) { if ...
23 comments
Read more

Lookup In Select of URL For Power Pages

 While working on a Power Pages implementation I was consume the Power Pages API to get a Lookup table so I could show and hide information on the form using jQuery.  One issue I was having is I couldn't get a lookup field to return even though I had it as a field in the site setting for the table and included it in the my select statement in the URL.  I kept getting an `unexpected error occurred`. This issue is caused because lookup's are made up using 3 fields. 1) Formatted Value = The information you see in the lookup field (i.e. the record name) 2) Logical Name = The table the related record is a part of 3) Id = The id of the related record In the api there is a 4th field that is returned that is the Associated Navigation Property and it is the logical name of the lookup field. When setting up our select and even saying what columns can be returned in the API, we have to think how the OData endpoint will return the data to us.  ...
3 comments
Read more