Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Macola to Crystal How-to

Macola to Crystal How-to

Macola to Crystal How-to

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:

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.

RE: Macola to Crystal How-to

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

RE: Macola to Crystal How-to

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?

RE: Macola to Crystal How-to


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close