Difference between revisions of "Custom Lookup Options"

From SmartWiki
Jump to: navigation, search
m
m (→‎Setting Up a Successive Dropdown Lists)
 
(53 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
{{ Banner-UnderConstruction }}
 
−
 
−
 
−
 
 
=Overview=
 
=Overview=
 
The''' Custom Lookup Options''' is a feature that allows you to import custom datasets for lookups and validation based on client-specific needs. For example, you can upload a CSV file whose data can be used as options within a select-type custom field such as '''Select One – Dropdown''', '''Select One - Radio Button''', or '''Lookup - Autocomplete Options'''.
 
The''' Custom Lookup Options''' is a feature that allows you to import custom datasets for lookups and validation based on client-specific needs. For example, you can upload a CSV file whose data can be used as options within a select-type custom field such as '''Select One – Dropdown''', '''Select One - Radio Button''', or '''Lookup - Autocomplete Options'''.
Line 13: Line 9:
  
 
=Configuration - Essentials=
 
=Configuration - Essentials=
−
As an example, let's upload a custom lookup CSV containing university and course information. A sample of this CSV data can be found at the end of this article for reference.
+
As an example, let's upload a custom lookup CSV containing university and course information. A sample of ''UniversityCourseLookup.csv'' is included in the appendix at the end of this article.
  
 
===Importing a New Custom Lookup===
 
===Importing a New Custom Lookup===
 
# Navigate to '''Global Settings''' > '''Custom Lookup Options''' and click on the '''Import Custom Lookup Options''' button represented by an import icon.<br />[[File:2021-07-ticket-124709-1.png|thumb|none|300px]]
 
# Navigate to '''Global Settings''' > '''Custom Lookup Options''' and click on the '''Import Custom Lookup Options''' button represented by an import icon.<br />[[File:2021-07-ticket-124709-1.png|thumb|none|300px]]
−
# You will now access step 1 one of the '''Custom Lookup Options Import''' wizard. Upload a CSV containing your custom data. The first row of the CSV must include column headers. A sample CSV is provided under the '''Settings Explained''' section below.
+
# You will now access step 1 one of the '''Custom Lookup Options Import''' wizard. Upload a CSV containing your custom data. The first row of the CSV must include column headers. A sample of ''UniversityCourseLookup.csv'' is included in the appendix at the end of this article.
−
# Give your custom lookup a descriptive name under '''Lookup Name''' that will help you remember what it is for. For our example, we will call our lookup "UniversityCourseLookup". Select one of the columns for the '''Lookup Key'''. The lookup key is a non-unique identifier that is associated with ''one or more'' records in the table. In our example, our lookup key is the "University ID" because this number corresponds to the different universities in our dataset.  
+
# Give your custom lookup a descriptive name under '''Lookup Name''' that will help you remember what it is for. For our example, we will call our lookup "UniversityCourseLookup".
−
# The '''Primary Key''' is optional. A primary key is a unique identifier that corresponds to a ''single'' record in our table. For our example, the primary key is "Course ID" because each row in the table has a different Course ID that distinguishes it. Click '''Next''' to continue.
+
# Select one of the columns for the '''Lookup Key'''. The lookup key is a non-unique identifier that is associated with ''one or more'' rows in the table. In our example, our lookup key is the "University ID" because this number corresponds to the different universities in our dataset.
 +
# The '''Primary Key''' is optional. A primary key is a unique identifier that corresponds to a ''single'' row in our table. For our example, the primary key is "Course ID" because each row in the table has a uniquely different Course ID that distinguishes it from every other row. Click '''Next''' to continue.
 
# Check over the sample of the uploaded data to ensure it has been processed as intended. Toggle which columns you want to include in the final import. Click '''Next''' to proceed.
 
# Check over the sample of the uploaded data to ensure it has been processed as intended. Toggle which columns you want to include in the final import. Click '''Next''' to proceed.
−
# The last step will let you review the final changes. Each attribute-value pair under '''Lookup Value''' represents a value under your selected columns for a particular lookup value. Click '''Next''' to complete the import process.
+
# The last step will let you review the final changes. Click '''Next''' to complete the import process.
−
 
 
−
 
 
  
 
===Updating an Existing Custom Lookup===
 
===Updating an Existing Custom Lookup===
 
# To update an existing custom lookup, import the updated file and be sure to type out the exact name of the lookup you want to update in the '''Lookup Name''' field. Specify the lookup key and any primary key needed. Click '''Next''' to continue.
 
# To update an existing custom lookup, import the updated file and be sure to type out the exact name of the lookup you want to update in the '''Lookup Name''' field. Specify the lookup key and any primary key needed. Click '''Next''' to continue.
−
# You will see a warning that notifies you that an existing lookup with the same name already exists. All existing records for that lookup will be deleted and re-imported with the new updated data. Click '''Next''' to continue and click '''Yes''' on the alert pop-up.
+
# You will see a warning that notifies you that an existing lookup with the same name already exists. All existing rows with that lookup name will be deleted and re-imported with the new updated data. Click '''Next''' to continue and click '''Yes''' on the alert pop-up.
 
# Click '''Next''' to finish the import.
 
# Click '''Next''' to finish the import.
 +
 +
 +
 +
===Viewing Custom Lookup Data===
 +
To view the data of any custom lookup, navigate to '''Global Settings''' > '''Custom Lookup Options''' to see a list of imported lookups. Click the name of the lookup you want to see in more detail. This will allow you to see the uploaded data in a list view format organized by the '''Lookup Key'''.
 +
 +
[[File:2021-07-ticket-124709-5.png|thumb|none|800px|A sample list view of an imported custom lookup.]]
 +
 +
 +
The list of values under '''Lookup Value''' form an attribute-value pair of column values for the specified row. To see this data in more detail, click the icon on the far left marked '''Open''' on any row. Based on the expanded '''Lookup Value''' data, we can see that this specific row contains the values for our table columns "Course ID", "University", and "Course". 
 +
 +
[[File:2021-07-ticket-124709-6.png|thumb|none|800px|Attribute-value pairs in the <strong>Lookup Value</strong> are stored in JSON format.]]
  
 
=Configuration - Advanced=
 
=Configuration - Advanced=
−
===Setting up Dynamic Dropdown Lists===
+
===Setting Up a Standalone Dropdown List===
 +
The easiest way to make use of your imported custom lookup options is to use a standalone dropdown list. We will use our university course lookup as an example.
 +
 
 +
# Create a custom field of type '''Select One - Dropdown List'''.
 +
# Toggle on '''Enable Dynamic Content'''
 +
# You will now see a new toggle called '''Use Custom Lookup Options'''. Toggle this on.
 +
# You will now see additional settings for the custom lookup. Under the '''Lookup Name''' dropdown, select the custom lookup you want to reference. Under '''Option Text Identifier''', type the name of the lookup column whose values you want to populate in the dropdown. In our example, we want to expose the available course names in the dropdown so we will type the name of the column that contains that information: "Course".
 +
 
 +
 
 +
[[File:2021-07-ticket-124709-7.png|thumb|none|800px|Configuration settings for a standalone dropdown using a custom lookup.]]
 +
 
 +
===Setting Up a Parent-Child Dropdown List===
 
Custom lookups can be incorporated into dynamic dropdown lists using the parent and child relationship between data. After uploading a CSV for your custom data, you can set up a series of parent and child dropdown lists that can further filter user options based on previous selections.
 
Custom lookups can be incorporated into dynamic dropdown lists using the parent and child relationship between data. After uploading a CSV for your custom data, you can set up a series of parent and child dropdown lists that can further filter user options based on previous selections.
  
−
====Parent Dropdowns====
+
====Configuring the Parent Dropdown====
−
To set up a parent dropdown list, create a custom field of type '''Select One - Dropdown List'''. In the '''Pre-Defined Options''' field, map your dropdown options to the lookup keys you chose for your custom lookup. For example, a custom lookup for course registration may have a lookup key that uses 100 to denote freshman courses, 200 to denote sophomore courses, etc. When the user selects a course year from the parent dropdown, their options for the child dropdown will be filtered.
+
# Create a custom field of type '''Select One - Dropdown List'''.
 +
# In the '''Predefined Options''' field, map the various lookup keys of your custom lookup data to the options you want to surface in the dropdown. In our example, our lookup keys span a range of 100 to 600, with each 100 increment corresponding to a different university. Mapping the lookup keys in this way will allow the child dropdown we set up later to be filtered by the selected lookup key. For example, if the user selects "Totally Totes College" (with a lookup key mapped to 100), the corresponding child dropdown options will filter the custom lookup data by this lookup key and only return the courses available to that university.
 +
 
  
 +
[[File:2021-07-ticket-124709-8.png|thumb|none|800px|Configuration for a parent dropdown list. Note the first item has been set to "--Select One--" to make it the default option.]]
  
−
[[File:2021-07-ticket-124709-2.png|thumb|none|800px]]
 
  
  
  
−
====Child Dropdowns====
 
−
To set up a child dropdown list, create a custom field of type '''Select One - Dropdown List''' and set the '''Parent Field''' to the parent dropdown list created above. Toggle on '''Enable Dynamic Content'''. This will expose the toggle '''Use Custom Lookup Options'''. When you toggle this on, you see additional settings to specify the lookup. Under '''Lookup Name''', select the desired custom lookup you imported. Under '''Option Text Identifier''', type the name of the column whose data you want to display in the child dropdown. In this example, we want to show the course name.
 
  
−
[[File:2021-07-ticket-124709-3.png|thumb|none|800px]]
+
====Configuring the Child Dropdown====
 +
# Create a custom field of type '''Select One - Dropdown List''' and set the '''Parent Field''' to the parent dropdown list created above.
 +
# Toggle on '''Enable Dynamic Content'''
 +
# You will now see a new toggle called '''Use Custom Lookup Options'''. Toggle this on.
 +
# You will now see additional settings for the custom lookup. Under the '''Lookup Name''' dropdown, select the custom lookup you want to reference. Under '''Option Text Identifier''', type the name of the lookup column whose values you want to populate in the dropdown. In our example, we want to expose the available course names in the dropdown so we will type the name of the column that contains that information: "Course".
  
  
 +
[[File:2021-07-ticket-124709-9.png|thumb|none|800px|Configuration settings for the child dropdown.]]
  
−
====Dropdown Relationship====
+
====End Result of Parent-Child Dropdown====
−
The options within the child dropdown should now change depending on the selections made in the parent dropdown.
+
The available options within the child dropdown will now change depending on the selections made in the parent dropdown. In our example, changing the university in the parent dropdown will change the available courses we can select in the child dropdown.
  
 
[[File:2021-07-ticket-124709-4.png|thumb|none|800px]]
 
[[File:2021-07-ticket-124709-4.png|thumb|none|800px]]
  
 +
===Setting Up Successive Dropdown Lists===
 +
In the previous example, all the predefined options had to be hardcoded into to the custom field configuration settings. This could be a laborious process if the custom data needs regular updates or new entries. The process can be simplified by importing multiple lookups. The section will illustrate how to use successive dropdown lists and reference multiple custom lookups without having to hardcode predefined options.
 +
 +
As an example, we want to set up a chain of parent-child dropdowns for course registration. We want the user to first select from a list of available universities, then select from a list of available faculties at that selected university, and lastly, we want the user to select from a list of available courses under that university faculty.
  
 +
All referenced CSV files are provided in the appendix below.
 +
 +
====Configuring the Parent Dropdown====
 +
# Import ''UniversityLookup.csv'' with the "University ID" as the '''Lookup Key''' and the "Primary Key" as the '''Primary Key'''. Set '''Lookup Name''' to "UniversityLookup".
 +
# Create a custom field of type '''Select One - Dropdown List''' and give it the caption "University".
 +
# Toggle on '''Enable Dynamic Content''' and '''Use Custom Lookup Options'''.
 +
# Under '''Lookup Name''', select "University Lookup". Under '''Option Text Identifier''', type the name of the lookup column you want to surface. For our example, we type "University".
 +
 +
 +
[[File:2021-07-ticket-124709-13.png|thumb|none|800px|Configuration settings for the university dropdown.]]
 +
 +
 +
 +
====Configuring the First Child Dropdown====
 +
# Import ''FacultyLookup.csv'' with the "University ID" as the '''Lookup Key''' and the "Faculty ID" as the '''Primary Key'''. Set '''Lookup Name''' to "FacultyLookup".
 +
# Create a custom field of type '''Select One - Dropdown List''' and give it the caption "Faculty".
 +
# Set the '''Parent Field''' to previously created "University" dropdown custom field.
 +
# Toggle on '''Enable Dynamic Content''' and '''Use Custom Lookup Options'''.
 +
# Under '''Lookup Name''', select "FacultyLookup". Under '''Option Text Identifier''', type the name of the lookup column you want to surface. For our example, we type "Faculty".
 +
 +
 +
[[File:2021-07-ticket-124709-11.png|thumb|none|800px|Configuration settings for the faculty dropdown.]]
 +
 +
 +
 +
====Configuring the Second Child Dropdown====
 +
# Import ''UniversityCourseLookup.csv'' with the "Faculty ID" as the '''Lookup Key''' and the "Course ID" as the '''Primary Key'''. Set '''Lookup Name''' to "UniversityCourseLookup".
 +
# Create a custom field of type '''Select One - Dropdown List''' and give it the caption "Course".
 +
# Set the '''Parent Field''' to previously created "Faculty" dropdown custom field.
 +
# Toggle on '''Enable Dynamic Content''' and '''Use Custom Lookup Options'''.
 +
# Under '''Lookup Name''', select "UniversityCourseLookup". Under '''Option Text Identifier''', type the name of the lookup column you want to surface. For our example, we type "Course".
 +
 +
 +
[[File:2021-07-ticket-124709-12.png|thumb|none|800px|Configuration settings for the course dropdown.]]
 +
 +
 +
 +
====End Result of Successive Parent-Child Dropdowns====
 +
The available options within the child dropdown will now change depending on the selections made in the parent dropdown. In our example, changing the university in the parent dropdown will change the available faculty listings in the subsequent faculty dropdown. The user's selection in this faculty dropdown will then filter the available options in the course dropdown.
 +
 +
 +
[[File:2021-07-ticket-124709-14.png|thumb|none|800px|Successive parent and child dropdowns.]]
  
 
=Settings Explained=
 
=Settings Explained=
Line 67: Line 136:
 
|-
 
|-
 
||Lookup Key
 
||Lookup Key
−
||The Lookup Key is a non-unique identifier used to return all records with a matching Lookup Name.
+
||The Lookup Key is a non-unique identifier used to return all rows with a matching Lookup Name.
 
|-
 
|-
 
||Primary Key
 
||Primary Key
−
||(Optional) The Primary Key is an optional unique key used to return a single record that matches the Lookup Name and Primary Key value. Records without a matching Primary Key will be imported as new records.
+
||(Optional) The Primary Key is an optional unique key used to return a single row that matches the Lookup Name and Primary Key value. Rows without a matching Primary Key will be imported as new rows.
 
|}
 
