Sage 100

Is there a way to combine lines on a VI import job?

Importing to PO Receipt Entry.
Have the ItemCode, OrderLineKey, WarehouseCode, QuantityInvoiced, & ExtensionAmt.
The issue is that the vendor is passing multiple lines for the same OrderLineKey (like separate lot numbers) on the csv file, that we don't care about.
They can't seem to sum them up before sending. There are sometimes hundreds of lines, so not something we want to open the source file & manually manipulate.
If there are no duplicate lines, it works fine.

Is there a way to add the QuantityInvoice & ExtensionAmt fields per OrderLineKey using some VI logic?

Thx.

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------

I don't know if there is some sort of Providex magic way, but we've dealt with this kind of thing by either:

• running the file through a VBScript to do the line consolidation,
  or
• importing into a UDT, then running a second report from the UDT data (either SQL View ODBC, or report export to consolidated new file and import from that).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Dang. Neither are less steps for the end-user, which I am trying to accomplish.
Thanks for the suggestions! 
I was hoping there was a way someone knew how with some of that ProvideX magic :)

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------

Original Message:
Sent: 03-09-2021 16:07
From: Kevin Moyes
Subject: Combine lines in VI Import

I don't know if there is some sort of Providex magic way, but we've dealt with this kind of thing by either:

• running the file through a VBScript to do the line consolidation,
  or
• importing into a UDT, then running a second report from the UDT data (either SQL View ODBC, or report export to consolidated new file and import from that).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

It's possible Perform Logic could do some comparisons of OrderLineKey, QuantityInvoiced, and ExtensionAmt and reject a duplicate but it is a bit messy AND you would see the rejects in the Import Job Log as failed records and that would cause confusion for your customer. It isn't like rejecting a duplicate A/P Invoice.

What I would do myself is have Perform Logic run at Job Execution (f.k.a. Job Start) where it will actually open your source import file (assuming it's a flat file like a CSV), make a backup of it, then go thru line by line and remove the duplicate rows based on the OrderLineKey, QuantityInvoiced, and ExtensionAmt columns. I've done that sort of thing before.

But that is not very different than what Kevin suggested. What about telling the vendor to use SELECT DISTINCT or SELECT TOP 1 (not entirely being facetious :-)

------------------------------
Alnoor Cassim

Email: alnoor@asifocus.com
Ph: 949-689-9887
Orange County, CA
------------------------------

Original Message:
Sent: 03-09-2021 21:50
From: Dana Young
Subject: Combine lines in VI Import

Dang. Neither are less steps for the end-user, which I am trying to accomplish.
Thanks for the suggestions!
I was hoping there was a way someone knew how with some of that ProvideX magic :)

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------


Original Message:
Sent: 03-09-2021 16:07
From: Kevin Moyes
Subject: Combine lines in VI Import

I don't know if there is some sort of Providex magic way, but we've dealt with this kind of thing by either:

• running the file through a VBScript to do the line consolidation,
  or
• importing into a UDT, then running a second report from the UDT data (either SQL View ODBC, or report export to consolidated new file and import from that).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

I've already asked the vendor to group by the OrderLineKey because we need to sum it up, not dump them, since they are part of the total qty invoiced & extension amount... but they couldn't seem to figure out how. I feel like this is the option we need so that each line number is the totals for that invoice.
It already errors on those additional lines & just fails them, so its basically throwing them out now. They had an example of 90+ rows that got rejected on the last attempt, so its not like its 1 that can be adjusted manually after the receipt is in the batch, or fixed on the csv.

I think the vendor fixing the issue before its a csv is the best bet, but I wanted to see if I had other options.
Thx!

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------

Original Message:
Sent: 03-10-2021 02:12
From: Alnoor Cassim
Subject: Combine lines in VI Import

It's possible Perform Logic could do some comparisons of OrderLineKey, QuantityInvoiced, and ExtensionAmt and reject a duplicate but it is a bit messy AND you would see the rejects in the Import Job Log as failed records and that would cause confusion for your customer. It isn't like rejecting a duplicate A/P Invoice.

What I would do myself is have Perform Logic run at Job Execution (f.k.a. Job Start) where it will actually open your source import file (assuming it's a flat file like a CSV), make a backup of it, then go thru line by line and remove the duplicate rows based on the OrderLineKey, QuantityInvoiced, and ExtensionAmt columns. I've done that sort of thing before.

But that is not very different than what Kevin suggested. What about telling the vendor to use SELECT DISTINCT or SELECT TOP 1 (not entirely being facetious :-)

------------------------------
Alnoor Cassim

Email: alnoor@asifocus.com
Ph: 949-689-9887
Orange County, CA
------------------------------


Original Message:
Sent: 03-09-2021 21:50
From: Dana Young
Subject: Combine lines in VI Import

Dang. Neither are less steps for the end-user, which I am trying to accomplish.
Thanks for the suggestions!
I was hoping there was a way someone knew how with some of that ProvideX magic :)

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI


Original Message:
Sent: 03-09-2021 16:07
From: Kevin Moyes
Subject: Combine lines in VI Import

I don't know if there is some sort of Providex magic way, but we've dealt with this kind of thing by either:

• running the file through a VBScript to do the line consolidation,
  or
• importing into a UDT, then running a second report from the UDT data (either SQL View ODBC, or report export to consolidated new file and import from that).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

