Using Visual Integrator to modify BOM

SOLVED

  Is it still not possible to use Sage VI to change the qty on an already existing component item in Bill of Materials? I have not seen anything posted on this subject in several years, so i just wanted to see if it was possible now. I've tried everything I can think of and just can not get it to work.

  • 0

    You either need to have the LineKey in your source data and point the L.LineKey element to it using the Replace operation or if your BM module is configured to not allow duplicate components, you can use some perform logic to retrieve the LineKey and either; 

    1. Populate a source data column using the IMP$[X] variable where X is a number representing the column number and then use the Replace operation on the L.LineKey element and point it to X, this has to take place before any other line elements are set.
    2. Use coBusiness'Lines'EditLine(coBusiness'Lines'GetEditKey$(LineKeyVariable$)) to tell the import which line to edit. Substitute LineKeyVariable$ with whatever variable name you decide to use but keep the "$" suffix.

    In order to retrieve the LineKey, you can use the coBusiness'Lines'ItemCodeCollectionFind method, the first argument passed is the component item code to find, this could be a variable, string, or reference a source data column by using IMP$[X], the second argument is the variable name you want the line key returned in.

    I'll include some screenshots later.

  • +1 in reply to David Speck
    verified answer

    I had this question earlier today.  Four fields in the import file (exact text match to what is in Sage for the first three, including leading zeroes): BillNo, Revision, LineKey, QuantityPerBill (new value).

  • 0 in reply to Kevin M

      So, I should be able to just pull those fields out of Sage using Access and then just import it back in with the updated quantity using VI? Is that correct?

  • 0 in reply to BobbyB4569

    As long as the text fields are not auto-converted to numeric (losing the leading zeroes), it should work.  Today my advice was to do Excel query, edit the quantity, then save as CSV to import... and that lead to success.

  • 0 in reply to Kevin M

      I will give it a try and post the outcome. Thank you very much for your input!

  • 0 in reply to BobbyB4569

      Well, I was finally able to get this to work! First, I had to use a text file for the import so all of my numbers stayed exactly the same as how they came out of Sage. Second, I had to uncheck the box on the import job for "Header separate from line record". After that, it worked like a Charm.                                                  Thanks again for your help! I had been fighting with this for a while.                                               

  • 0 in reply to BobbyB4569

    Opening CSV files with Excel can indeed corrupt data (leading zeroes removed, dates reformatted, big numbers converted to scientific notation...).  Text editors are better at preserving CSV data integrity for sure.

  • 0 in reply to Kevin M

    Hi Kevin, I am wondering if I can create a VI to change the balance of a master PO. I think your method may lead me to a good start. Do you have any opinions or suggestions?  Ultimately I am trying to achieve creating standard orders to be issued against the master order using Visual integrator.

  • 0 in reply to EmmaAPI

    Start a new discussion for this question, with appropriate details, since it seems your question has nothing to do with BoM.

  • 0 in reply to Kevin M

    Yes, it's not related the BOM. I thought it may be relevant in some sense. Maybe it's not.