Visual Basic

Re: Linq GroupBy Invoice Number, iterate all items before group

3-Apr-20 7:48

I feel like I painted myself into a corner here. But hopeful Linq was well designed to pull this off. I don't even know what to call it to look it up on the internet.

So I have a List of Items sold for the year, and each item has a cost, price, qty, invoice number, shipdate.
My objective is to group those items by invoice number. But as I group, I need to do the following ...

Say invoice 1000 has 5 items for example.
For each item assigned to invoice 1000,
I need ....

FExtCost = (FSHIPQTY * FCOST)<br />
fExtPrice = (FSHIPQTY * FPRICE)

What I have so far is the remnants of my design mistake, when I realized my numbers were wrong after fact checking the report. Like I just need the first instance of the inv number, first instance of a item description, but to total the cost, price so I can calculate the margin of the invoice.

Yes I'm asking for help writing code. Or a link to some examples I can study.

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(priceTotal) priceTotal.FAMOUNT),
    .FExtCost = 0,
    .FExtPrice = 0,
    .FExtMargin = 0,
    .FPercent = 0
}).ToList()

Along time ago, Richard gave me a sophistacated solution for price, so I tried to build on it

.FPriceMin = cl.OrderByDescending(Function(i) If(i.FPrice = 0, 0, 1)).ThenByDescending(Function(i) i.FShipDate).First().FPrice,

If it ain't broke don't fix it
Discover my world at jkirkerx.com

modified 3-Apr-20 14:21pm.

jkirkerx3-Apr-20 8:51

Re: Linq GroupBy Invoice Number, iterate all items before group

3-Apr-20 8:51

Perhaps the answer is to just perform the calc after the grouping.
Group the Invoices, then loop them, and do another linq to go back to the original and grab the items.

'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)
    pI.FExtMargin += pI.FExtPrice - pI.FExtCost
  Next

If it ain't broke don't fix it
Discover my world at jkirkerx.com

Richard Deeming6-Apr-20 1:19

Richard Deeming

6-Apr-20 1:19

How about:

VB.NET

.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(item) item.FAMOUNT),
    .FExtCost = cl.Sum(Function(item) item.FCOST * item.FSHIPQTY),
    .FExtPrice = cl.Sum(Function(item) item.FPRICE * item.FSHIPQTY),
    .FExtMargin = cl.Sum(Function(item) (item.FPRICE - item.FCOST) * item.FSHIPQTY),
    .FPercent = 0
}).ToList()

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Linq GroupBy Invoice Number, iterate all items before group

jkirkerx6-Apr-20 7:28

Re: Linq GroupBy Invoice Number, iterate all items before group

6-Apr-20 7:28

So use this within my invoice loop?
Hmm ...

If it ain't broke don't fix it
Discover my world at jkirkerx.com

Richard Deeming6-Apr-20 8:01

Richard Deeming

6-Apr-20 8:01

No, in the outer query:

VB.NET

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(item) item.FAMOUNT),
    .FExtCost = cl.Sum(Function(item) item.FCOST * item.FSHIPQTY),
    .FExtPrice = cl.Sum(Function(item) item.FPRICE * item.FSHIPQTY),
    .FExtMargin = cl.Sum(Function(item) (item.FPRICE - item.FCOST) * item.FSHIPQTY),
    .FPercent = 0
}).ToList()

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Linq GroupBy Invoice Number, iterate all items before group

jkirkerx6-Apr-20 9:20

Re: Linq GroupBy Invoice Number, iterate all items before group

6-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

Richard Deeming7-Apr-20 0:30

Richard Deeming

7-Apr-20 0:30

jkirkerx wrote:

VB.NET

If(item.FCOST <> 0 And item.FSHIPQTY <> 0, item.FCOST * item.FSHIPQTY, 0)

If FCOST or FSHIPQTY is equal to 0, then FCOST * FSHIPQTY will return 0. There's no need for the conditional here, or any of the similar conditionals.

jkirkerx wrote:

VB.NET

Select Case True
    Case item.FPRICE.Equals(0)
        ' pI.FExtMargin -= If(item.FCOST <> 0 And item.FSHIPQTY <> 0, item.FCOST * item.FSHIPQTY, 0)
        ' Removing the conditional:
        pI.FExtMargin -= item.FCOST * item.FSHIPQTY
        
    Case Else
        ' pI.FExtMargin += pI.FExtPrice - pI.FExtCost
        ' Expanding the calculations:
        ' pI.FExtMargin += (item.FCOST * item.FSHIPQTY) - (item.FPRICE * item.FSHIPQTY)
        pI.FExtMargin += (item.FCOST - item.FPRICE) * item.FSHIPQTY
End Select

