Sage100 Visual Integrator Conundrum

Question

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!

David Speck · Accepted Answer

For numeric fields, you have to convert the string variable IMP$[x] into a number. Try this. 
 EVN("NUM(IMP$[x])",0) 
 The NUM function takes a string variable or value in the first argument and converts it to a number but it will cause a hard error if the string cannot be converted to a number so we wrap it in the EVN function which evaluates an expression in the first argument and returns a number but if the evaluation causes an error, then the value in the second argument is returned instead, in this case, I used 0.

David Speck · Answer

Take what you have in the Condition and copy it to the Calculation. 
 Then add >0 to your existing condition so it should be 
 EVN("NUM(IMP$[10])",0)>0

David Speck · Answer

Is the freight still being read from the wrong row? 
 If it is, try creating a temp field set up as a number, to be assigned on key assigns only, and to reset on every record. Then use the same condition and calculation used on the FreightAmt field. 
 Run a test import and observe whether or not the temp field has the correct value for each row. If it does, move it above the FreightAmt field and then change the FreightAmt field's calculation to just be Tempxxx where xxx is the sequence number of the temp field you created and then set the condition on the FreightAmt field to Tempxxx>0.

Sage 100

Sage100 Visual Integrator Conundrum

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING