Duplicate Results When Creating Formulas Based on Joined 2 tables

[jopav613]

My group on " itemnum" works in a formula I created until I join with another table on itemnum....then when i create and pull in a second formula based on the latter table then the results in the first formula show duplicates, triplicates and sometimes even quintiplicates!

Here's my show sql query: SELECT DISTINCT `orderlines1`.`itemnum`, `items1`.`class`, `items1`.`description`, `items1`.`buyer`, `items1`.`itemnum`, `orderlines1`.`qtyordered`, `orderlines1`.`qtyinvoiced`, `orders1`.`ordertype`, `orderlines1`.`ordernum`, `openpo1`.`poe`, `openpo1`.`qtybo` FROM ((`tov`.`items` `items1` LEFT OUTER JOIN `tov`.`orderlines` `orderlines1` ON `items1`.`itemnum`=`orderlines1`.`itemnum`) LEFT OUTER JOIN `tov`.`openpo` `openpo1` ON `items1`.`itemnum`=`openpo1`.`itemnum`) INNER JOIN `tov`.`orders` `orders1` ON `orderlines1`.`ordernum`=`orders1`.`ordernum` WHERE `orderlines1`.`qtyordered`<>`orderlines1`.`qtyinvoiced` AND `items1`.`itemnum`='20282' ORDER BY `items1`.`itemnum`, `orderlines1`.`ordernum`

Note: Source of duplication seems to be duplicating the same "ordernum" twice

Please help.

Thanks!

[abhilash_kumar]

If you've inserted a Group on the Item Number field, you can create manual running totals.

For example, create a formula to sum the subtotals per group. Place this on the Group Header/Group Footer (If you place this on the Details, it'll also add duplicates):

shared numbervar gsum;
gsum := gsum + {field to sum};

You'd also need a formula to display the summary. This goes in the Report Footer to display the Grand Total:

shared numbervar gsum;

-Abhilash

[abhilash_kumar]

Hi Joe,

You may be missing a join or two and the order of Joins could be changed as well.

Try Joining:

Orders > Order Lines

Orders Lines > Items

Items > OpenPo

You may still see duplicates as there does not seem to be any relationship between an Order and OpenPO. If an Item has multiple rows in the OpenPO table, you'd still get duplicate results

-Abhilash

[jopav613]

Hi Abhilash,

Thanks for your response. Unfortunately I tried all this and yes, there is no way for me to relate the order to openpo tables as although they both contain "ordernum" and some other similar sounding field names "orders" are customer orders and openpo are factory orders so therefore they have NOTHING to do with each other. I find that I can supress the duplicates thereby hiding the rows but the problem is that my formulas which are based on summing the group will still end up adding the duplicate information and I don't want that. Is there ANY way to have my formula not sum up the duplicate rows?