If FPRICE is equal to 0, then FCOST * FPRICE is equal to FCOST. Both cases are trying to perform the same calculation, so the Select Case is not required.

I say "trying to", because you're actually inflating the margin. For each item in the invoice which has a price, you're adding the cumulative difference between the price and the cost, rather than the difference between the price and the cost of that item.

Eg:

Items:
------
1: Price = 10; Cost = 5; Qty = 1
2: Price = 10; Cost = 5; Qty = 1
3: Price = 10; Cost = 5; Qty = 1

Your calculation:
-----------------
1: FExtPrice = 10; FExtCost =  5; FExtMargin =  0 + 10 -  5 =  5
2: FExtPrice = 20; FExtCost = 10; FExtMargin =  5 + 20 - 10 = 15
3: FExtPrice = 30; FExtCost = 15; FExtMargin = 15 + 30 - 15 = 30

Total margin: 30 = 100% !

Correct calculation:
--------------------
1: FExtPrice = 10; FExtCost =  5; FExtMargin =  0 + 10 - 5 = 5
2: FExtPrice = 20; FExtCost = 10; FExtMargin =  5 + 10 - 5 = 10
3: FExtPrice = 30; FExtCost = 15; FExtMargin = 10 + 10 - 5 = 15

Total margin: 15 = 50%

This code should produce the correct results in a single statement, without needing to manually loop over the items:

VB.NET

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(item) item.FAMOUNT),
 .FExtCost = cl.Sum(Function(item) item.FCOST * item.FSHIPQTY),
 .FExtPrice = cl.Sum(Function(item) item.FPRICE * item.FSHIPQTY),
 .FExtMargin = cl.Sum(Function(item) (item.FPRICE - item.FCOST) * item.FSHIPQTY),
 .FPercent = If(cl.Sum(Function(item) item.FCOST * item.FSHIPQTY) = 0, 0, Math.Round(cl.Sum(Function(item) (item.FPRICE - item.FCOST) * item.FSHIPQTY) / cl.Sum(Function(item) item.FCOST * item.FSHIPQTY), 4))
}).ToList()

If it doesn't, then give me an example where it goes wrong.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Linq GroupBy Invoice Number, iterate all items before group

jkirkerx7-Apr-20 7:05

Re: Linq GroupBy Invoice Number, iterate all items before group

7-Apr-20 7:05

Your case is solid here.
I'll do a case study here with this new info and facts, and fact check both methods and see what the totals are.
I really didn't think that group statement would iterate the items on the invoice and group it.
Let me see what happens ... Give me a day.

If it ain't broke don't fix it
Discover my world at jkirkerx.com

jkirkerx8-Apr-20 9:24

How to update sql server data from excel data using VBA code by all User

8-Apr-20 9:24

I went ahead and tested that code and it works good. I did the fact or math test and the numbers are spot on now. Don't know why I doubted the first revision of my code, which was close to what you worked on. Guess my math was off and I blamed it on the group function.

Thanks for sticking with it, and proving that your suggestion did the same thing, while being smaller and more compact.

If it ain't broke don't fix it
Discover my world at jkirkerx.com

Member 1478441627-Mar-20 0:25

Member 14784416

27-Mar-20 0:25

Hi Team,

I have an Excel file that has data and a table in SQL server.
I am doing it by excel VBA code in "Audit Query Report" Consolidation.
Hence, I want to do a Consolidated report by all Users in the SQL Table. Each User data to Update\Insert in DB once click the macro button in excel, Even I existing data to replaced, New Data should add in DB.

I googled it, unfortunately, there is no related code as my per requirement, Kindly advise best way code to make a consolidation DB, am finance Background, am not much familiar SQL code through VBA.

Re: How to update sql server data from excel data using VBA code by all User

ZurdoDev27-Mar-20 1:19

ZurdoDev

27-Mar-20 1:19

Member 14784416 wrote:
there is no related code as my per requirement,

Actually, there are probably millions of related codes out there on the internet. But you can't search for that whole task. You have to break it into pieces and look for code examples of the pieces.

But it's hard to understand exactly what you are asking. It sounds like you just need to be able to save data from excel into sql using vba. Here is one example, sql server - Export Excel to SQL using VBA - Stack Overflow[^]

Member 14784416 wrote:
am not much familiar SQL code through VBA.

If you do not know how to code then you need to hire someone. There is likely no complete solution that you can download based on your requirements, it will have to be built.

Social Media - A platform that makes it easier for the crazies to find each other.

Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.

Re: How to update sql server data from excel data using VBA code by all User

Member 1478441627-Mar-20 2:17

Member 14784416

27-Mar-20 2:17

Thanks for your reply, ok i will do my own part, if any error,let you know Laugh | :laugh:

event handler

