Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Updating fields which ...
Microsoft Dynamics GP (Archived)

Updating fields which are not available through Integration Manager

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee
I would like to use this post to gather the ideas related to updating those fields in GP that are not available through Integration Manager. I am recently facing a similar problem in updating UMSLSOPT (Unit of Measure Sales Option) in IV001007 table for a number of items for certain programs. I have thoroughly checked all the fields in Inventory item destination in Microsoft Dynamics GP adapter but could not find this field. Another similar issue is how can make sure that updating a table directly using SQL query will not corrupt the GP. Any reply to these issues will be highly appreciated.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Re: Re: Re: Re: Re: Updating fields which are not available through Integration Manager

    Fair point...didn't think of that.

    Its back to the sql update, so.

    Ian.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Re: Re: Re: Re: Updating fields which are not available through Integration Manager
    If we use macro, how can we mention the specific line number for the specific price level for which we want to change the Selling Option for an item in the Item Price List Option Maintenance window? If all the items have only one price level then it is simple but if items have more than one price level then the required price level will be in different line for different items. I have used macros for simple cases but have no idea if we can use conditions in macro such as if the price level = required price level then selling option = not available. As an example following is the macro to change the Selling Option to "Not Available" for item 'REQ_ITEM1' for price level 'REQ_PRCLVL'. However, for other items may be the 'REQ_PRCLVL' is in some other lines.

    # DEXVERSION=10.0.324.0 2 2
    CheckActiveWin dictionary 'default'  form ivItemPriceListMnt window ivItemPriceListMnt
      TypeTo field 'Item Number' , 'REQ_ITEM1'
      MoveTo field 'Price Method' item 1  # 'Currency Amount'
      MoveTo field 'Options Button'
      ClickHit field 'Options Button'
    NewActiveWin dictionary 'default'  form ivItemPriceListOptMnt window ivItemPriceListOptMnt
    ActivateWindow dictionary 'default'  form ivItemPriceListOptMnt window ivItemPriceListOptMnt
    # Key 1: 'REQ_ITEM1', '', 'ANY_PRICELEVEL', 'AnyUofM'
      MoveTo line 5 scrollwin ivItemPriceListOptMntScroll field 'U Of M Sales Options' item 2  # 'Whole'
    # Key 1: 'REQ_ITEM1', '', 'REQ_PRCLVL', 'AnyUofM'
      ClickHit line 5 scrollwin ivItemPriceListOptMntScroll field 'U Of M Sales Options' item 1  # 'Not Available'
      MoveTo field 'OK Button'
      ClickHit field 'OK Button'
    NewActiveWin dictionary 'default'  form ivItemPriceListMnt window ivItemPriceListMnt
    ActivateWindow dictionary 'default'  form ivItemPriceListMnt window ivItemPriceListMnt
      MoveTo field 'Save Button'
      ClickHit field 'Save Button'

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Re: Re: Re: Updating fields which are not available through Integration Manager

    Richards bang on with the backups!

    If this is just a once off update, then eConnect might be overkill. Its worth learning it though - definately the best solution for any kind of serious regular integration work (it does have some minor issues, but you can get around them).

    A well designed Macro might be your answer in this case - its a lot safer in that it won't allow you to do anything you shouldn't. Writing data direct to the tables, you can write anything you like, but the macro will obey all of the GP business logic an therefor fail when logic is being broken and stop bad data being inserted.

    Ian.

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at
    Re: Re: Re: Re: Re: Updating fields which are not available through Integration Manager

    In this room we often do not know if we are talking to a SQL expert or someone that thinks they can do it anyway.  Thus the disclaimers.

     :)

     

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Re: Updating fields which are not available through Integration Manager

    Hi Ian/Richard,

    Thank you so much. Yes, the table is IV00107(my apologies for the typo). Sorry, we use the term program for price level. We need to update the UMSLSOPT to 'Not Available' for all the items under all the expired price levels. The CSV file which I had created for integration was like this

    ITEMNMBR,PRCLEVEL,UMSLSOPT
    ITEMNO1,PRICELEVEL1 ,1
    ITEMNO2,PRICELEVEL1 ,1
    ..
    ITEMNON,PRICELEVEL1 ,1
    .....
    ITEMNO1,PRICELEVEL2 ,1
    ITEMNO2,PRICELEVEL2 ,1
    ..
    ITEMNON,PRICELEVEL2 ,1

    Although I have never used econnect to update or insert any records but now, as suggested by Ian, I will spend some time to learn it and use it for such type of updates.In the meantime, as suggested by Richard, I am planning to update UMSLSOPT in IV00107 table in Test Company for few items for a specific price level (we use only on UOM for each item) to make sure that it is not causing any issues. As far as Joins are concerned they are a part of my daily life so I think I am qualified enough to use the joins effectively. Thanks again and I am very grateful to all of you. 

     

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at
    Re: Re: Re: Updating fields which are not available through Integration Manager

    BACKUPS FIRST!!!!! 

     Many, many tables are interrelated and using SQL can be dangerous.  However, in this case, if you update the UMSLSOPT in the IV00107 table with a UOM that is defined in the UOM schedule for the items, you will be safe. 

    BE SURE TO UPDATE WITH A UOM THAT IS IN THE SCHEDULE FOR THE ITEM.  One way to do this would be to link the tables IV00101, IV00107, and the UOM schedule table (I forget the number) and use an update that moves the base UOM (if that is the one you want) from the schedule table into the IV00107 table.

    You could also simply do updates to the IV00107 and then join that table to the uom schedule line table on the UOM and look for empty cells in the schedule column.  These would be incorrect values in the 107 table.

    BACKUPS FIRST!!!

    And if you are not sure how to structure the joins, find a qualified SQL consultant to help you.  If you do know how to build them, then I don't need to explain them and you are qualified.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Updating fields which are not available through Integration Manager

    Hi Zafar,

    What do you mean by 'for certain programs' ?

    Is the table in fact IV00107, the Item Price List Options table?

    eConnect has an incoming schema called taIVCreateItemPriceListLine, which creates / updates the price list lines for items, including the UMSLSOPT field. (valid values are 1 = Not Available, 2 = Whole, 3 = Fractional and whole.)

    In terms of updating this field through SQL. Is this something that will be done regularly, or is it just a once off set up issue? If its just to correct a set up issue, you could update all items through SQL, then run a checklinks on the entire inventory series. I haven't tested this, but if it was a test enviornment you are working in, I would give it a go and see how you get on. You would need to do serious testing in POP, SOP and Inventory.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Updating fields which are not available through Integration Manager

    Hi Ian,

    Thank you so much for your reply.  Yes, I have a question about specific integration. I need to to update UMSLSOPT (Unit of Measure Sales Option) in IV001007 table for a number of items for certain programs. I checked the two destination adopters in GP 10 but could not locate this field under destination inventory items.

     

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Updating fields which are not available through Integration Manager

    Hi Zafar,

    I think you need to take a good look at eConnect.

    I would strongly advise you against updating tables directly through SQL. I've seen this done a number of times (usually called in to rescue a failed integration project), and its never successful when the integrations are more than a minor simple field update. To answer your question 'how can you ensure that updating data through sql won't corrupt GP'...you can't.

    Maybe for very minor data changes, I'd consider a SQL update, but only for fields that have no dependencies. Others may advise you different, this is just my personal feelings. eConnect is a supported and integral product - why re-create the wheel unless you absolutely have to?

    If you have any questions about specific integrations, let me know.

    Best regards,

    Best regards,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans