© 2018 by PDG Consultants, Inc. 

Tel: 1-727-510-4558

Office: Clearwater, Florida
Please reload

Recent Posts

Debugging Extender Imports with SmartConnect

April 3, 2016

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.

 

We re-run the import process and find a different value not in the list for the THICKNESS column, which is defined as Row 4 on the Extender form.

Looking at our list for Row 4 [THICKNESS], we see that 1″ value isn’t there. We add it and re-run the import process.

 

 Re-Running the import another error occurs when a value in the SHAPE column (Row 8) was misspelled.

Re-run the import process and find the OPTION source data was mistakenly mapped to the EDGE destination column.

 Correct the Destination mapping.

 Fixed the row mis-mapping and – finally, no errors. The import works perfectly! OMG

 

 

Even with a tool - the process is not A to B. Without it – good luck!

Share on Facebook
Share on Twitter
Please reload

Follow Us
Please reload

Search By Tags
Please reload

Archive
  • Facebook Basic Square
  • Twitter Basic Square
  • Google+ Basic Square