| Author |
Message |
will99
Newbie
Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
|
   Topic: Having Problems with the SUM with 2 differnt table
Posted: 06 Jan 2016 at 8:35am |
|
Hi Guys, i hope somebody can help me here.
I have 2 tables {INVENTORY} and {SALESORDERSITEMS}. My goal is to have the following on the report:
{inventory.Item} | {inventory.description} | sum({inventory.Onhand} | sum(SalesOrderItems.OrderQty}
Inventory.ONHAND has to be added cause we use multiple warehousing and the {INVENTORY} table has a WHSE field.
So if i stick only to the inventory table, all the SUM's i use for ONHAND, ONPURCHASE etc are working perfectly but AS SOON as i use a field
from SalesOrderItems table, all my SUM's are MULTIPLIED by hundreds? For example the total on hand in all my warehouse for the same product is
supposed to be 650 pcs but the reports gives me 27000 pcs. How can i fix this? I tried to GROUP them by Item #, by WHSE # etc. etc. but nothing seem to work.
I have tried also subreports but still nothing...
i'm missing something but i have no idea how to do it and this is creating same problems in other reports so if i find a solution for this one, i will have the solution for the other reports!
i appreciate your help
Will!
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Jan 2016 at 11:10am |
|
when you add a field from a second table it will 'enforce' the join which may cause your records to end up with 'duplicate rows'if one table has multiple matches from the other table. If you did not join the table it will make a Cartesian result.
If you did join the tables you can use Running totals (or shared variable formulas) to get the values you want.
if you do not to or cannot join the tables you may have to consider sub reports but not sure how that will work as you need some way to match the content.
|
|
IP Logged |
|
will99
Newbie
Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
|
Posted: 06 Jan 2016 at 1:25pm |
|
Thanks for the infos, I'm trying now the Running totals but its simply not working, it gives me always the same results, My tables are joint and when the joint is not there, the results from the INVENTORY tables are good but the results that are supposed to come from the SALES table are not, not even close. The Running Total i have used all combination that i thought possible but nothing.
Also, what's a CARTESIAN result? If i don't joint the tables, Crystal Reports advise me to JOINT them.
I'm a bit lost..
Will
Edited by will99 - 06 Jan 2016 at 1:25pm
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Jan 2016 at 2:32am |
|
A cartesian result is every possible combination between the two tables. basically every row from table one joined to evert row in table two.
Not that running totals only work in the detail section or footers. If you place them in headers they only include the first row of the data (relevant to the header it is used in).
Your running total will likely be set to evaluate on 'a change of a field' then pick the PK field from the relevant table.
If you are grouping the data (likely you need to do this to hide the details section to hide the duplicate rows) then you can place the RT in the group footer and have it reset on change of group.
|
|
IP Logged |
|
will99
Newbie
Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
|
Posted: 07 Jan 2016 at 11:06am |
|
Thank you Dblank but do you think i have to absolutely Joint the 2 tables?
I'm doing it without joint and nothing seem to work. Obviously when i join 2 tables the numbers are just all screwed up and way too up.
Thanks
Will
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Jan 2016 at 3:07am |
|
Unless you are going to use sub reports you will need to join the tables.
Do you have a'products' table too?
One of the things you have to be cautious of is possible excluding products that are not in one of the two other tables. Not knowing your data and working on some assumptions of how it managed I would...
left join use the 'products' table and left join both the {INVENTORY} and {SALESORDERSITEMS} to it.
I would
group on the products.productid
create two running totals to calculate each of the values you want
-sum({inventory.Onhand} and
--sum(SalesOrderItems.OrderQty}.
use "on change of file" using the PK of each table in the RT set up as the evaluate for option.
Set each to Reset on change of group
place in the detail section to verify it is executing as desired
place in the group footer for display
suppress the group header and details
|
|
IP Logged |
|
will99
Newbie
Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
|
Posted: 08 Jan 2016 at 11:10am |
|
Thank you very much DBlank, i'm advancing really fast and MOST of the numbers are pretty good but sometimes, for no reason, my RT's are BLANK... I did trying the 0 as a default value but nothing seem to work!
Would you have an idea why this would happen?
thanks
Will
Edited by will99 - 08 Jan 2016 at 1:24pm
|
|
IP Logged |
|
will99
Newbie
Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
|
Posted: 09 Jan 2016 at 4:07am |
|
Thank you very much Dblank, you have answered all the questions... and i finally finished it with your help. I'm just a beginner with Crystal so i appreciate all you guys help..
You have been such a good help for me!
thank you
Will
|
|
IP Logged |
|
will99
Newbie
Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
|
Posted: 09 Jan 2016 at 4:08am |
|
FOUND IT!
thanks
Will
|
|
IP Logged |
|
|
|
