Inserting Records into Extender

(0) Share

Report

Posted on by Richard Wheeler

75,848 Moderator

I was just handed a request to be able to mass modify Extender fields. I see no problem doing that but the issue I see is in the beginning when there are no Extender values entered. Here, you would be inserting records into the Extender tables. In this case, they are adding Extender fields to their inventory items. They are using Extender fields for things like color, size and weight. So they want to do mass change based on the part number prefix. I do not think it is a good idea to go about inserting records externally into the Extender tables. One thought I has was to do an import using Extender with all blank values then after that it would be all updates.

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (1)

steveendow 2,281 on at

Like (0)

Report

Hi Richard,

I have done several integrations that involved Extender fields, and the integration inserts and updates the Extender records without issue.

What is your concern with inserting Extender records?

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

Given the array nature of Extender I do not it as simple as just inserting a record into one table. I am concerned about all the table relationships. Will I get all the tables that are necessary.

Was this reply helpful? Yes No
Verified answer

Josh P 2,895 on at

Like (0)

Report

This script will give all the fields that are foreign keys to other tables with the parent table and column name. It should help you make sure you're hitting all the related tables.

You can also make a back up of the tables you intend to insert data to, and if the process becomes broken, simply reload the original tables.

SELECT

      f.name AS ForeignKey,

      OBJECT_NAME(f.parent_object_id) AS TableName,

      COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

      SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,

      OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

      COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f

      INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

      INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

where OBJECT_NAME(f.parent_object_id) = 'RM00105'

Was this reply helpful? Yes No
Suggested answer

Howard Swerdloff 845 on at

Like (0)

Report

I have inserted records to the Extender tables using SQL insert statements and it works fine. If you are concerned doing that, the other option is to create a macro using mail merge in Word and run that. That way you can basically insert the records 1 by 1 and let GP do all the business checks.

Was this reply helpful? Yes No
Kevin Day 610 on at

Like (0)

Report

I have done a lot of extender work using integrations using eConnect and it is fairly simple to do. If you are only using Text fields, you only need to insert into 2 tables. EXT00100 and EXT00101. EXT00100 is the main record that Extender fields "exist" and all the other tables sort of link back to that one with your key fields put all together. So for example, I am using Sales Order Lines and my key fields are SOP Number and Line Number. The link between all of them is those 2 fields put together. INV0000116384. If you pick Test (Char(255)) then all those will be in the EXT00101. If you have Date fields, they will be in EXT00102, and so on. One note. I am using GP 10 and there is a bug in the View you can create from the Extender Views (as far as I am concerned) where it won't pick up any data unless your "First" field has a value in it. It is because the view doesn't left join back to the EXT00100. I'll elaborate more if anyone is interested.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

Here is my full situation. The IT director of my client has asked me to produce a sql script that will allow him to make changes to Extender fields for one particular Extender window. There are 10 fields on this window. These Extender fields are display fields for their web store. So if they need to change the color of a range of parts, he wants to pull up this script, change the Extender field that contains the color and Execute the script and voila, the entire range of colors will change on the web store. The more I think about this the more I want to wrap this into some VS C# code with calls to eConnect. What do you think? Any better suggestions? I need to make this thing foolproof!

Was this reply helpful? Yes No
steveendow 2,281 on at

Like (0)

Report

Hi Richard,

Based on what you described, I would agree that a C# WinForm utility would probably be a good option. If this is something they want to use on a regular basis, then a structured utility makes sense.

Was this reply helpful? Yes No
Kevin Day 610 on at

Like (0)

Report

My suggestion would be to have a Windows form that they can just upload a spreadsheet to that can do the updaing, similar to Integration Manager (or budgets). They can export the Item number and the Extender Values from Smartlist, make changes in Excel, then load it back up. Update the ones that exist and insert the ones that don't. Unless you wanted some functionality to also update real Item fields (like descriptions, pricing, etc...) you don't even need eConnect involved. As long as you write a good stored procedure that doesn't violate the data integrity of the keys fields, then it should be pretty solid and simple.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Richard, we do not recommend writing directly to the SQL tables for Extender as the tables using for Extender 2013 have changed and the update would not work. The recommended method is to use Smart Connect to load new and updated data into Extender or use the Extender import Data Entry tool for new data.

In Extender a user can also change Extender data from the window itself using

Thanks,

Lorren

Was this reply helpful? Yes No
Richard Whaley 25,195 on at

Like (0)

Report

So many words......

Extender can create a view of the data for an Extender Window/Form. This view provides a horizontal image of the data. Using the view you can import data directly and all of the tables will be hit.

SIMPLE.

BTW, we have a book called Extending MS Dynamics GP that covers all of the features of Extender quite well. www.AccoladePublications.com

Was this reply helpful? Yes No