|}
  
−
===Dropdown Settings===
+
===Configuration Settings===
 
{| class="wikitable" style="height: 178px;" width="548"
 
{| class="wikitable" style="height: 178px;" width="548"
 
|-
 
|-
Line 83: Line 152:
 
|-
 
|-
 
||Option Value Identifier
 
||Option Value Identifier
−
||(Optional)
+
||(Optional) The name of the column header from your custom dataset whose values are unique for each row. If a '''Primary Key''' was defined when importing the custom lookup, you can leave this field blank.
 
|}
 
|}
  
−
===Sample CSV===
+
=Appendix=
−
  University ID,Course ID,University,Course
+
===UniversityLookup.csv===
−
  0,0,--- Please Select ---,--- Please Select ---
+
  University ID,Primary Key,University<br data-attributes="%20/" />100,100,--- Please Select ---<br data-attributes="%20/" />100,100,Totally Totes College<br data-attributes="%20/" />200,200,Obviously OBVI College<br data-attributes="%20/" />300,300,Artisanal Art College<br data-attributes="%20/" />400,400,Coffee for Me University<br data-attributes="%20/" />500,500,Asymmetrical University of Aesthetics<br data-attributes="%20/" />600,600,University of Cold-pressed Flannel
−
  100,1001,Totally Totes College,Introduction to Art