If you know MS Access, you could create a summary query (assuming you don't need the Lot/Serial detail) and use that as your V/I source. The end users don't need MS Access b/c its driver is included with Windows. You could also export the summary query with a macro as a csv and import that, but that would require the end user have MS Access.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

Original Message:
Sent: 03-10-2021 08:22
From: Dana Young
Subject: Combine lines in VI Import

I've already asked the vendor to group by the OrderLineKey because we need to sum it up, not dump them, since they are part of the total qty invoiced & extension amount... but they couldn't seem to figure out how. I feel like this is the option we need so that each line number is the totals for that invoice.
It already errors on those additional lines & just fails them, so its basically throwing them out now. They had an example of 90+ rows that got rejected on the last attempt, so its not like its 1 that can be adjusted manually after the receipt is in the batch, or fixed on the csv.

I think the vendor fixing the issue before its a csv is the best bet, but I wanted to see if I had other options.
Thx!

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------


Original Message:
Sent: 03-10-2021 02:12
From: Alnoor Cassim
Subject: Combine lines in VI Import

It's possible Perform Logic could do some comparisons of OrderLineKey, QuantityInvoiced, and ExtensionAmt and reject a duplicate but it is a bit messy AND you would see the rejects in the Import Job Log as failed records and that would cause confusion for your customer. It isn't like rejecting a duplicate A/P Invoice.

What I would do myself is have Perform Logic run at Job Execution (f.k.a. Job Start) where it will actually open your source import file (assuming it's a flat file like a CSV), make a backup of it, then go thru line by line and remove the duplicate rows based on the OrderLineKey, QuantityInvoiced, and ExtensionAmt columns. I've done that sort of thing before.

But that is not very different than what Kevin suggested. What about telling the vendor to use SELECT DISTINCT or SELECT TOP 1 (not entirely being facetious :-)

------------------------------
Alnoor Cassim

Email: alnoor@asifocus.com
Ph: 949-689-9887
Orange County, CA


Original Message:
Sent: 03-09-2021 21:50
From: Dana Young
Subject: Combine lines in VI Import

Dang. Neither are less steps for the end-user, which I am trying to accomplish.
Thanks for the suggestions!
I was hoping there was a way someone knew how with some of that ProvideX magic :)

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI


Original Message:
Sent: 03-09-2021 16:07
From: Kevin Moyes
Subject: Combine lines in VI Import

I don't know if there is some sort of Providex magic way, but we've dealt with this kind of thing by either:

• running the file through a VBScript to do the line consolidation,
  or
• importing into a UDT, then running a second report from the UDT data (either SQL View ODBC, or report export to consolidated new file and import from that).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

If you can always have the source file named the same (or use perform logic on execution to make a copy of the selected file and rename it to something static somewhere), you can actually just set up a DSN using the microsoft text driver and point it to a static location where the source file will be placed (which should either be on the server or available via a UNC path).  Then have the import use ODBC and the DSN as the source and use a SQL statement to sum the quantity invoiced and group by the fields unique to each line item (purchase order number/invoice number, item code, and order line key).  If you are running Advanced, this could be done with a system DSN set up on the server and check the On Host option.  You may also need to set up a schema.ini file in the location where the file will be read from so you can control how the text driver interprets data types.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 03-11-2021 02:25
From: Dan Burleson
Subject: Combine lines in VI Import

If you know MS Access, you could create a summary query (assuming you don't need the Lot/Serial detail) and use that as your V/I source. The end users don't need MS Access b/c its driver is included with Windows. You could also export the summary query with a macro as a csv and import that, but that would require the end user have MS Access.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------


Original Message:
Sent: 03-10-2021 08:22
From: Dana Young
Subject: Combine lines in VI Import

I've already asked the vendor to group by the OrderLineKey because we need to sum it up, not dump them, since they are part of the total qty invoiced & extension amount... but they couldn't seem to figure out how. I feel like this is the option we need so that each line number is the totals for that invoice.
It already errors on those additional lines & just fails them, so its basically throwing them out now. They had an example of 90+ rows that got rejected on the last attempt, so its not like its 1 that can be adjusted manually after the receipt is in the batch, or fixed on the csv.

I think the vendor fixing the issue before its a csv is the best bet, but I wanted to see if I had other options.
Thx!

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI


Original Message:
Sent: 03-10-2021 02:12
From: Alnoor Cassim
Subject: Combine lines in VI Import

It's possible Perform Logic could do some comparisons of OrderLineKey, QuantityInvoiced, and ExtensionAmt and reject a duplicate but it is a bit messy AND you would see the rejects in the Import Job Log as failed records and that would cause confusion for your customer. It isn't like rejecting a duplicate A/P Invoice.

What I would do myself is have Perform Logic run at Job Execution (f.k.a. Job Start) where it will actually open your source import file (assuming it's a flat file like a CSV), make a backup of it, then go thru line by line and remove the duplicate rows based on the OrderLineKey, QuantityInvoiced, and ExtensionAmt columns. I've done that sort of thing before.

But that is not very different than what Kevin suggested. What about telling the vendor to use SELECT DISTINCT or SELECT TOP 1 (not entirely being facetious :-)

------------------------------
Alnoor Cassim

Email: alnoor@asifocus.com
Ph: 949-689-9887
Orange County, CA


Original Message:
Sent: 03-09-2021 21:50
From: Dana Young
Subject: Combine lines in VI Import

Dang. Neither are less steps for the end-user, which I am trying to accomplish.
Thanks for the suggestions!
I was hoping there was a way someone knew how with some of that ProvideX magic :)

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI


Original Message:
Sent: 03-09-2021 16:07
From: Kevin Moyes
Subject: Combine lines in VI Import

I don't know if there is some sort of Providex magic way, but we've dealt with this kind of thing by either:

• running the file through a VBScript to do the line consolidation,
  or
• importing into a UDT, then running a second report from the UDT data (either SQL View ODBC, or report export to consolidated new file and import from that).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON