Sage100 Visual Integrator Conundrum

SOLVED

All,

I'll try and be concise. I am working with Sage100 2019 and Visual Integrator Jobs. We just moved up from Mas90 4.3. Whoa, I know. Here's my dilemma:

In Mas90, we had a VI job that would bring in website orders in a csv formatted file and import them into Mas. No problems. Our web host exports the orders with every item in a separate row. So one person could order 5 items and each item would be its own row, but with the same customer number. Our VI job would look at the Customer Number, create a Sales Order and only create a new Sales Order when the customer Number changed. No problem. We did this using the Next Operand in SalesOrderNo referencing CustomerNo as the Header field, and using the ChangeTo Record referencing the value of the CustomerNo field which is Column 1 in our csv file. No problem.

Now, in Sage100, I make the VI job the same way as in Mas90, but there is no longer a Record called ChangeTo and the VI job will process the file, but if there are multiple items on a single order (each in their own row), it will create a new Sales Order Number for each item, but give each Sales Order Number the same Customer Number. I cannot for the life of me get all of those items on one single order. Without the ChangeTo Record Operand, I have no idea what to do.

I have tried adding Conditions to the SalesOrderNo field, but to no avail. That's about how far I know to go.

Any suggestions? I am stuck with a capital S.

Thanks!

Parents
  • 0

    With using Next for the SO #, VI will create a new order if any of the values in an "H" column changes in the source data.

  • 0 in reply to Kevin M

    Kevin M, thank for the reply. The thing is, I may have 5 rows with an H column that is the same. for instance:

    #23176 will be the H column for 5 rows because John Smith ordered 5 items, and our web host (we use Shopify) will export the items John ordered on their own row. The H column has the same value though. VI will see that the H column is the same and the customer number will be #23176, but it will assign a brand new SO number for each item, and only the first item will have the bill and ship to info.

  • 0 in reply to hyanaga

    hyanaga, I tried your suggestions, but I get the same results, no matter what I do, the VI job splits up a multi item order into a header record with the customer information and then each item as a line detail. I cannot seem to make the job merge the header record and the line details so that there is one order with multiple items purchased.

  • 0 in reply to j-fly

    Did you create a brand new VI import job on the new version for this?

    We need to see your entire VI import job definition to observe for any header fields that might be pointing to a column that is changing between one item row and the next. You can either take a screenshot of each tab and repeat on tabs where you need to scroll the right list or try printing the job's definition to a PDF and attach that.

    Since you can't post the raw source file, you'll need to thoroughly examine every column referenced by an H.* field that uses the Replace operation.

    Do you have this unchecked?

    Do you have anything defined on this tab? If the above option is unchecked and the source file contains both header and detail info on the same line, the only thing you should have on this tab is a "Skip Record". but only if actually needed.

  • 0 in reply to David Speck

    I'm thinking Perform logic might be required in this situation... because a simple test still split for the rows with / without the extra details.

    Even when only setting Div / CustomerNo from the file, with the temp field being set to Key Assigns Only.

    ...and setting a File Assign value from the SO header when the Temp field is blank.

    What I thought might work... doesn't.

    Personally, I'd write a VBScript to make the file corrections to the source file before import... having done that many times in other situations (when VI doesn't play well with what's in a source file).

  • 0 in reply to Kevin M

    What you are seeing is because even temp fields using the Replace operation are used to compare current record read to prior record read to determine if a new header record is needed.

    Seeing your post and going back to review the screenshot of the source posted made me realize what is actually going on. 

    You can get around this by using the Calculated operation and in the Calculation field, set it to IMP$[x] where x is the column number. You can then conditionally set the actual header field when IMP$[x] is not blank. You can either do this on the header field or in a header temp field if you want to easily use the temp field in other calculations or conditions.

  • 0 in reply to David Speck

    David,

    OK, so I'm using CustomerNo as my header field. In that field, I set the operation to Calculated and then in the Calculation box I add IMP$[1] for my header column and then I'm stumped on the condition. How do I write out that condition? 

    Thank you all for the help. I understand why this is happening, just failing to understand how to fix it with VI.

  • 0 in reply to j-fly

    According to your source in the screenshot you attached, column 1 and 2 appear to be the only "header" fields that are actually repeated when multiple lines are present. You do not want to use the calculation operation on them, they must use the Replace operation so VI will be able to determine when a new header record is truly needed. You need to use the Calculation operation on any other header fields that you will be importing where they are not repeated when multiple lines are present. You would enter the condition like this.

    IMP$[3]<>""

    Before you go and do this on a bunch of fields, i would first test it out with a very small source file sample that contains at least 3 separate orders where order 1 and 3 might have just 1 line and order two in the middle can have 2 or more lines. Then set up a new import to include just the bare minimum fields with one header column using the Calculation and IMP$[x] reference.

  • 0 in reply to David Speck

    That is an awesome trick David!

    One SO.

  • 0 in reply to David Speck

    David! My man! That is the Rosetta Stone! Last question for now. I'm a newb a t supporting Sage, so please bear with my lack of knowledge. I am using the Calculation Operand and the Max Characters is causing failure of things like my ShipVia field. There are more than 15 characters. How do I allow more than the max for a field when using Calculation as the operand?

    Thanks once again! This has been an education.

  • 0 in reply to Kevin M

    Kevin and David,

    Using IMP with the Calculation Operand is not working with my FreightAmt field I'm guessing because it's Data Type is Number and not String. I can't use the Replace Operand for anything else, so I'm guessing I''l have to convert the number to string or use an additional temp field. Do I have that correct or am I way off?

    Thank you a thousand times over.

  • 0 in reply to j-fly

    You cannot exceed sage 100's max length for a field, the database file will not support it. You can either truncate it down to the max length, modify it by abbreviating it to something else, or skip it. On the first tab (configuration) there is an option to skip truncation, do you have that checked? If it isn't checked, then if you source exceeds the max length, it should get truncated automatically although I don't recall ever needing to observe and confirm it will do that on fields using the Calculation operation. If you have to truncate it yourself, use the MID function.

    MID(IMP$[3],1,15)

    The first argument is the string to be modified, the second argument is the starting point (not zero based), the third argument is the number of characters to keep from the starting point.

Reply
  • 0 in reply to j-fly

    You cannot exceed sage 100's max length for a field, the database file will not support it. You can either truncate it down to the max length, modify it by abbreviating it to something else, or skip it. On the first tab (configuration) there is an option to skip truncation, do you have that checked? If it isn't checked, then if you source exceeds the max length, it should get truncated automatically although I don't recall ever needing to observe and confirm it will do that on fields using the Calculation operation. If you have to truncate it yourself, use the MID function.

    MID(IMP$[3],1,15)

    The first argument is the string to be modified, the second argument is the starting point (not zero based), the third argument is the number of characters to keep from the starting point.

Children
No Data