+
===FacultyLookup.csv===
−
100,1002,Totally Totes College,Intermediary Art
+
  University ID,Faculty ID,Faculty,University<br data-attributes="%20/" />100,100,--- Please Select ---,--- Please Select ---<br data-attributes="%20/" />100,101,Arts & Literature ,Totally Totes College<br data-attributes="%20/" />100,102,Natural Sciences,Totally Totes College<br data-attributes="%20/" />100,103,Humanities,Totally Totes College<br data-attributes="%20/" />100,104,Mathematics,Totally Totes College<br data-attributes="%20/" />200,201,English Literature,Obviously OBVI College<br data-attributes="%20/" />200,202,Physical Sciences,Obviously OBVI College<br data-attributes="%20/" />200,203,History & Humanities,Obviously OBVI College<br data-attributes="%20/" />200,204,Mathematics & Computer Science,Obviously OBVI College<br data-attributes="%20/" />300,301,Literature ,Artisanal Art College<br data-attributes="%20/" />300,302,Biology, Chemistry, and Physics,Artisanal Art College<br data-attributes="%20/" />300,303,History, Anthropology, and Psychology,Artisanal Art College<br data-attributes="%20/" />300,304,Mathematics & Philosophical Sciences,Artisanal Art College<br data-attributes="%20/" />400,401,Faculty of English Literature,Coffee for Me University<br data-attributes="%20/" />400,402,Faculty of Natural Sciences,Coffee for Me University<br data-attributes="%20/" />400,403,Faculty of History,Coffee for Me University<br data-attributes="%20/" />400,404,Faculty of Mathematics & Computing,Coffee for Me University<br data-attributes="%20/" />500,501,Modern Arts & Literature,Asymmetrical University of Aesthetics<br data-attributes="%20/" />500,502,Hard Sciences,Asymmetrical University of Aesthetics<br data-attributes="%20/" />500,503,Modern History & Anthropology,Asymmetrical University of Aesthetics<br data-attributes="%20/" />500,504,Mathematics & Computing,Asymmetrical University of Aesthetics<br data-attributes="%20/" />600,601,Department of English,University of Cold-pressed Flannel<br data-attributes="%20/" />600,602,Department of Science,University of Cold-pressed Flannel<br data-attributes="%20/" />600,603,Department of History,University of Cold-pressed Flannel<br data-attributes="%20/" />600,604,Department of Mathematics,University of Cold-pressed Flannel
