Crystal Not Adding Material Qty

[bstafford21]

Using Crystal 9 in conjunction with Magic eDeveloper 9.3 Pervasive database.

My problem is the particular database is for stock Qty. The database file I must use for the report has on some material more than 1 row showing the qty of the material. The reason for this is due to Lot No.'s used will be different when we receive new material. There for when trying to retrieve the data, example Material 1 Lot No. 1 has Qty of 10. Then Material 1 Lot No. 2 has Qty of 1. The total on the report should be 11, but crystal is only pulling up the Qty for Lot No. 1 and not both Lot No. 1&2 that is needed.

Do I need to create a formula to pull both into the Total?
I currently am using the field Material Inventory Qty Total in the Footer of the report.

In the details area is listing each of the materials and the Qty for Open Work Orders and the material used and required for the Job. But I have made a Group for each type of material and it will list all Work Orders for that material. That all works fine except for materials that have in the Stock data file that I am using in the footer to tell me the current Inventory Qty, if it has > 1 Lot No. it will only pull the first Qty of the 1st Lot not 2 or more? How can I get this to add more than 1 Lot Qty to the footer for that particulr Material Invertory Stock Qty?

Thanks for any help and hope I explained well enough. Trying to finish this today am on a deadline.

Cheers
Bill

 

[IanWaterman]

Group Report by Material Field

Move common data to be displayed from Details to Material Group footer and suppress details.

Instead of placing qty in Material footer add a summary to sum qty.

Should now be OK.

Ian

 

[bstafford21]

Thanks Ian for the tip.

I tried this but now it will not show all the Open WO's that require this information.

Need to show in the detail section the following if a WO is on that part that is in Production.

Due Date SO WO Finished Part No. Current Finished Part No Inventory Qty Material Req Qty

In the footer I put the Material Part No. Inventory Qty

But because there is on some material more than 1 batch it will only bring in 1 Qty not adding in or pulling in individual Batch Qty's for that called Material.

Everything works except for that in my original setup.

Is there something I can do, maybe formula to pull all the qty's listed in my Stock Qty datafile?

Cheers
Bill

 

[IanWaterman]

Is the sum in your report footer a Running total or just a standard summary which sums?

If and RT check settings, ie when does it evaluate if it should be every record, sounds like it is set to evaulate on change of material. If not correct to evaluate fo r every record then make sure its on change of LOT.

Ian

 

[bstafford21]

The current Material Inventory is strictly a database file field. It is not a sum at all nor a running total.

This datafile field has only quantities for all materials by batch no's. Some have 1 field for the Material Qty and some have 2 or more fields for the Material Qty. Just trying to pull in all fields that exist for each Material and it is not doing that.

 

[IanWaterman]

But you said all records were appearing as all lot numbers for each material was appearing in details?

I thought it was your sum of the material qty was not in the report footer which was not totalling all lots correctly?

If I have misunderstood you and lots are not appearing in detail then its likely to be a join issue.

How is you inventory table linked to your work order table?

Ian

 

[bstafford21]

Sorry for the missunderstanding. My database file Material Inv Qty is linked and uses the field Inv Qty.

This works fine in the footer and just placed the field there. The detail fields work fine.

But since I am pulling a field related to Inv Qty, it works but for only 1 Qty of 1 batch number. The database file has this as a field as well, but not used.

How to pull in all material Qty fields related to batch No. Should I use this batch No. field and just suppress it?

 

[IanWaterman]

I am sure this relates to how you have linked work order data to stock data.

Please paste SQL statement from

Database -> Show SQL statement

Ian

 

[bstafford21]

How can I paste the data to this post, it won't let me paste?

Bill

 

[bstafford21]

I have the following in my Crystal 9 Report

GROUPHEADER 1
COMPONENTS.COMPONENTS PART No

DETAILS
(1)SO_Items.Due Date (2)SO_Items.SO No (3)WO.WO No.
(4)Part.Part No. (5)SO_Items.Qty (6)COMPONENTS.Qty Var
(7)@Material Qty ={SO_Items.Qty}*{COMPONENTS.Qty Var}

