Przemysław Sidor
asked on
Crystal reports, selecting latest apearence of group in table
I am new in this Crystal Reports thing and I have problem, since my coworker usually doing this stuff is sick at home I need to figure it out my self. I have 2 tables.
First is like this:
Article.ID Article.name Article.buyingprice
1 aa 2.00
2 bb 1.00
3 cc 3.50
4 dd 4.20
5 ee 2.00
And second is like this:
Income.ID Income.article Income.date Income.number Income.quantity
1 2 79100 P1 2.00
2 1 79100 P1 3.00
3 4 79101 P2 2.00
4 3 79101 P2 1.00
5 1 79101 P2 4.00
6 5 79101 P2 1.00
7 2 79103 P3 15.00
What I need is formula for selecting Income.quantity from the latest Income document. For example for article aa it will be 4.00 and for article bb it will be 15. Please save my job folks :)
Report is grouped by article.name.
First is like this:
Article.ID Article.name Article.buyingprice
1 aa 2.00
2 bb 1.00
3 cc 3.50
4 dd 4.20
5 ee 2.00
And second is like this:
Income.ID Income.article Income.date Income.number Income.quantity
1 2 79100 P1 2.00
2 1 79100 P1 3.00
3 4 79101 P2 2.00
4 3 79101 P2 1.00
5 1 79101 P2 4.00
6 5 79101 P2 1.00
7 2 79103 P3 15.00
What I need is formula for selecting Income.quantity from the latest Income document. For example for article aa it will be 4.00 and for article bb it will be 15. Please save my job folks :)
Report is grouped by article.name.
ASKER
Yes, tables are linked using Article.ID. You are correct in your update.
I tried something like
I tried something like
if {income.date} =maximum({income.date}, {article.name}) then {income.quantity}ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I tried what you asked I see every single income, not last. What should i change?
I think it's better to check the output and suggest further.
Is it possible to share the report with some saved data? or a screenshot with what you are getting and what you want will do.
Is it possible to share the report with some saved data? or a screenshot with what you are getting and what you want will do.
ASKER
I tried again and it works, thank you.
ASKER
It works after I backed to original report, because this one was edited 1000 times. I used your solution and it works, thank you very much!
Are those tables linked using Article.ID from first table and Income.article from second table?
If so, try creating a formula (say max_income_qty) and write formula content as maximum(Income.quantity, Income.article).
In the above formula content, replace Income.quantity and Income.article with your actual database fields.
Place the formula in group header section and check the value.
Hope this helps.
Update: I think I misunderstood it, I thought you are looking for the maximum value for each Article ID.
It seems you want to identify the maximum Income ID for each Article ID and then display the corresponding Income Quantity, is it?