−
100,1003,Totally Totes College,Advanced Art
+
 
−
100,1004,Totally Totes College,Introduction to Music
+
===UniversityCourseLookup.csv===
−
100,1005,Totally Totes College,Intermediary Music
+
  University ID,Course ID,Faculty ID,University,Faculty,Course<br data-attributes="%20/" />100,1000,100,--- Please Select ---,--- Please Select ---,--- Please Select ---<br data-attributes="%20/" />100,1001,101,Totally Totes College,Arts & Literature,Gothic Literature in the 19th Century<br data-attributes="%20/" />100,1002,102,Totally Totes College,Natural Sciences,Organic Compounds in Chemistry<br data-attributes="%20/" />100,1003,103,Totally Totes College,Humanities,Introduction to Modern History<br data-attributes="%20/" />100,1004,104,Totally Totes College,Mathematics,Introduction to Calculus <br data-attributes="%20/" />100,1005,101,Totally Totes College,Arts & Literature,Comparitive Mythology & Folklore<br data-attributes="%20/" />100,1006,101,Totally Totes College,Arts & Literature,Introduction to Latin American Literature<br data-attributes="%20/" />200,2001,202,Obviously OBVI College,Physical Sciences,Fluid Dynamics in Brownian Motion<br data-attributes="%20/" />200,2002,204,Obviously OBVI College,Mathematics & Computer Science,Advanced Lorentz Transformations<br data-attributes="%20/" />200,2003,201,Obviously OBVI College,English Literature,Analysis of Arthurian Legend<br data-attributes="%20/" />200,2004,202,Obviously OBVI College,Physical Sciences,Morphology in Evolution<br data-attributes="%20/" />200,2005,202,Obviously OBVI College,Physical Sciences,Thermodynamics<br data-attributes="%20/" />200,2006,203,Obviously OBVI College,History & Humanities,Early Modern Period<br data-attributes="%20/" />300,3001,303,Artisanal Art College,History, Anthropology, and Psychology,Architecture of the Roman Empire<br data-attributes="%20/" />300,3002,302,Artisanal Art College,Biology, Chemistry, and Physics,Anatomy of the Human Body<br data-attributes="%20/" />300,3003,304,Artisanal Art College,Mathematics & Philosophical Sciences,Topology of Non-Euclidean Systems <br data-attributes="%20/" />300,3004,301,Artisanal Art College,Literature ,Ancient Epics and Poetry<br data-attributes="%20/" />300,3005,303,Artisanal Art College,History, Anthropology, and Psychology,History of the Dutch Golden Age<br data-attributes="%20/" />300,3006,302,Artisanal Art College,Biology, Chemistry, and Physics,Virology and Epidemiology<br data-attributes="%20/" />400,4001,402,Coffee for Me University,Faculty of Natural Sciences,Inorganic Compounds in Chemistry<br data-attributes="%20/" />400,4002,401,Coffee for Me University,Faculty of English Literature,Russian Literature in the 19th Century<br data-attributes="%20/" />400,4003,401,Coffee for Me University,Faculty of English Literature,Proto-Indo European Folklore (Thesis)<br data-attributes="%20/" />400,4004,401,Coffee for Me University,Faculty of English Literature,Introduction to English Literature<br data-attributes="%20/" />400,4005,402,Coffee for Me University,Faculty of Natural Sciences,Nomenclature of Organic Compounds<br data-attributes="%20/" />400,4006,403,Coffee for Me University,Faculty of History,Monarchies Through the Ages<br data-attributes="%20/" />500,5001,504,Asymmetrical University of Aesthetics,Mathematics & Computing,Polynomial Differentiation<br data-attributes="%20/" />500,5002,501,Asymmetrical University of Aesthetics,Modern Arts & Literature,Ancient Greek Classics<br data-attributes="%20/" />500,5003,501,Asymmetrical University of Aesthetics,Modern Arts & Literature,Introduction to Renaissance Literature<br data-attributes="%20/" />500,5004,502,Asymmetrical University of Aesthetics,Hard Sciences,Quantum Mechanics<br data-attributes="%20/" />500,5005,504,Asymmetrical University of Aesthetics,Mathematics & Computing,Introduction to Algebraic Topology<br data-attributes="%20/" />500,5006,501,Asymmetrical University of Aesthetics,Modern Arts & Literature,Magic Realism in Latin American Literature<br data-attributes="%20/" />600,6001,602,University of Cold-pressed Flannel,Department of Science,Radiation and Thermonuclear Reactions<br data-attributes="%20/" />600,6002,602,University of Cold-pressed Flannel,Department of Science,Kinematics and Dynamics<br data-attributes="%20/" />600,6003,603,University of Cold-pressed Flannel,Department of History,Rise and Fall of the Mughal Empire<br data-attributes="%20/" />600,6004,603,University of Cold-pressed Flannel,Department of History,Neo-Classical Architecture Through the Ages<br data-attributes="%20/" />600,6005,602,University of Cold-pressed Flannel,Department of Science,Cardiovascular System Processes<br data-attributes="%20/" />600,6006,604,University of Cold-pressed Flannel,Department of Mathematics,Polynomial Field Theory