Member 1356965021-Mar-20 23:16

21-Mar-20 23:16

I have a richtextbox named
input RTB that I want to associate
its event input RTB.ChangedText to a handler programmatically.

and then it want to use
addhandller and removehandller
to enable the event and to disable it.

can someone help me to achieve
this.

Thank you very much.

Richard MacCutchan21-Mar-20 23:31

AddHandler Statement - Visual Basic | Microsoft Docs[^]

21-Mar-20 23:31

Member 1356965022-Mar-20 0:24

22-Mar-20 0:24

Thank you for the response.

before I post my question I browsed Microsoft doc and followed its but that worked
partially .

I succeed to addhandler and it works.

but the removehandler was failed
and the event continue to work.

what I want to achieve is
when my app get input from the user I want
that myevethandler work and
when my app do processing
I want that myeventhandler do
not work.

Again thank you very much

Richard MacCutchan22-Mar-20 0:33

22-Mar-20 0:33

Sorry, but we cannot guess what you have done wrong. The documentation explains how to use both AddHandler and RemoveHandler. You need to provide full details of what you are doing and why it is not working.

Member 1356965022-Mar-20 1:04

22-Mar-20 1:04

Hi Richard MacCutchan 

This is what I done

Public Class myForm


   Public Sub New()
     MyBase.New()

     'This call is required by the Windows Form Designer.
     InitializeComponent()

     'Add any initialization after the InitializeComponent() call

     ' InputRTB is a richtextbox in myForm

     AddHandler InputRTB.TextChanged, AddressOf NewInputTextChanged

   End Sub

    ' slTextBox is a textbox in myForm

    Private Sub NewInputTextChanged        ' my event handler
         slTextBox.Text = InputRTB.Text.Length.ToString
    End Sub

    ' DoButton is a Button in myForm
    Private Sub DoButton_Click(sender As Object, e As EventArgs) Handles DoButton.Click

      RemoveHandler InputRTB.TextChanged, AddressOf NewInputTextChanged ' disable eventhandler
      DoProcess()
      AddHandler InputRTB.TextChanged, AddressOf NewInputTextChanged ' enable eventhandler

    End Sub

   . . .

End Class

Thank you very much

Richard MacCutchan22-Mar-20 4:03

22-Mar-20 4:03

Look at your code in the button click event, it does the following:
1. Removes the handler
2. Calls some method
3. Adds the handler back again

RemoveHandler InputRTB.TextChanged, AddressOf NewInputTextChanged ' disable eventhandler
DoProcess()
AddHandler InputRTB.TextChanged, AddressOf NewInputTextChanged ' enable eventhandler

So as soon as this event is complete you have the handler catching every TextChanged event again.

Member 1356965022-Mar-20 4:18

22-Mar-20 4:18

its ok after the method is finished.

but the problem is inside the method that had access the inputRTB
textbox and cause interference
because of the event continue
to work a d that seams as removehandler called before the
method not working.

I'm thank you very much
for your efforts to help me.

Richard MacCutchan22-Mar-20 4:47

22-Mar-20 4:47

Sorry, but I don't understand. And I have no idea what the DoProcess method is doing.

Member 1356965022-Mar-20 5:01

22-Mar-20 5:01

DoProcess manipulate the text of inputRTB and because the event still work
that cause DoProcess to be hanging.

When I run DoProcess without these addhandler removehandler events
DoProcess run smoothly and quickly.

But with them its hang.

again I'm very thank you.

Richard MacCutchan22-Mar-20 5:12

22-Mar-20 5:12

See my message above.

Richard MacCutchan22-Mar-20 5:11

22-Mar-20 5:11

OK, I have got it working (I think). You need the following changes to your code:

' Add the following at the beginning of your class
Dim handler As EventHandler  '  create an event handler type

Public Sub New()
    MyBase.New()

    'This call is required by the Windows Form Designer.
    InitializeComponent()

    'Add any initialization after the InitializeComponent() call

    ' InputRTB is a richtextbox in myForm

    handler = AddressOf NewInputTextChanged ' set the address of the handler
    AddHandler InputRTB.TextChanged, handler ' use the variable, NOT AddressOf
End Sub

' slTextBox is a textbox in myForm

Private Sub NewInputTextChanged()        ' my event handler
    slTextBox.Text = InputRTB.Text.Length.ToString
End Sub

' DoButton is a Button in myForm
Private Sub DoButton_Click(sender As Object, e As EventArgs) Handles DoButton.Click
    ' use handler NOT AddressOf
    RemoveHandler InputRTB.TextChanged, handler ' disable eventhandler
    DoProcess()
    AddHandler InputRTB.TextChanged, handler ' enable eventhandler

End Sub

Member 1356965022-Mar-20 5:48