Macola to Crystal How-to

[waltervp]

I have been asked to create a report that shows the total number of inventory items sold, both open orders and history using crystal reports. Is there a flag for open items? Where do I pull this data from?

I have tried:
OELINHIST
OEORDLIN

The numbers do not match the numbers that are produced in Macola and I can't seem to understand why. What are the elements of the MAcola report? How does Macola calulate the totals if not from these files?

Thank you very much.

 

[dgillz]

The information is in BOTH of these files. The OEINQORD table is the jey to writing this report.

The OEINQORD file has a field called "Ord-Fg" which has a value of O for Open, H for History, or B for Both. You must write an if-then statement depending on the status of this flag. If it is O, grab the Qty Ordered from the OEORDLIN table, if it is H, grab the Qty to Ship from the OELINHST table, and if is it B for both (meaning you have had a partial shipment) grab both of these values.

Be careful - if you have had multiple shipments against a line item, the open qty in the OEORDLIN file will be inflated. You will most likely want to use a running total here, or variables.

What do you want to do with credit memos? or RMAs?

Please let me know if you have any questions. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[vbajock]

Don, a few questions on your answer to this post -

When an Order is flagged as B, do you mean to add together the Qty Ordered amt in OEORDLIN and the Qty Shipped amt in OELINHST in order to get the correct total?

Also, could you expand a little on the need for a running total and why the amount in Qty Ordered would be inflated?
Thanks
Kirk

 

[dgillz]

Kirk,

Yes B means it is in Both files and you would need to add the amounts together to get the totals.

In terms of record inflation, image that you entered an order for a qty of 100 of a gaven item. Then 10 of these ship today, and then 15 more ship tomorrow.

After the invoices are posted the OEORDLIN file will have one record in it, for a qty of 75 (the backorder). the OELINHST file will have 2 records in it, one shipment of 10 and one of 15.

If you link these two files in crystal, and you display the qty from both files on the report, you will see the following:

Item OEORDLIN QtyonOrder OELINHST QtyShipped
ABC123 75 10
ABC123 75 15
Totals 150 25

Seehow the qty on order is inflated? Thus the need for a running total field, evaluate on change of group=Item, so that the QtyOnOrder would evaluate only once per item.

Also, please note that thisis only an issue when there are multiple shipments against a single line, and there is still a backorder.

Let me know if you have any questions. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com