Configurator lookup column set

Kind of confused with config lookup tables.

Each Lookup table has a Column set. And that column set has columns. Rows are added, and the intersection of the row and column is the data stored.

But the lookup functions only ever use the table name, column name and row name. None of them require the column set.

Can I have the same column name used in two different column sets of the same lookup table? How does the lookup function know which column set to use?

2 Likes

You can’t have two column sets for the same lookup table. You can have the same column set across multiple tables but that enforces uniqueness

1 Like

I use UDMethods to access the data in the lookup tables. That allows me to access the columns by the ā€˜name’.

EDIT

Apparently I didn’t read the original post, I was told by a consultant that the column sets are pointless and aren’t used in the system.

So a Column Set is a group of column definitions (col name, data type format, etc…). And if marked as template, it can be used in multiple lookup tables.

But each lookup table can only have 1 column set.

That all correct?

I thought the lookup table was akin to an Excel file. With the lookup table being like a workbook, column sets being worksheets, and rows and columns being rows and columns.

1 Like

Yep

But only one Workbook per company if we’re keeping up with the analogy.

1 Like

But my analogy was incorrect.

One more question, lookup tables aren’t tied to specific configurators, right?

2 Likes

No, only UD Methods.

I mean right. Lookup Tables are separate from Configurators. Think of them like UDxx(x) tables but combined into a single table. There is also Configurator Control Data which you would access with a Server Side UD Method but not a flexible as Lookup Tables.

Worth noting, Lookup Tables can take up a lot of ā€œwire-timeā€. It’s best to pull as much in with a single call instead of doing field lookups. You’ll REALLY feel it if accessing LU Tables across continents!

3 Likes

Hi @Mark_Wonsil

How would one go about pulling multiple values in a single call? I have a lookup table with data and i need to pull multiple values from the same table in one go.

I can do this in multiple calls but if its possible to do in one go that would be excellent!

Tony,
This would depend on the type of data within the table and how you are needing the data. Also, are you referring to a UD Table or a Lookup Table?

A single BAQ could return your data, or data from a Configurator UD Method, this really depends on how you intend on using the returned data. If you can provide more context on your use case, you will likely find a clearer answer.

Are you pulling a specific column or row? There are native methods for that.

There’s Data Column List

// returns ~ separated string of all values from the column 

string ColumnValues = PCLookUp.DataRowList( string Table, string Column );

/*
ColumnValues evaluates as

"Row1Value~Row2Value~Row3Value~Row4Value"
*/

and Data Row List

// returns ~ separated string of all values from a row specified by it's Key Value 
// Key value is  the value in the First Column from the desired row  

string ColumnValues = PCLookUp.DataRowList( string Table, string Row Key );

string RowValues = PCLookUp.DataRowList( string Table, string Column );

/*
RowValues evaluates as

"2ndColValue~3rdColValue~4thColValue~5thColValue"
*/

You can then get these into a list or array using the Split function:

var ValueList = ColumnValues.Split('~').ToList();

/*
Now ValueList is a list of the values from ColumnValues, and the individual cell values can be called with indexing

ValueList[0] == "Row1Value"
ValueList[1] == "Row2Value"
etc.
*/
1 Like

I have a 3 part adhesive and would like to return the qty required for each component,

I have 3 lookup tables (one for each component of adhesive)

Each table contains a different width of our product as well as a flat m2 rate.

I would like to return a value relating to the width selected as well as the flat m2 rate from all 3 tables.

I respect as i am calling 3 tables this may require 3 seperate calls which would be better than 6!

Not if they are interrelated and can be tied together within a BAQ to a single dataset you can then return this to your configurator.

This is alien territory to me, built BAQs before no issues there and i could certainly link together as they have a common reference…didnt even know you could pull a configurator lookup table to a BAQ!

Becasue this is new to me would you be able to give me some pointers?

The VALUES in all configurator lookup tables are here:

1 Like

Wow, this is a long time ago! So, what I did (back then) was to create a hidden input for each table I was downloading. I wrote a server side UD method that would read the PCLookUpTbleHed (and column sets maybe :thinking: ) and created a delimited string for the column headings. I think I also had a row delimiter to search the rows. Again, this was a while ago. I then created a delimited string with the data from PCLookupTblValues for that ā€œtable.ā€

The trick was Epicor does not enter a record for an empty cell, so I had to ensure there was a placeholder (extra delimiter) for the missing column.

This happened when the configurator opened. I then used a client UD method to pass in the table (the hidden input), the key for the row, and which column I wanted. I used the regular Epicor functions that processes delimited strings (index, etc.) to find the column number then then grab the associated column for the selected row. It was wicked fast compared to calling the PCLookup methods, especially from Germany when SaaS was in Iowa.

1 Like