Debugging Extender Imports with SmartConnect

Trying to import data from Excel or .csv files into an Extender form sometimes works without a hitch, so I have heard. Not my experience, since I wasted 30 unproductive (un-billable) hours last week trying to get 4098 records into an Extender form unsuccessfully. I am pretty good at SQL, so I’ll try a “simple” SQL INSERT. Fuggedaboutit!

Extender has a very complex data design. You can verify that by looking at the construction of any Extender Data View.

create view [dbo].[ATTRIBUTES] as select A1.ATTRIBUTESID_Item# AS ‘Item #’, A1.ATTRIBUTESDesc_Description AS ‘Description’, (select STRNG132 from EXT20021 where Extender_List_ID = 1 and Extender_List_Item_ID = A1.ATTRIBUTES_100_Category) AS ‘Category’, (select STRNG132 from EXT20021 where Extender_List_ID = 2 and Extender_List_Item_ID = A1.ATTRIBUTES_101_InnerDiam) AS ‘Inner Diam’, (select STRNG132 from EXT20021 where Extender_List_ID = 3 and Extender_List_Item_ID = A1.ATTRIBUTES_102_OuterDiam) AS ‘Outer Diam’, (select STRNG132 from EXT20021 where Extender_List_ID = 4 and Extender_List_Item_ID = A1.ATTRIBUTES_103_Thickness) AS ‘Thickness’, (select STRNG132 from EXT20021 where Extender_List_ID = 5 and Extender_List_Item_ID = A1.ATTRIBUTES_104_Kerf) AS ‘Kerf’, (select STRNG132 from EXT20021 where Extender_List_ID = 6 and Extender_List_Item_ID = A1.ATTRIBUTES_105_ShankSize) AS ‘Shank Size’, (select STRNG132 from EXT20021 where Extender_List_ID = 7 and Extender_List_Item_ID = A1.ATTRIBUTES_106_CEGrade) AS ‘CE Grade’, (select STRNG132 from EXT20021 where Extender_List_ID = 8 and Extender_List_Item_ID = A1.ATTRIBUTES_107_Shape) AS ‘Shape’, (select STRNG132 from EXT20021 where Extender_List_ID = 9 and Extender_List_Item_ID = A1.ATTRIBUTES_108_RotationFace) AS ‘Rotation/Face’, (select STRNG132 from EXT20021 where Extender_List_ID = 10 and Extender_List_Item_ID = A1.ATTRIBUTES_109_Edge) AS ‘Edge’, (select STRNG132 from EXT20021 where Extender_List_ID = 11 and Extender_List_Item_ID = A1.ATTRIBUTES_110_Option) AS ‘Option’, (select STRNG132 from EXT20021 where Extender_List_ID = 12 and Extender_List_Item_ID = A1.ATTRIBUTES_111_Cel) AS ‘Cel’, (select STRNG132 from EXT20021 where Extender_List_ID = 13 and Extender_List_Item_ID = A1.ATTRIBUTES_112_CelMetric) AS ‘Cel Metric’, (select STRNG132 from EXT20021 where Extender_List_ID = 14 and Extender_List_Item_ID = A1.ATTRIBUTES_113_Height) AS ‘Height’, (select STRNG132 from EXT20021 where Extender_List_ID = 15 and Extender_List_Item_ID = A1.ATTRIBUTES_114_Pockets) AS ‘Pockets’, (select STRNG132 from EXT20021 where Extender_List_ID = 16 and Extender_List_Item_ID = A1.ATTRIBUTES_115_Wings) AS ‘Wings’, (select STRNG132 from EXT20021 where Extender_List_ID = 17 and Extender_List_Item_ID = A1.ATTRIBUTES_116_Teeth) AS ‘Teeth’, (select STRNG132 from EXT20021 where Extender_List_ID = 18 and Extender_List_Item_ID = A1.ATTRIBUTES_117_Radius) AS ‘Radius’, (select STRNG132 from EXT20021 where Extender_List_ID = 19 and Extender_List_Item_ID = A1.ATTRIBUTES_118_CuttingAngle) AS ‘Cutting Angle’, (select STRNG132 from EXT20021 where Extender_List_ID = 21 and Extender_List_Item_ID = A1.ATTRIBUTES_119_ToolType) AS ‘Tool Type’ from (select EXT01200.UD_Form_Field_ID as ATTRIBUTESID_Item#,EXT01200.UD_Form_Field_Desc as ATTRIBUTESDesc_Description, ATTRIBUTES_100_Category, ATTRIBUTES_115_Wings, ATTRIBUTES_101_InnerDiam, ATTRIBUTES_116_Teeth, ATTRIBUTES_102_OuterDiam, ATTRIBUTES_117_Radius, ATTRIBUTES_103_Thickness, ATTRIBUTES_118_CuttingAngle, ATTRIBUTES_104_Kerf, ATTRIBUTES_119_ToolType, ATTRIBUTES_105_ShankSize, ATTRIBUTES_106_CEGrade, ATTRIBUTES_107_Shape, ATTRIBUTES_108_RotationFace, ATTRIBUTES_109_Edge, ATTRIBUTES_110_Option, ATTRIBUTES_111_Cel, ATTRIBUTES_112_CelMetric, ATTRIBUTES_113_Height, ATTRIBUTES_114_Pockets from EXT01200 left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_100_Category from EXT01203 where Field_ID = 100) B100 on EXT01200.Extender_Record_ID = B100.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_115_Wings from EXT01203 where Field_ID = 115) B115 on EXT01200.Extender_Record_ID = B115.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_101_InnerDiam from EXT01203 where Field_ID = 101) B101 on EXT01200.Extender_Record_ID = B101.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_116_Teeth from EXT01203 where Field_ID = 116) B116 on EXT01200.Extender_Record_ID = B116.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_102_OuterDiam from EXT01203 where Field_ID = 102) B102 on EXT01200.Extender_Record_ID = B102.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_117_Radius from EXT01203 where Field_ID = 117) B117 on EXT01200.Extender_Record_ID = B117.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_103_Thickness from EXT01203 where Field_ID = 103) B103 on EXT01200.Extender_Record_ID = B103.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_118_CuttingAngle from EXT01203 where Field_ID = 118) B118 on EXT01200.Extender_Record_ID = B118.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_104_Kerf from EXT01203 where Field_ID = 104) B104 on EXT01200.Extender_Record_ID = B104.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_119_ToolType from EXT01203 where Field_ID = 119) B119 on EXT01200.Extender_Record_ID = B119.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_105_ShankSize from EXT01203 where Field_ID = 105) B105 on EXT01200.Extender_Record_ID = B105.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_106_CEGrade from EXT01203 where Field_ID = 106) B106 on EXT01200.Extender_Record_ID = B106.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_107_Shape from EXT01203 where Field_ID = 107) B107 on EXT01200.Extender_Record_ID = B107.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_108_RotationFace from EXT01203 where Field_ID = 108) B108 on EXT01200.Extender_Record_ID = B108.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_109_Edge from EXT01203 where Field_ID = 109) B109 on EXT01200.Extender_Record_ID = B109.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_110_Option from EXT01203 where Field_ID = 110) B110 on EXT01200.Extender_Record_ID = B110.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_111_Cel from EXT01203 where Field_ID = 111) B111 on EXT01200.Extender_Record_ID = B111.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_112_CelMetric from EXT01203 where Field_ID = 112) B112 on EXT01200.Extender_Record_ID = B112.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_113_Height from EXT01203 where Field_ID = 113) B113 on EXT01200.Extender_Record_ID = B113.Extender_Record_ID left join (select Extender_Record_ID, TOTAL as ATTRIBUTES_114_Pockets from EXT01203 where Field_ID = 114) B114 on EXT01200.Extender_Record_ID = B114.Extender_Record_ID where EXT01200.Extender_Form_ID = ‘ATTRIBUTES’) A1

Now you are beginning to believe me.

Enter SmartConnect. It is a data integration/mapping tool that works to or from most any data source (ODBC, text, XL, SQL, GP, etc.). It is the only tool that recognizes the Extender Form or Window structures.

This is the SmartConnect console, showing 3 maps that I just created: one for importing Inventory items into GP, one for importing vendors into GP, and another for importing SQL data into Extender. Our client owns a tool and die shop, where they manufacture cutting knives for woodworking tools. You can use SmartConnect to import real-time data – or bulk data load, like I was doing.

Once you have your source data defined, you create a data source configuration, pointing to your data.

Then you tell SmartConnect the Destination of this data, where’s it going. In this case an Extender form, using the GP eConnect API.

Then you Map the fields from Source to Destination, by dragging them from the left side of the window to the right.

Run the Map.

Import process runs with errors. SmartConnect tells you exactly what the error is and in what column it occurred. Row 1 on the Extender form is the CATEGORY column. The value “CUTTER” was not found in the list of acceptable values.

We look at our Extender form and indeed see that the value “CUTTER” is not in the list. So now we add it to the list and run the import process again.