GROUP FOOTER
Current Material Inv = Sum of @Material Qty


Example:
Due Date SO WO PartNo SO Qty Comp Var @MatQty
12.12.07 12345 54321 0001-00001 5,000 0.16 800
12.12.07 12345 54321 0001-00001 5,000 0.16 800

Due to some parts requiring 2 Component Material Required to
produce the finished part no 0001-0001, then everything in DETAILS section is listed twice (DUPLICATED) 2 lines with the same thing.

I have suppressed Duplicates, but "Total Material Req"
is still double what should actually be there.

Also have problem with Current Material Inventory of the Component Stock that is used to make the finished part.

Thanks for any help and hope this makes clear what I am using and doing.

Cheers
Bill

 

[IanWaterman]

I would think it is duplicating because of the 2 lot numbers.

When you suppress duplicates the data is still there so a standard summary will duplicate. YOu will need to replace summary with a Running total.

In the condition evaluate on change the On change of radio button and the drag PartNo field into box

For a group subtotal get it to reset on change of relevant group, for Grand total create another RT and this time have reset set as never.

Still not sure what you are trying to do with Inventory ?

Ian

 

[bstafford21]

The Group Footer needs to show the Raw Material Inventory.
This is coming from Stock.QtyonHand

However when I add this field, I creates duplicate lines for each item and will not display all the inventory.

The reason for the duplicate lines for each detail field item is because there is more than 1 Qty line in the Stock.QtyonHand database file.

There can be seperate lines in Stock.QtyonHand because of the date the Qty may have been received. The Stock.QtyonHand data field also has the field Stock.PartNo which is relevant but do not need to display. The Header Group is already displaying the Material Part No.

How do I display all the quantities in Stock.QtyonHand without creating duplicate lines for each detail? I just need to know what the total is for that Stock.PartNo

 

[bstafford21]

I have now modified the Group Footer and placed Stock.PartNo from the database field.

This now gives me what I need in the Group Footer for the
Total Stock.Qty on Hand

The problem this creates is causing the duplication in the details now because this particular part requires 2 Stock Materials to make it. So for each WO & SO it thinks it should add and show everything twice.

How do I remove the duplicate line?
Common to each detail line is listed above in a previous explanation which shows everything used.

Thanks for any help on this.

 

[IanWaterman]

YOur example above

Example:
Due Date SO WO PartNo SO Qty Comp Var @MatQty
12.12.07 12345 54321 0001-00001 5,000 0.16 800
12.12.07 12345 54321 0001-00001 5,000 0.16 800

makes no mention of yor inventory so I have no idea what you want to see?

Please show an example of what you are getting now and what you want to remove?

Where do you want your inventory total to appear?

Ian

 

[bstafford21]

Ian,

The Inventory Total of the RawMaterial used does not need to be in the Details Section.

I have placed the Stock.Qty on Hand (Raw Material Inv Total) in the Footer Section. It works fine but causes duplicates when I place it. If I take it out, then everything is normal and only 1 instance (no duplicates)

Do I need to put it in Details and just suppress it, cause there is not need to see the information there, but just want to see The total and using a Group Section the Raw Material name is the Header section.

 

[IanWaterman]

If your raw material total is OK then no need to add to detail.

Add in a new group Part No suppress header
Move all the datafields you have detail into the Pt No Group footer and suppress details.

As I mentioned before you will need to use Running Totals instead of summaries other wise you other totals will double up.

Ian

 

[bstafford21]

My mistake on the Group Footer "Raw Material Inv Total"

It was not correct and only will display 1 value out of the 2 values that are stored in the database field.

This database field has 2 quanties in the database. Need these 2 added together coming into the report as 1 Value.

This is the field causing the duplication. Nothin worked and tried above, so PartNo (Finished PartNo) did not work.

What else can I evaluate then. Try them all