When setting up any configurator whether the primary goal is simple CAD automation, or you want to tie everything together with a full blown CPQ system, you’ll likely be working with lots of data. Let’s review the most efficient way to store data, and present complex data in an easy-to-understand way on the front end. This blog is going to cover some of the fundamental functions needed when working with tabular data in DriveWorks.
Fundamental Functions
Below are some key functions that every DriveWorks data wizard should know! Each function has a specific purpose and can tremendously help simplify a complex implementation when used correctly. Let’s start with the most obvious, and possibly the most used function when working with tables – the “lookup”.
VLookup & DWVLookup
The VLookup command returns a value from a table corresponding to a match found when searching the first column of that table. The VLookup searches the first column in the selected table for a match and if a match is found, the data from the specified column in the matching row is returned. This is a really easy way to pull any relating data from a table easily.
A DWVLookup does the exact same thing as a VLookup with one major difference, it allows you to specify the search column as well as the lookup column, meaning you’re not just limited to the first column when looking for a match!
Learn more about VLookup and DWVLookup >>
| Make | Model | Color |
|---|---|---|
| Volkswagen | Golf | Blue |
| Ford | Escort | Red |
| Renault | Kangoo | White |
| Mazda | 2 | Black |
A VLookup would allow you to search the table above for “Ford”, and then return the model or color of that vehicle – such as Escort, or Red.
A DWVLookup would allow you to search any column (such as “Model”) and then return a match. So instead of always searching in column one, we can search for a list of vehicle models, and return the make, or color! This is great when trying to match a customer name with an email, or address.
TableGetValue
The TableGetValue function is a great way to pull specific information from a table. This function gets the value from the specified data row and column of the given table. In our sample table above, using the TableGetValue function, we can pull a specific value from the table by specifying which row and column we want, for instance, row four column three will return “Black”. When creating an interactive user form in DriveWorks that has lots of information, sometimes it is helpful to display a data table on the form that lets customers scroll through the data and select a specific row. Using the return value of the data table, you can easily plug that number into the TableGetValue function to dynamically update what data is being pulled from a table! In the example below, that’s exactly what is happening. You can select information from a filtered table containing large amounts of data, and then easily extract the specific information needed, in this case, make and year.
Learn more about TableGetValue >>
ListAllConditional
Often times when you have a table full of data, one thing that’s needed is the ability to display a list of everything in a column. This can help with making selections or showing a complete list of current information in the table. The ListAllConditional function takes this concept one step further, and allows the user to specify a specific condition for what is to be listed. For instance in the table below, perhaps we want to list all of the vehicles that are “Red”. We can simply use the ListAllConditional function, to list all vehicle “makes” that have the color “Red” in the third column.
Above you see the raw data. Below, you can see the result after the ListAllConditional function is applied.
ListAllConditional(DwLookupSampleData,1,3,“Red”)
The resulting list above shows all the results that meet the condition “Red” for the vehicle makes. This is a great way to filter and extract matching data.
Learn more about ListAllConditional >>
More Table Functions
There are countless table functions that can be used in DriveWorks to make working with complex data easy. Check out our webinar that breaks down how to work with tables within DriveWorks for even more tips and tricks. If you want to learn more about specific table functions click the above help file link for some in depth content.
