Databases 2: Populating and Querying


In this walkthrough, we'll create a database table and use it as a lookup source in our configurator UI.  

After confirming you can connect to your database, you can create database tables or views for use with your configurator.  Here are some use cases:

  • Reading and Writing
    Consider a list of existing part numbers and configurations.  By reading from this table, you can determine if the current configuration on the user's screen has already been built some time before, and already has a unique part number.  And by writing to this table, such as when a user submits a configuration that has no existing part number, you can ensure this table of information is up-to-date.  It could be useful for an integration to your ERP system, for example.
  • Reading only
    You may want to store a table of postal code information, so your users only have to enter a postal code (the corresponding city or town name will appear to confirm their choice).  This list doesn't change, so your configurator will only read the data from this reference table, and not write any information back.  
  • Writing only
    You may want to log in a database table when certain events occur, so you can test out enhancements to your user interface.  For example, you can stamp the time that your user clicks on each page in your configurator, to see how much time is spent on each page.


This walkthrough will guide you through this second use case: we will add a new "Shipping Address" section to our configurator.


Before you begin...

  • Be familiar with the basic concept of database tables, as described on the database tables page.
  • Have some familiarity with SQL, the language used to work with databases.
  • Have successfully connected to your database using a database tool, such as SSMS or Azure Data Studio.


Clarify the business need

Let's use the following hypothetical scenario.  Currently, our configurator has two fields where users manually type in the shipping city and shipping postal code.  Sometimes they may not spell the city correctly, or they may enter a zip code that doesn't match the city.  These mistakes result in delivery delays.  We need to change the configurator, so the process is easier and faster for our users, and more accurate for our deliveries.

To do so, we'll make two changes.  First, to capture the "Postal Code", we'll use the text mask feature available on text fields.  This helps ensure that only good data is entered by the user.  Second, for the "City" field we'll use a database query to find the correct city name for the postal code that was selected.  The user won't have to edit this field at all: it will be populated for them.  By displaying the city name related to the postal code they entered, they can confirm their data entry is accurate.

Mock up the configurator user interface

  1. Create a "Shipping Zip Code" field: data type text, control textbox,  textbox mask zip code.
    (If your postal codes are not the simple 5 digits found in the US, you can use a field rule instead of this mask, and implement any business logic you like.)
  2. Create a "Shipping City" field: data type text, control textbox, enabled false.

Create your database table

