|
|
What is the error message on the Client's machine ?
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
How about:
.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
|
|
|
|
|
So use this within my invoice loop?
Hmm ...
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
No, in the outer query:
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
|
|
|
|
|
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.
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
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)
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
|
|
|
|
|
jkirkerx wrote:
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:
Select Case True
Case item.FPRICE.Equals(0)
pI.FExtMargin -= item.FCOST * item.FSHIPQTY
Case Else
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:
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
Thanks for your reply, ok i will do my own part, if any error,let you know 
|
|
|
|
|
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.
|
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
DoProcess()
AddHandler InputRTB.TextChanged, AddressOf NewInputTextChanged
So as soon as this event is complete you have the handler catching every TextChanged event again.
|
|
|
|
|
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.
|
|
|
|
|
Sorry, but I don't understand. And I have no idea what the DoProcess method is doing.
|
|
|
|
|
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.
|
|
|
|
|
|
OK, I have got it working (I think). You need the following changes to your code:
Dim handler As EventHandler
Public Sub New()
MyBase.New()
InitializeComponent()
handler = AddressOf NewInputTextChanged
AddHandler InputRTB.TextChanged, handler
End Sub
Private Sub NewInputTextChanged()
slTextBox.Text = InputRTB.Text.Length.ToString
End Sub
Private Sub DoButton_Click(sender As Object, e As EventArgs) Handles DoButton.Click
RemoveHandler InputRTB.TextChanged, handler
DoProcess()
AddHandler InputRTB.TextChanged, handler
End Sub
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
