Math Formula - Creating Record 2

[RedHeadedStepITChild]

I've run into a bit of a problem with a report.
I am using CRXIr2 connecting to DB2 (AS400) via OBDC
Two tables:
IIM(item master) :: FSO(shop order)
Link: FSO.SPROD == IIM.IPROD (item number = item number)
Formula to find LabelQty:
RoundUp(TotalQty/QtyPerLabl) TotalQty comes as an entry in the shop order, and QtyPerLabl is a package code, located in the IIM.

Here is a visual of what the report looks like (it will probably get distorted when I post it)

ShopOrd# DateDue Item# ItemDesc TotalQty LabelQty QtyPerLabl
-------- ------- ---- -------- -------- -------- ----------
1234 2/4/08 12 Chair 211 36 6


Notice the last field -- Quantity per Label. We pack 6 chairs to a box. In an order with 211 chairs, that is 35 labels with a qty. of 6 and 1 label showing a qty. of 1, so you can see that this line of data would figure to be wrong. Is there a way to have CR "create" a row when this issue happens, so that it looks something like this:

ShopOrd# DateDue Item# ItemDesc TotalQty LabelQty QtyPerLabl
-------- ------- ---- -------- -------- -------- ----------
1234 2/4/08 12 Chair 211 35 6
1234 2/4/08 12 Chair 211 1 1

I have some psuedo-ideas for formula(s). Bear with me, this is probably 4 formulas all condensed into one IF/THEN/ELSE statement, and might be part of my problem...having everything all condensed in my head. But, at any rate, here goes.

IF totalQty/packCode = Integer THEN
labelQty = totalQty/packCode AND
qtyPerLabl = packCode
ELSE
IF totalQty/packCode = in[decimal remainder] THEN
labelQty = (RoundUp(totalQty/packCode))-1 AND
qtyPerLabl = packCode AND
remainingLabl = totalQty -(qtyPerLabl * labelQty)

I think the above will solve the issue to come up with the correct per label quantities. However, I am not 100% sure how to do it in CR syntax.

Also, when dividing out the totalQnty/packCode = Integer, I abviously wouldn't need another record showing the one last remaining label. However, when it results in an decimal, I will need to show a record pointing out one more label and the quantity of product on that label.

I would appreciate any help very much! I hope at least 1/2 of this made some sort of sense of what I am trying to accomplish...if its even possible.

Thanks!

Jason

 

[PatriciaObreja]

I think that each field that is displayed in the report should be replaced by a formula.

The formula will return the value of the field when we don't have a remainder of qty, and it should use a concatenation to simulate a new record, when we have a remainder.
eg. {ShopOrd} & chr(13) & {ShopOrd}
will return
1234
1234

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[kskid]

integer divide ({totalqty}\6) will give you the numbef of labels with pack quantity of 6

remainder({totalQty},6) > 0 will give you the amount less than a 6 pack quantity.

 

[smcnulty2000]

If you need to 'create another row' right under the first, the best way to do that is to add another section and then suppress it unless it needs to show this extra remainder information.

Say you have put all of the above into a detail section. You go to your section expert, then click on details, then the insert button.

Then you go back to your detail section, copy all of your fields/formulas except the last two from detail A into detail B.

Then in detail B you replace the Label Qty and Per Label Qty with your new remainder formulas that kskid is helping you with.

Then to do a conditional suppress, you go back to your section expert, click on Details B, go to the formula button on the right, enter in a formula that is true if the remainder formula is equal to zero.

 

[RedHeadedStepITChild]

Awesome!

Thanks so much kskid and smcnulty2000.

Let me apply these suggestions to the report, and I'll get back to you!

Thanks

Jason

 

[RedHeadedStepITChild]

Awesome...again.

Both of your suggestions worked perfectly.