Use the database tool to create your table in the X schema of your database.  Many techniques for creating tables are explained in the SSMS documentation, but for this example we will use a simple technique to create our table.

  1. Copy this text:

    Create table SQL
    SET ANSI_NULLS ON 
    GO 
    SET QUOTED_IDENTIFIER ON 
    GO 
    CREATETABLE [x].[PostalCodesUS]( 
        [PostalCode] [nvarchar](max) NOTNULL
        [City] [nvarchar](max) NOTNULL
        [StateName] [nvarchar](max) NOTNULL
        [StateCode] [nvarchar](max) NOTNULL 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY
    GO 
  2. Launch your database tool, and connect to your database if you haven't already. Learn how to use SSMS or Azure Data Studio to connect.)

  3. Using MSSMS:
    Right-click your black database cylinder icon, and select "new query". Paste the text into the query window, and press the "execute" icon in the toolbar.  If the messages pane says "Commands completed successfully" then the table has been built. Changes to the database (like this one we just did) do not appear in the tree until it is refreshed. Right-click the same black database cylinder icon, and select "Refresh" to see the new table.


    Using Data Studio:
    Right-click the cylinder icon for your database, and select "new query". Paste the text into the query window, and press the "Run" icon in the toolbar.  If the messages pane says "Commands completed successfully" then the table has been built. Changes to the database (like this one we just did) do not appear in the tree until it is refreshed. Right-click the same database cylinder icon, and select "Refresh" to see the new table.


Load your database table with data (using SSMS)

Now that your table exists, let's fill it with data.  Tables can be loaded through ETL tools, SSMS, or even through Snap code you write.  For this example, we'll use SSMS to load the data, but Data Studio also has an import extension.  

  1. Download this CSV file of freely-available public information to your computer: PostalCodesUS.csv
  2. Right-click on the cylinder database icon, and choose Tasks > Import Data.  A new Import and Export Wizard appears.


  3. For data source, select "Flat File Source".
  4. Click the "Browse" button next to the file name field, and find the zipcodes.csv file you downloaded from step 1. 
    (Note: when browsing for the file, the file browser window may be only showing you text files.  Select "CSV files" from the drop-down list above the Open button to see the CSV file.)  After selecting the file, the wizard window should look something like this:


  5. Click "Next" – you'll be asked to confirm the row and column delimiters used to parse this CSV file.  Usually this is set correctly for you.  If you see your data appear correctly in the preview rows, you know your delimiters are set correctly.  


  6. Click "Next" – and choose your destination. Select "Sql Server Native Client", and enter your server name, username, password, and database.


  7. Click on the destination parameter, and select [x].[PostalCodesUS]. 
    Note: SSMS tries to select the destination for us automatically.  However, ensure the destination is in the x schema, not the dbo schema.  If it is not set correctly, just click on it and scroll until you find [x].[PostalCodesUS].  Do not use some other table it may suggest, like [dbo].[PostalCodesUS].

    (Since the columns in our table exactly match the columns in the CSV file, we don't need to map them.  In other situations, you would click "Edit Mappings" to map your source and destination columns.)
  8. In the "Review Data Type Mapping" page of the wizard, and all following pages, click "Next" until the "Finish" button appears.
  9. Click "Finish", and the load process will start.  The wizard will display various stages of the process to you.
  10. When it finishes, confirm that 74,022 rows have been loaded.  Then click "Close".


Load your database table with data (using Data Studio)

You can perform the same data load with Azure Data Studio.  

  1. Download this CSV file of freely-available public information to your computer: PostalCodesUS.csv
  2. In Azure Data Studio, right-click on the cylinder database icon, and choose Import Wizard.  A new Import Wizard appears. Enter the parameters and click "Next".

    • Use the same Server and Database settings provided for your Data Studio initial setup.
    • Browse to the location of the CSV file you downloaded.
    • Here, we give the table name "PostalCodesUS"
    • All tables and views you create are in the "x" schema. Don't use the "dbo" schema.
  3. Preview the data and click "Next".
  4. Modify the columns as necessary to match your data. Usually, "nvarchar(50)" is best for text columns, and INT for integers. Consult with a database administrator at your company for guidance on the best column types to use for fastest performance.
  5. Click "Done" to start the import, and watch for informative messages.

 

Confirm the data in the table

You can review the data in your table by finding the [x].[PostalCodesUS] table in the tree, and right-clicking it to see a pop-up menu.  Choose "Select top 1000 rows" to see a preview of your table appear.

 

Create a safe function to query the database table

Now that you have created the table and filled it with data, it's a simple process to write a safe function to get the results you need. (The simple example here works, but see the wiki entry on safe functions for more information and best practices on writing safe functions.)

  1. Open your Epicor CPQ administrative interface.
  2. Click Resources > Safe Functions.  Click new.  A new safe function workspace appears.
  3. In the workspace, create this function:

  4. Test the safe function by saving it, then clicking the test button at the bottom of the screen. Enter a postal code, and you should see a result.

 

Call the safe function in a value rule to populate your field

Now that the safe function is built and tested, let's use it. 

  1. Open your configurator, and ensure you have the two shipping fields described above.
  2. create a new value rule as shown here. Note the logic which surrounds the call to the safe function.  The safe function is called only if the contents of the "Shipping Zip Code" field has changed.  Database queries can take a few seconds to return results, depending on network speeds, so why run the query over and over if we don't have to?


  3. Test your work.
    • In "Shipping Zip Code" you should only be able to enter 5 numbers: no letters, spaces, or other erroneous characters.
    • You should see the Shipping City update, every time you change the Shipping Zip Code.


Related articles


Was this article helpful?