−
100,1006,Totally Totes College,Advanced Music
+
[[Category:Tools]]
−
200,2001,Obviously OBVI College,Introduction to Art
 
−
200,2002,Obviously OBVI College,Intermediary Art
 
−
200,2003,Obviously OBVI College,Advanced Art
 
−
200,2004,Obviously OBVI College,Introduction to Music
 
−
200,2005,Obviously OBVI College,Intermediary Music
 
−
200,2006,Obviously OBVI College,Advanced Music
 
−
300,3001,Artisanal Art College,Introduction to Art
 
−
300,3002,Artisanal Art College,Intermediary Art
 
−
300,3003,Artisanal Art College,Advanced Art
 
−
300,3004,Artisanal Art College,Introduction to Music
 
−
300,3005,Artisanal Art College,Intermediary Music
 
−
300,3006,Artisanal Art College,Advanced Music
 
−
400,4001,Coffee for Me University,Introduction to Art
 
−
400,4002,Coffee for Me University,Intermediary Art
 
−
400,4003,Coffee for Me University,Advanced Art
 
−
400,4004,Coffee for Me University,Introduction to Music
 
−
400,4005,Coffee for Me University,Intermediary Music
 
−
400,4006,Coffee for Me University,Advanced Music
 
−
500,5001,Asymmetrical University of Aesthetics,Introduction to Art
 
−
500,5002,Asymmetrical University of Aesthetics,Intermediary Art
 
−
500,5003,Asymmetrical University of Aesthetics,Advanced Art
 
−
500,5004,Asymmetrical University of Aesthetics,Introduction to Music
 
−
500,5005,Asymmetrical University of Aesthetics,Intermediary Music
 
−
500,5006,Asymmetrical University of Aesthetics,Advanced Music
 
−
600,6001,University of Cold-pressed Flannel,Introduction to Art
 
−
600,6002,University of Cold-pressed Flannel,Intermediary Art
 
−
600,6003,University of Cold-pressed Flannel,Advanced Art
 
−
600,6004,University of Cold-pressed Flannel,Introduction to Music
 
−
600,6005,University of Cold-pressed Flannel,Intermediary Music
 
−
600,6006,University of Cold-pressed Flannel,Advanced Music
 

Latest revision as of 10:43, 17 May 2022

Overview

The Custom Lookup Options is a feature that allows you to import custom datasets for lookups and validation based on client-specific needs. For example, you can upload a CSV file whose data can be used as options within a select-type custom field such as Select One – Dropdown, Select One - Radio Button, or Lookup - Autocomplete Options.

Custom lookups can be used to provide large selection sets for your users, such as item inventory codes, code dictionaries, or geographic lookups. You can also easily setup dynamic sub-filtering conditions across multiple fields. For example, you could set up parent and child fields for selecting the country, university, faculty, and courses where the available options for each subsequent field is dependent upon the value of a previous field. This new functionality is advantageous since you don't need any of the data to exist as records in your system (as with the current Enable Dynamic Content option within a custom field) and it will be easier to update the options by importing them rather than editing large amounts of text in the custom field Edit screen.

This article will show you how to import a custom lookup, update a lookup, and the various ways it can later be referenced and used on your instance. You will need to be a Global Administrator to access these settings.


Configuration - Essentials

As an example, let's upload a custom lookup CSV containing university and course information. A sample of UniversityCourseLookup.csv is included in the appendix at the end of this article.

Importing a New Custom Lookup

  1. Navigate to Global Settings > Custom Lookup Options and click on the Import Custom Lookup Options button represented by an import icon.
    2021-07-ticket-124709-1.png
  2. You will now access step 1 one of the Custom Lookup Options Import wizard. Upload a CSV containing your custom data. The first row of the CSV must include column headers. A sample of UniversityCourseLookup.csv is included in the appendix at the end of this article.
  3. Give your custom lookup a descriptive name under Lookup Name that will help you remember what it is for. For our example, we will call our lookup "UniversityCourseLookup".
  4. Select one of the columns for the Lookup Key. The lookup key is a non-unique identifier that is associated with one or more rows in the table. In our example, our lookup key is the "University ID" because this number corresponds to the different universities in our dataset.
  5. The Primary Key is optional. A primary key is a unique identifier that corresponds to a single row in our table. For our example, the primary key is "Course ID" because each row in the table has a uniquely different Course ID that distinguishes it from every other row. Click Next to continue.
  6. Check over the sample of the uploaded data to ensure it has been processed as intended. Toggle which columns you want to include in the final import. Click Next to proceed.
  7. The last step will let you review the final changes. Click Next to complete the import process.

Updating an Existing Custom Lookup

  1. To update an existing custom lookup, import the updated file and be sure to type out the exact name of the lookup you want to update in the Lookup Name field. Specify the lookup key and any primary key needed. Click Next to continue.
  2. You will see a warning that notifies you that an existing lookup with the same name already exists. All existing rows with that lookup name will be deleted and re-imported with the new updated data. Click Next to continue and click Yes on the alert pop-up.
  3. Click Next to finish the import.


Viewing Custom Lookup Data

To view the data of any custom lookup, navigate to Global Settings > Custom Lookup Options to see a list of imported lookups. Click the name of the lookup you want to see in more detail. This will allow you to see the uploaded data in a list view format organized by the Lookup Key.

A sample list view of an imported custom lookup.


The list of values under Lookup Value form an attribute-value pair of column values for the specified row. To see this data in more detail, click the icon on the far left marked Open on any row. Based on the expanded Lookup Value data, we can see that this specific row contains the values for our table columns "Course ID", "University", and "Course". 

Attribute-value pairs in the Lookup Value are stored in JSON format.

Configuration - Advanced

Setting Up a Standalone Dropdown List

The easiest way to make use of your imported custom lookup options is to use a standalone dropdown list. We will use our university course lookup as an example.

  1. Create a custom field of type Select One - Dropdown List.
  2. Toggle on Enable Dynamic Content
  3. You will now see a new toggle called Use Custom Lookup Options. Toggle this on.
  4. You will now see additional settings for the custom lookup. Under the Lookup Name dropdown, select the custom lookup you want to reference. Under Option Text Identifier, type the name of the lookup column whose values you want to populate in the dropdown. In our example, we want to expose the available course names in the dropdown so we will type the name of the column that contains that information: "Course".


Configuration settings for a standalone dropdown using a custom lookup.

Setting Up a Parent-Child Dropdown List

Custom lookups can be incorporated into dynamic dropdown lists using the parent and child relationship between data. After uploading a CSV for your custom data, you can set up a series of parent and child dropdown lists that can further filter user options based on previous selections.

Configuring the Parent Dropdown

  1. Create a custom field of type Select One - Dropdown List.
  2. In the Predefined Options field, map the various lookup keys of your custom lookup data to the options you want to surface in the dropdown. In our example, our lookup keys span a range of 100 to 600, with each 100 increment corresponding to a different university. Mapping the lookup keys in this way will allow the child dropdown we set up later to be filtered by the selected lookup key. For example, if the user selects "Totally Totes College" (with a lookup key mapped to 100), the corresponding child dropdown options will filter the custom lookup data by this lookup key and only return the courses available to that university.


