SydneyMcConnell

Hey, guys.

I'm in a software engineering class, and we participate in competitions with BPA, the Business Professionals of America. I am in the "Software Engineering Team" competition, with two of my classmates, and the project is an Accounts Payable system for a fake energy drink company, "Revcorp."

My current task for the program is the check writing. My weapon of choice is Crystal Reports.
I've got the layout and vendor information done, as well as the ability to obtain the items for the invoice the check is for and generate the check stub. The only thing left is getting the payment amount, which I must get from the Unit Price * Quantity Recieved, from the invoice.

So far, the closest I've been able to get is seperate aggregate functions on Unit Price and Quantity:


This returned a value. The only problem is that it's the wrong value.
So, I need to know if there is a way to do this, and if so, how?

Any help would be greatly appreciated.

Sane

I'm sorry I don't have an answer for you, but maybe I can help you realize the mistake you made:

SUM will return the sum of all elements in the inputted array.

If you have an array of unit prices, and an array of quantities.


And you want to find prices * quantities.

SUM(prices) will evaluate to 10
SUM(quantities) will evaluate to 14

Therefore, SUM(prices) * SUM(quantities) is 140.

However, that's not the right answer, as you've pointed out. You want SUM(prices * quantities) which is 1*4 + 2*4 + 3*3 + 4*3 = 33.

BUT! ... That'll probably give you an error. I don't believe VB natively supports array multiplication like that, so you'll most likely have to use a for loop to multiply each element individually, and sum the result. A function might exist for this, but sorry I haven't used VB in years... Hope this helps.

SydneyMcConnell

If I were doing the code in VB, I wouldn't have a problem at all. The problem is that I'm doing it in Crystal, which is extremely akward for me, because the syntax just seems so odd. It's not allowing me to do what I want to do.

Do you think it would be possible if I were to try:


That seems like it shouldn't work, though, because I always get an error when I attempt to enclose more than one field in a sum with a math operator. Which doesn't suprise me, I suppose.

I'm at such a loss when it comes to Crystal, and it's as though no matter where I search, there are no tutorials, books or online, that cover something like this. =/

__________________
Tier 2 Hardware Technician
Net Effects, LLC
Brunswick, Ohio

Sane

Oh, I see. Sorry I'm not familiar, so I didn't pick up on that it was Crystal syntax.

I see your problem now.

Well, SUM(SUM(Prices) * SUM(Quantites)) won't work either, because if you go back to my previous example, this statement would evaluate to SUM(140), which doesn't make a lick-o' sense.

SUM takes in an array, and produces a sum. Period. So in SUMmary (ahahah ) you can't use the SUM function.

If Crystal offers a way to do a for loop, or nest a lambda function in a generator statament... it would be possible. And it must be possible, or else Crystal would be just plain lousy.

After some searching on Google, I found this link:

http://www.inetsoftware.de/products/...la/syntax.html

So see if you can fiddle around with the for loop. You basically want to translate this to Crystal:

SydneyMcConnell

Well I just amazed myself, because it took me this long to realize that basic syntax refers to the language and not the simplicity. Honestly I didn't know what they meant by basic syntax.

But looking at that, I think it may be quite a bit easier. Now all I have to do is find out if it will let me do a loop in a formula field. =]

Thanks a bunch, I'm not sure if that's the solution I'm looking for, but I'll be able to let you know tomorrow between about 11:45 and 2:05.

Again, thanks.
You were a great help. =]

__________________
Tier 2 Hardware Technician
Net Effects, LLC
Brunswick, Ohio

Sane

Well, not sure if you saw, but a subtlty in the link I sent you to alludes to it being possible. A Crystal statement is ended by a semicolon, not a line break.

Therefore, everything could be crunched on to one line with Crystal syntax and put into a formula field. Whereas with Basic syntax, you need multiple lines, and could not do it.

Example of a for loop with Crystal:


And your welcome. Hope it works out for you.

SydneyMcConnell

Thanks again! I didn't even notice that. =P
That will definitely help.
Although I may be able to do it with Basic syntax since I haven't even been using semicolons to end lines. >_>
So, that could be my problem.
Either way, I think I can do it now.
Thanks a ton!

__________________
Tier 2 Hardware Technician
Net Effects, LLC
Brunswick, Ohio

SydneyMcConnell

Update:

I attempted to use the code in Basic, which worked fine.
I've got the code sorted out, but there are two problems.

1. The calculation for UnitPrice * QtyRecieved is taking the first record, and doing the calculation twice. (Repeats 2.5 * 10 twice instead of continuing to the next record after finishing)
2. The final calculation will not allow me to put the end result into words.

But we're one step closer. I will continue looking, and again, if any help can be offered on this development, it will be much appreciated. I'll continue working and update when progress is made.

__________________
Tier 2 Hardware Technician
Net Effects, LLC
Brunswick, Ohio

Sane

That sounds like a silly mistake to me. Are you sure you're using your iterating variable correctly? Could you post the syntax?

What do you mean "put it into words"? You need 10 expressed as "Ten"? Or you need 10 expressed as "10"?

For the latter, look up "type conversion in VB from integer to string".

SydneyMcConnell

Crystal has a function for putting numbers to words, 10 to "Ten", for example.
The function is ToWords()
It's probably nothing a little type conversion can't fix.

As for iteration, I am using it correctly, as far as I know. I'm running it from 0 to the count of returned rows - 1.

I'm no longer at school, so I can't give exact syntax. I'll post it tomorrow, first chance I get.

__________________
Tier 2 Hardware Technician
Net Effects, LLC
Brunswick, Ohio