Click here to Skip to main content
14,549,638 members

Visual Basic

 
AnswerRe: Install a VB Net application to install a sql database on other computers Pin
Dave Kreskowiak3-Apr-20 14:12
mveDave Kreskowiak3-Apr-20 14:12 
AnswerRe: Install a VB Net application to install a sql database on other computers Pin
evry1falls6-May-20 9:22
Memberevry1falls6-May-20 9:22 
QuestionLinq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx3-Apr-20 7:48
professionaljkirkerx3-Apr-20 7:48 
AnswerRe: Linq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx3-Apr-20 8:51
professionaljkirkerx3-Apr-20 8:51 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
Richard Deeming6-Apr-20 1:19
mveRichard Deeming6-Apr-20 1:19 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx6-Apr-20 7:28
professionaljkirkerx6-Apr-20 7:28 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
Richard Deeming6-Apr-20 8:01
mveRichard Deeming6-Apr-20 8:01 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx6-Apr-20 9:20
professionaljkirkerx6-Apr-20 9:20 
I thought about that. Actually I think I had that at first (Or Similar), but after doing the fact check on the report numbers, I had lots of negative margins. So my thinking was that it wasn't iterating through all the items on each invoice. So the numbers are spot on now, and the report runs very fast, didn't really take a hit in the invoice loop.

Interesting, your code is spot on, like your are working along side with me on this.

So your saying technically, this will produce the result I'm looking for?
Or is it just in theory it should?

hmm...

I ended up with this. I wanted to take special care of negative numbers, and avoid divide by zero errors as well. So if an item was given away for free at $0 price, I would deduct that from the margin total.
'Use Linq to Group the Invoices Together
Dim gInvoicesAll As List(Of MarginCustomerInvoicesR1) = cTpAll.OrderBy(Function(ob) ob.FINVNO).GroupBy(Function(inv) inv.FINVNO).Select(Function(cl) New MarginCustomerInvoicesR1() With {
 .FCustNo = cl.First().FCUSTNO,
 .FInvNo = cl.First().FINVNO,
 .FShipDate = cl.First().FSHIPDATE,
 .FDescript = cl.First().FDESCRIPT,
 .FAmount = cl.Sum(Function(sum) sum.FAMOUNT),
 .FExtCost = 0,
 .FExtPrice = 0,
 .FExtMargin = 0,
 .FPercent = 0
}).ToList()

Dim rsDataRow As DataRow
For Each pI As MarginCustomerInvoicesR1 In gInvoicesAll

  'Instead of using Linq, I'll just iterate the original data before grouping
  Dim items = cTpAll.Where(Function(item) item.FINVNO.Equals(pI.FInvNo)).ToList()
  For Each item In items
   pI.FExtCost += If(item.FCOST <> 0 And item.FSHIPQTY <> 0, item.FCOST * item.FSHIPQTY, 0)
   pI.FExtPrice += If(item.FPRICE <> 0 And item.FSHIPQTY <> 0, item.FPRICE * item.FSHIPQTY, 0)

  'Forward or Reverse the Margin Calc
  Select Case True
   Case item.FPRICE.Equals(0)
     pI.FExtMargin -= If(item.FCOST <> 0 And item.FSHIPQTY <> 0, item.FCOST * item.FSHIPQTY, 0)
   Case Else
     pI.FExtMargin += pI.FExtPrice - pI.FExtCost
  End Select
Next

Dim fPercent = If(pI.FExtCost <> 0 And pI.FExtPrice <> 0, Math.Round((pI.FExtPrice - pI.FExtCost) / pI.FExtCost, 4), 0)
If it ain't broke don't fix it
Discover my world at jkirkerx.com

GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
Richard Deeming7-Apr-20 0:30
mveRichard Deeming7-Apr-20 0:30 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx7-Apr-20 7:05
professionaljkirkerx7-Apr-20 7:05 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx8-Apr-20 9:24
professionaljkirkerx8-Apr-20 9:24 
QuestionHow to update sql server data from excel data using VBA code by all User Pin
Member 1478441627-Mar-20 0:25
MemberMember 1478441627-Mar-20 0:25 
AnswerRe: How to update sql server data from excel data using VBA code by all User Pin
ZurdoDev27-Mar-20 1:19
mveZurdoDev27-Mar-20 1:19 
GeneralRe: How to update sql server data from excel data using VBA code by all User Pin
Member 1478441627-Mar-20 2:17
MemberMember 1478441627-Mar-20 2:17 
AnswerMessage Closed Pin
13-Apr-20 2:16
MemberSharie14d13-Apr-20 2:16 
Questionevent handler Pin
Member 1356965021-Mar-20 23:16
MemberMember 1356965021-Mar-20 23:16 
AnswerRe: event handler Pin
Richard MacCutchan21-Mar-20 23:31
mveRichard MacCutchan21-Mar-20 23:31 
GeneralRe: event handler Pin
Member 1356965022-Mar-20 0:24
MemberMember 1356965022-Mar-20 0:24 
GeneralRe: event handler Pin
Richard MacCutchan22-Mar-20 0:33
mveRichard MacCutchan22-Mar-20 0:33 
GeneralRe: event handler Pin
Member 1356965022-Mar-20 1:04
MemberMember 1356965022-Mar-20 1:04 
GeneralRe: event handler Pin
Richard MacCutchan22-Mar-20 4:03
mveRichard MacCutchan22-Mar-20 4:03 
GeneralRe: event handler Pin
Member 1356965022-Mar-20 4:18
MemberMember 1356965022-Mar-20 4:18 
GeneralRe: event handler Pin
Richard MacCutchan22-Mar-20 4:47
mveRichard MacCutchan22-Mar-20 4:47 
GeneralRe: event handler Pin
Member 1356965022-Mar-20 5:01
MemberMember 1356965022-Mar-20 5:01 
GeneralRe: event handler Pin
Richard MacCutchan22-Mar-20 5:12
mveRichard MacCutchan22-Mar-20 5:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.