Configuration for a parent dropdown list. Note the first item has been set to "--Select One--" to make it the default option.



Configuring the Child Dropdown

  1. Create a custom field of type Select One - Dropdown List and set the Parent Field to the parent dropdown list created above.
  2. Toggle on Enable Dynamic Content
  3. You will now see a new toggle called Use Custom Lookup Options. Toggle this on.
  4. You will now see additional settings for the custom lookup. Under the Lookup Name dropdown, select the custom lookup you want to reference. Under Option Text Identifier, type the name of the lookup column whose values you want to populate in the dropdown. In our example, we want to expose the available course names in the dropdown so we will type the name of the column that contains that information: "Course".


Configuration settings for the child dropdown.

End Result of Parent-Child Dropdown

The available options within the child dropdown will now change depending on the selections made in the parent dropdown. In our example, changing the university in the parent dropdown will change the available courses we can select in the child dropdown.

2021-07-ticket-124709-4.png

Setting Up Successive Dropdown Lists

In the previous example, all the predefined options had to be hardcoded into to the custom field configuration settings. This could be a laborious process if the custom data needs regular updates or new entries. The process can be simplified by importing multiple lookups. The section will illustrate how to use successive dropdown lists and reference multiple custom lookups without having to hardcode predefined options.

As an example, we want to set up a chain of parent-child dropdowns for course registration. We want the user to first select from a list of available universities, then select from a list of available faculties at that selected university, and lastly, we want the user to select from a list of available courses under that university faculty.

All referenced CSV files are provided in the appendix below.

Configuring the Parent Dropdown

  1. Import UniversityLookup.csv with the "University ID" as the Lookup Key and the "Primary Key" as the Primary Key. Set Lookup Name to "UniversityLookup".
  2. Create a custom field of type Select One - Dropdown List and give it the caption "University".
  3. Toggle on Enable Dynamic Content and Use Custom Lookup Options.
  4. Under Lookup Name, select "University Lookup". Under Option Text Identifier, type the name of the lookup column you want to surface. For our example, we type "University".


Configuration settings for the university dropdown.


Configuring the First Child Dropdown

  1. Import FacultyLookup.csv with the "University ID" as the Lookup Key and the "Faculty ID" as the Primary Key. Set Lookup Name to "FacultyLookup".
  2. Create a custom field of type Select One - Dropdown List and give it the caption "Faculty".
  3. Set the Parent Field to previously created "University" dropdown custom field.
  4. Toggle on Enable Dynamic Content and Use Custom Lookup Options.
  5. Under Lookup Name, select "FacultyLookup". Under Option Text Identifier, type the name of the lookup column you want to surface. For our example, we type "Faculty".


Configuration settings for the faculty dropdown.


Configuring the Second Child Dropdown

  1. Import UniversityCourseLookup.csv with the "Faculty ID" as the Lookup Key and the "Course ID" as the Primary Key. Set Lookup Name to "UniversityCourseLookup".
  2. Create a custom field of type Select One - Dropdown List and give it the caption "Course".
  3. Set the Parent Field to previously created "Faculty" dropdown custom field.
  4. Toggle on Enable Dynamic Content and Use Custom Lookup Options.
  5. Under Lookup Name, select "UniversityCourseLookup". Under Option Text Identifier, type the name of the lookup column you want to surface. For our example, we type "Course".


Configuration settings for the course dropdown.


End Result of Successive Parent-Child Dropdowns

The available options within the child dropdown will now change depending on the selections made in the parent dropdown. In our example, changing the university in the parent dropdown will change the available faculty listings in the subsequent faculty dropdown. The user's selection in this faculty dropdown will then filter the available options in the course dropdown.


Successive parent and child dropdowns.

Settings Explained

Import Settings

Option Description
Lookup Name The Lookup Name is used to identify the set of lookup options. If you want to update an existing custom lookup, ensure the Lookup Name is the same as the existing custom lookup.
Lookup Key The Lookup Key is a non-unique identifier used to return all rows with a matching Lookup Name.
Primary Key (Optional) The Primary Key is an optional unique key used to return a single row that matches the Lookup Name and Primary Key value. Rows without a matching Primary Key will be imported as new rows.

Configuration Settings

Option Description
Option Text Identifier The name of the column header from your custom dataset whose values you want to display in the dropdown.
Option Value Identifier (Optional) The name of the column header from your custom dataset whose values are unique for each row. If a Primary Key was defined when importing the custom lookup, you can leave this field blank.

Appendix

UniversityLookup.csv

University ID,Primary Key,University
100,100,--- Please Select ---
100,100,Totally Totes College
200,200,Obviously OBVI College
300,300,Artisanal Art College
400,400,Coffee for Me University
500,500,Asymmetrical University of Aesthetics
600,600,University of Cold-pressed Flannel

FacultyLookup.csv

University ID,Faculty ID,Faculty,University
100,100,--- Please Select ---,--- Please Select ---
100,101,Arts & Literature ,Totally Totes College
100,102,Natural Sciences,Totally Totes College
100,103,Humanities,Totally Totes College
100,104,Mathematics,Totally Totes College
200,201,English Literature,Obviously OBVI College
200,202,Physical Sciences,Obviously OBVI College
200,203,History & Humanities,Obviously OBVI College
200,204,Mathematics & Computer Science,Obviously OBVI College
300,301,Literature ,Artisanal Art College
300,302,Biology, Chemistry, and Physics,Artisanal Art College
300,303,History, Anthropology, and Psychology,Artisanal Art College
300,304,Mathematics & Philosophical Sciences,Artisanal Art College
400,401,Faculty of English Literature,Coffee for Me University
400,402,Faculty of Natural Sciences,Coffee for Me University
400,403,Faculty of History,Coffee for Me University
400,404,Faculty of Mathematics & Computing,Coffee for Me University
500,501,Modern Arts & Literature,Asymmetrical University of Aesthetics
500,502,Hard Sciences,Asymmetrical University of Aesthetics
500,503,Modern History & Anthropology,Asymmetrical University of Aesthetics
500,504,Mathematics & Computing,Asymmetrical University of Aesthetics
600,601,Department of English,University of Cold-pressed Flannel
600,602,Department of Science,University of Cold-pressed Flannel
600,603,Department of History,University of Cold-pressed Flannel
600,604,Department of Mathematics,University of Cold-pressed Flannel

UniversityCourseLookup.csv

University ID,Course ID,Faculty ID,University,Faculty,Course
100,1000,100,--- Please Select ---,--- Please Select ---,--- Please Select ---
100,1001,101,Totally Totes College,Arts & Literature,Gothic Literature in the 19th Century
100,1002,102,Totally Totes College,Natural Sciences,Organic Compounds in Chemistry
100,1003,103,Totally Totes College,Humanities,Introduction to Modern History
100,1004,104,Totally Totes College,Mathematics,Introduction to Calculus 
100,1005,101,Totally Totes College,Arts & Literature,Comparitive Mythology & Folklore
100,1006,101,Totally Totes College,Arts & Literature,Introduction to Latin American Literature
200,2001,202,Obviously OBVI College,Physical Sciences,Fluid Dynamics in Brownian Motion
200,2002,204,Obviously OBVI College,Mathematics & Computer Science,Advanced Lorentz Transformations
200,2003,201,Obviously OBVI College,English Literature,Analysis of Arthurian Legend
200,2004,202,Obviously OBVI College,Physical Sciences,Morphology in Evolution
200,2005,202,Obviously OBVI College,Physical Sciences,Thermodynamics
200,2006,203,Obviously OBVI College,History & Humanities,Early Modern Period
300,3001,303,Artisanal Art College,History, Anthropology, and Psychology,Architecture of the Roman Empire
300,3002,302,Artisanal Art College,Biology, Chemistry, and Physics,Anatomy of the Human Body
300,3003,304,Artisanal Art College,Mathematics & Philosophical Sciences,Topology of Non-Euclidean Systems 
300,3004,301,Artisanal Art College,Literature ,Ancient Epics and Poetry
300,3005,303,Artisanal Art College,History, Anthropology, and Psychology,History of the Dutch Golden Age
300,3006,302,Artisanal Art College,Biology, Chemistry, and Physics,Virology and Epidemiology
400,4001,402,Coffee for Me University,Faculty of Natural Sciences,Inorganic Compounds in Chemistry
400,4002,401,Coffee for Me University,Faculty of English Literature,Russian Literature in the 19th Century
400,4003,401,Coffee for Me University,Faculty of English Literature,Proto-Indo European Folklore (Thesis)
400,4004,401,Coffee for Me University,Faculty of English Literature,Introduction to English Literature
400,4005,402,Coffee for Me University,Faculty of Natural Sciences,Nomenclature of Organic Compounds
400,4006,403,Coffee for Me University,Faculty of History,Monarchies Through the Ages
500,5001,504,Asymmetrical University of Aesthetics,Mathematics & Computing,Polynomial Differentiation
500,5002,501,Asymmetrical University of Aesthetics,Modern Arts & Literature,Ancient Greek Classics
500,5003,501,Asymmetrical University of Aesthetics,Modern Arts & Literature,Introduction to Renaissance Literature
500,5004,502,Asymmetrical University of Aesthetics,Hard Sciences,Quantum Mechanics
500,5005,504,Asymmetrical University of Aesthetics,Mathematics & Computing,Introduction to Algebraic Topology
500,5006,501,Asymmetrical University of Aesthetics,Modern Arts & Literature,Magic Realism in Latin American Literature
600,6001,602,University of Cold-pressed Flannel,Department of Science,Radiation and Thermonuclear Reactions
600,6002,602,University of Cold-pressed Flannel,Department of Science,Kinematics and Dynamics
600,6003,603,University of Cold-pressed Flannel,Department of History,Rise and Fall of the Mughal Empire
600,6004,603,University of Cold-pressed Flannel,Department of History,Neo-Classical Architecture Through the Ages
600,6005,602,University of Cold-pressed Flannel,Department of Science,Cardiovascular System Processes
600,6006,604,University of Cold-pressed Flannel,Department of Mathematics,Polynomial Field Theory
Retrieved from ‘https://wiki.smartsimple.com/index.php?title=Custom_Lookup_Options&oldid=39801’