Visual Integrator Import Query

[adriangarford]

Hi All

Not sure if anyone can help here? I'm trying to finalise a visual integrator import in MAS 200 (version 4.30).

This VI job will import shipping information from our 3rd party fulfilment operation via a simple delimited text file the pass to us. The aim is to automatically populate shipping & tracking information, thus generating invoices much quicker and cutting out the unnecessary errors and admin time.

The annoying thing for me is 90% of this job is working fine but I can't go live because of the following issues:

- FreightAmt. This isn't specified in my import but I was hoping VI would automatically bring it in from the original order? I've tried a few things (including omitting it completely) but I just can't work out how to bring it through?
- DiscountRate (as applied to the SO header). Same problem as above - this is also not available in the file from our fulfilment partner and gets 'lost' when converting the VI import data into an invoice.
- Miscellaneous (/M items). For some reason these items don't get bought accross to be invoiced through this import?
- VI creates a separate line rather than update the existing order lines. I've since read that this is an easy fix using the line key and updating to version 4.4 so this bit is sorted.

Any help on how I fix the above would be appreciated!
Thanks

Adrian

 

[Blorf]

If it is a simple text file, can you not bring that text file into some query environment to get the missing data? Link it to the SO header to get the values you need? You can't do a file assign on a number, so I think this is your only option.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[MASGuy]

You have not mentioned what files you are attempting to import into. So Invoice Data Entry? SO Invoice Tracking?

If you cannot get /M to import then you probably do not have the records tab setup correctly.

VI will not update existing lines in the table you are importing into. It will only add new lines. If you are importing into SO Invoice Data Entry and are attempting to update exisiting lines, not gonna happen. Totally not possible with VI.

It is not possible to bring over the frieght amount from the sales order as this is not part of the businesss framework logic. You can't even do it with manual entry.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[adriangarford]

MASGuy - sorry, I should have mentioned this earlier! I have two chained imports running that pickup the same text file for their import. The first is importing into SO_InvoiceHeader and the second is importing into SO_InvoiceTracking.

It's only the one importing into SO_InvoiceHeader that's experiencing all of the problems.

Blorf - good idea i'll give this a go. I might try an access database (simply because i'm familiar with it), import the text file within that & pull the original order data from MAS200 first. A couple of new VI jobs with an ODBC linked imports would then allow this 'full' data to be loaded into Sage.

PS. Surely this freight & discount thing is a flaw in VI? Importing shipping info to automatically create invoices must be a really common request & I can't believe it doesn't pull from the original order automatically!

 

[MASGuy]

Try this, manually create a sales order in MAS. Add a freight amount. Create an invoice based on the sales order. Tell me what happens to the freight field.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[adriangarford]

Hi All

Here's a quick update on this - I decided to try using MS access as a intermediary vehicle to make this work. The database sits on the server & I import my shipping data text file. Sage's live order info is then exported via a VI export. A simple query brings the two together & is the source of my SO_InvoiceHeader import job via ODBC.

The problem now is that I'm getting the following error message:

"Error #42: Subscript out of range / Invalid subscript"

Any ideas what may be causing this?

Below are the screen prints of the VI job spec and error message. I know it's not the ODBC link as i have another import into SO_Tracking that works fine.

http://www.mediafire.com/?q8djz5bc45m57d4

http://www.mediafire.com/?f91vjd7pcavoneg

I think it might be something to do with the batch number as if i change it to "incr assign" it doesn't fail?

Thanks

Adrian

 

[MASGuy]

Did you know that you can actually do a SQL Specific Pass Through Query in Access to read the Sales Order files without having to do an export?

Are you trying to assign a value to the batch number?

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[adriangarford]

D'oh - didn't know that!

Yes I have it set up currently so that a user can assign a batch number if they like but it's not essential functionality.

Is there is a way of automatically assigning the next batch number to the whole import?

 

[MASGuy]

The system will automatically assign a batch number for you.

Over on the Sage Community BigLouie has posted examples of how to do SQL Specific Pass Through Queries with log in. Do a search.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[adriangarford]

Thanks, I'll have a look at that. Any idea what could be causing my error message?

 

[MASGuy]

I suspect it is because you are trying to give it a batch number when you should not be.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[adriangarford]

Cracked it, I think the batch number anomaly must have been a red herring (hope this is not an English only phrase?).

Problem was that I included the [H.ShipperID] and [H.CommentText] in the data definition with Replace operators and refereces to columns that were the wrong format / didn't exist.

Changing them to an Assign operator & adding the text as default seems to have cleared this all up.

Thanks all for your help everyone

Adrian