Using SQL to Retrieve Extender List Data Type Values

Using SQL to Retrieve List Data Type Values

I’ve seen cases where customers have asked how the data for lists is stored in the Extender tables.

If you’re using SQL to retrieve list data type values, here’s everything you need to know to completely understand the process.

This is the table structure of the extender.
https://www.eonesolutions.com/help-article/extender-table-structures/

For Example: Extender Window >> Data Tables

Example: I want to retrieve the data for the field “Item Category 1″ from the Extender window “ITEMCAT”, which is defined on the GP Item Maintenance window.

 
To retrieve the list value from Extender, follow these steps:

1. In Microsoft SQL Server Management Studio, we need to find the List ID for the list we are looking for. Replace the XX in the statement below with your List Fiel name. In this example, it would be the “Item Category 1” list.

select * from EXT20020 where Extender_List_Desc = 'XX' -- Extender List Master Table

2. With the List ID found in step 1, you can find the values available for this list in the EXT20021 table. Replace the XX in the statement below with your list ID from step 1.

select * from EXT20021 where Extender_List_ID = XX -- Extender List Item Master Table

3. Now you need to find the Field ID for the field on the window you are using. To do this, you first need to note the field position. In our example, the “Item Category 1” is Field 1. If your window has two columns of fields, it goes down the first row 1-15 and then the second row would be 16-30, so for example, a field in the Field 2 row of column 2 would be Field 17.
4. Once you know the Field Position, we can look in the EXT20100 table for the Field ID. In the script below, replace the XX with the Field Position found in step 3. Replace the YY with the Window ID of your window. In our example, it would be ITEMCAT.

select Extender_Window_ID, Extender_Window_Name, Extender_Field_IDs_XX from EXT20100 where Extender_Window_ID = 'YY'

5. Once you have the List ID (step 2) and Field ID (step 4), you can include it in the join. You will want to replace the XX with the List ID from step 2 and the YY with the Field ID from step 4.

SELECT 
	 T1.Extender_Window_ID,
	 T1.Extender_Key_Values_1 AS 'Key 1',
	 T1.Extender_Key_Values_2 AS 'Key 2',
	 T1.Extender_Key_Values_3 AS 'Key 3',
	 T1.Extender_Key_Values_4 AS 'Key 4',
	 T1.Extender_Key_Values_5 AS 'Key 5',
	 T3.STRNG132 AS 'List Value'
FROM [dbo].[EXT01100] T1 
INNER JOIN [dbo].[EXT01103] T2 ON T1.Extender_Record_ID = T2.Extender_Record_ID 
LEFT OUTER JOIN [dbo].[EXT20021] T3 ON T3.Extender_List_Item_ID = T2.TOTAL
WHERE T3.Extender_List_ID = XX AND T2.Field_ID = YY 

6. At this point, you can add your additional Microsoft Dynamics GP tables or whatever else you may like to the script to get the data you need. You have the Keys in the Key 1-5 fields included to link to your GP data. In our example, it would be linking the Key 1 to the Item Number in something like the Item Master table.

This can be a bit technical if you’re not familiar with the Extender table structure. With that being said, eOne does recommend that you use the Views option built into Extender to pull this data together for you. Not only is it easier to just have Extender create it for you, but if something new is added in an upgrade, the views will get recreated with that new feature/change in them. If you create your scripts on your own, they will not get the new features/changes without your intervention.

Have a question? Please reach out to us at support@eonesolutions.com