Duplicate Order Numbers?? 1

[smtoomey3]

I'm trying to create a report which gives a total qty on specific items sold for a timeframe. The issue is, I am seeing duplicate order numbers and lineitems for those orders which is screwing up my count, I believe this is happening because of backorders or having an order shipped in increments although I could be wrong, does anyone know how I might remove the unneeded duplicate order numbers which are creating the unneeded line items. I've tried using different combinations of OELINHST and IMITMIDX...

 

[randva]

I use a formula to determine the quantity shipped:

if {OELINHST.SALE_AMOUNT} < 0
then -1 * {OELINHST.QTY_ORDERED}
else {OELINHST.QTY_TO_SHIP}


Credits(negative sales amounts) count against sales. Using the Qty_to_ship number should eliminate your problem with duplicates in Qty_ordered from backorders.

 

[smtoomey3]

Thank You Very Much,,,that worked perfect.

 

[smtoomey3]

I'm actually still having trouble,,,,kit seems that formula removed some of the duplicates but not all, are there any other area I should be looking at to try to remove these duplicates from the reports.

 

[abaldwin]

I think you need to group by order number AND inv_no since partial shipments will be in teh oelinhst with the same order number but different invoice numbers.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[smtoomey3]

Is there a different table I could use to get this information,,,maybe an AR table,,or some other OE table that won't have this duplicate issue??

 

[dgillz]

Andy is right on. The group by invoice # will accomplish what you want. If this does not work, please share your tables, linking and connectivity (native or ODBC).

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[kscheeler]

Based on your original statment, you only want to group by oelinhst_sql.item_no and then sum the qty. To calculate the qty shipped / credited, I use the following formula:

if oehdrhst_sql.orig_ord_type = 'C' then oelinhst_sql.qty_ordered * -1 else oelinhst_sql.qty_to_ship

This will give you the total qty for each item. If you wanted it by order and then item, you would then group by oelinhst_sql.ord_no and oelinhst_sql.item_no and sum the quantity formula.

As you want more detail or sub totals, increase your number of groups.

Kevin Scheeler

 

[dgillz]

Kevin,

I agreen that grouping on item no will give you the total for that item across all selected orders/invoices for the given time frame. However let me offer an alternative formula for quantity:

{OEORDLIN_SQL.qty_to_ship}-{OEORDLIN_SQL.qty_ret_to_stock}

I am not sure you can have a backorder on a credit memo, but if you can the formula above would cover all situations.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[kscheeler]

But a credit is a credit, whether you return it to stock or not.

Kevin Scheeler

 

[smtoomey3]

I used the original formula and Linked Inv_no in the OEHDRHST to Inv_no in OELINHST, and the problem went away, the issue seemed to be due to duplicate order numbers, by linking the Inv_no it somehow got rid of these duplicates..as for why I'm not sure...I have some ideas,,but I'll leave the explanation up to the MVP's

 

[smtoomey3]

I am not grouping by invoice number,,,Group 1 is Item#,,,Group2 is Item Description & Group 3 is order Date,,,with all details hidden and just item number product desription and Qty sold in Group 2 for viewing.

 

[dgillz]

The link between the 2 tables should always include inv_no, otherwise if you simply link on ord_type and order_no, then there will be record inflation whenever there are backorders.

It is the combination of these 3 fields that constitute a unique record in these tables. You should always know what field or fields are the primary key in order to construct an effective and accurate report.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[stravis]

Just a note on primary keys of tables in Macola. Macola does not use an actual primary key. They create unique indexes and so it is not clear what the primary key is for a table if you are not familiar with the Macola database. To find the primary key for a table you can look at the first index (the one numbered #1) and this should be considered the primary key for the table.

Scott Travis

http://www.infoSpring.net