Field Value Relationship

[davismar]

Crystal Reports XI

I have a field name = tbl.InvoiceDetail.ItemID

What I need to do is this:

If the the above field name "contains" a "U" , I need to have the result be the above but without a "U". (i.e. the U needs to be stripped off the end?)

Example:

Field value - result is 0890043U

If this is the result, I then need the formula to use
0890043 instead.

Any ideas?

 

[Madawc]

Try

Replace(tbl.InvoiceDetail.ItemID, "U", "")

This will have no effect if there is no U.



Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[davismar]

Yes - thank you. That worked.

I have another question related to this for my next step:

If the above {@ItemID} formula is "True" (i.e. it contains a "U", I need the base cost for the ItemID without the "U" to be used in another formula: {@Total COGS}.

Here are the formulas I have right now:

{@ItemID} = Replace({tblInvoiceDetail.ItemID}, "U", "")

{@Total COGS} = {tblInvoiceDetail.Quantity}*{tblPriceBook.BaseCost}

{@UItemID} = if{tblPriceBook.BaseCost}=0 then instr ({@ItemID},0 *{tblPriceBook.BaseCost})

The last formula is the one I need assistance with.

 

[Madawc]

Try

Replace({tblInvoiceDetail.ItemID}, "U", "") <> tbl.InvoiceDetail.ItemID

When displayed, it will show True or False. But you can just reference it as @FindU for any logic.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[davismar]

Madaec,

Let me make sure I understand your reply:

Originally, I had these formulas:

1.{@ItemID} = Replace({tblInvoiceDetail.ItemID}, "U", "")

2.{@Total COGS} = {tblInvoiceDetail.Quantity}*{tblPriceBook.BaseCost}

3.{@UItemID} = if{tblPriceBook.BaseCost}=0 then instr ({@ItemID},0 *{tblPriceBook.BaseCost})

Are you saying I would replace my original formula above for {@ItemID}with

Replace({tblInvoiceDetail.ItemID}, "U", "") <> tbl.InvoiceDetail.ItemID

?

 

[lbass]

This makes no sense. Please show what you want to see for a result based on the following mock data:

Item ID Base Cost Qty Result
123 2 25
123U 2 50
234 0 10
234U 0 20
234X 5 10
356U 10 4
356X 8 2

-LB

 

[Madawc]

If you want to spot an U, you check for a field that is different after edditing. That's what I meant.

You could also use the Right command to get the last character, U or otherwise.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[davismar]

LB -

Item ID Base Cost Qty Result
123 2 25 50
123U 2 50 100
234 0 10 0
234U 0 20 0
234X 5 10 50
356U 10 4 40
356X 8 2 16

However, if 123U has "0" base cost, I want the formula to pickup the base cost of item 123.

Therefore assuming 123U has "0" base cost, but item ID 123 has a base cost of 2, then the formula would look to itemID 123's base cost of $2.00 and would use that value in the calculation for every Item ID that contains a "U" in the field.

 

[lbass]

Please show sample data similar to the above that better illustrates the issue. I'm wondering whether there could be cases like this:

Item ID Base Cost Qty Result
234 2 10
234U 0 20
234X 5 10

...where there are two values in a non-U 234 item.

What kinds of variations are there in the ItemID itself--is it always a certain number of numbers followed by one letter or none?

-LB

 

[davismar]

LB -

We will only have Item ID 234 or ItemID 234U, not X.

Item ID Base Cost Qty Result
234 2 10 20

234U 0 20
Result - If ItemID = 234U, use Qty of ItemID 234U of 20 * the base cost $2.00 of item ID 234.

234X 5 10 = X - does not apply.

Thanks!

 

[lbass]

Insert a group on the following formula {@ItemID}:

left({table.itemID},len({table.itemID})-1)

You can suppress the group header and footer. Then create a formula to replace the base cost field:

maximum({table.basecost},{@ItemID})

The result would then be:

maximum({table.basecost},{@ItemID})*{table.qty}

-LB

 

[davismar]

I made the changes noted.

The Item ID of 0890043U is now 0890043, which is ok.

But the base cost still shows $0.00.
It should show $113.40.

Here is the formula I created called {@BaseCostNew}

maximum({tblPriceBook.BaseCost},{@ItemID})

Also - I forgot to mention that the base cost of item 0890043 should result in .80 x the base cost.

Any other ideas?

 

[lbass]

You have to replace {table.basecost} in the detail section with the maximum formula.

The item ID should not have changed. You were supposed to GROUP on the {@ItemID}, and then suppress the group sections.

Are you saying a non-U item should be .8*base cost, while a U item should be 1*base cost?

Please show a few lines of actual data.

-LB

 

[davismar]

1. You have to replace {table.basecost} in the detail section with the maximum formula.

Instead of {table.basecost} I now have the formula {@basecostnew} = which equals the formula:
maximum({tblPriceBook.BaseCost},{@ItemID})

The item ID should not have changed. You were supposed to GROUP on the {@ItemID}, and then suppress the group sections.

For the {@ItemID} formula, I went to Group Expert which now shows Group By {@ItemID}. Then when I selected the Options Tab - then Options again, it shows Customize Group Field Name = check box is on, Use a Formula as the group name. The formula used is:
left({tblInvoiceDetail.ItemID},len({tblInvoiceDetail.ItemID})-1)

Are you saying a non-U item should be .8*base cost, while a U item should be 1*base cost?

No.
A non-U item (i.e. 0890043 = $113.40)
A The U item (i.e. 0890043U) should reflect $113.40 x .80 as the result for the base cost.

 

[lbass]

No, don't use customer group name! Create a formula {@ItemID} in the field explorer:

left({tblInvoiceDetail.ItemID},len({tblInvoiceDetail.ItemID})-1)

...and insert a group on this formula.

Replace {tblPriceBook.BaseCost} with this formula {@basecost}:

if instr({tblInvoiceDetail.ItemID},"U")=0 then
{tblPriceBook.BaseCost} else
maximum({tblPriceBook.BaseCost},{@ItemID})*.8

Then the result field should be replaced by this formula:

{@basecost}*{tblInvoiceDetail.Quantity}

-LB

 

[davismar]

I made all of the changes.

My @basecost still displays a value of $0.00 instead of $90.72 ($113.40 x .80)

 

[lbass]

Better explain exactly what you have done, and show the content of all formulas. Identify where you are creating them and where you are placing them, etc.

-LB

 

[davismar]

Formula: BaseCost (Placed in the Details Section)

if instr({tblInvoiceDetail.ItemID},"U")=0 then
{tblPriceBook.BaseCost} else
maximum({tblPriceBook.BaseCost},{@ItemID})*.8

GroupName
@ItemID
Placed in Group Header # 1
Group Header # 1 and Group Footer # 1 - suppress section.

Formula: ItemID (Placed in the Details Section)
left({tblInvoiceDetail.ItemID},len({tblInvoiceDetail.ItemID})-1)

Formula: TotalCOGS (Placed in the Details Section)
{@BaseCost}*{tblInvoiceDetail.Quantity}

 

[lbass]

Please clarify what field/formula you actually grouped on. You should be selecting {@ItemID} and inserting a group on it.

Please also show some examples of the data at the detai level, including the actual {table.item ID} as it appears in the detail section after grouping on {@itemID}, the basecost field as well as the basecost formula, the qty field and the result.

-LB

 

[davismar]

First, the ItemID field and grouping.

The field name, itemID, is in the detail section.
Then, I went to Report, Group Expert.
This shows a grouping on field {@ItemID}-A
When I select "options", the common tab shows itemID - A order
The box that says "use formula as group sort order" is NOT checked.


Examples of data at the detail section level shows the following:

Item ID Item Description Qty Base Cost Total COGS

0890043U RAspire 22B Phone 1 68.00 68.00

The actual 0890043 item ID has a base cost of $113.40.
Therefore, the new base cost result (of the "U" item should be $113.40 * .80) or $90.72.