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?

 

[lbass]

I understand what you are trying to do, but I think you are implementing this incorrectly. Please put the following in the detail section and report back with at least four rows of detail level results:

{table.ItemID} {@ItemID} {table.basecost} {@BaseCost}

Also, I thought the base cost was always 0 for "U" items. If this isn't the case, then explain whether the base cost for U items can be more or less than the base cost for a non-U item.

-LB

 

[davismar]

Item ID @Item ID Base Cost @Base Cost

0890072 089007 175.50 175.50

0891042U 0891042 0.00 0.00

0890043U 0890043 85.00 68.00

0910048 091004 116.76 116.76


As far as the base cost for the "U" Item, it is almost always "0". But even it isn't, I still want 80% of the Non-U item as the result.

 

[lbass]

Please respond to this

Also, I thought the base cost was always 0 for "U" items. If this isn't the case, then explain whether the base cost for U items can be more or less than the base cost for a non-U item.

Also, in your example you are showing fields with U's where there are no non-U items--can this occur? If it does, what value would you expect to see for {table.basecost}? Please add to your sample above, and show all values in the groups based on {@ItemID}.

-LB

 

[davismar]

1. The base cost is NOT always $0.00 for "U" items. The base cost for U items is usually less than the base cost for a non-U item.

2. There can be results where there is a "U" item but no Non-U item. When there is a "U" item, the value for the {table.basecost} should be 80% of the base cost for the Non-U item ID.


REVISED SAMPLE:

Item ID @Item ID Base Cost @Base Cost

If 0890072 then 0890072 175.50 175.50
If 0890072U then 0890072 175.50 (175.50*.80)= 140.40

If 0891042 then 0891042 701.40 701.40
If 0891042U then 0891042 701.40 (701.40*.80)= 561.12

If 0890043 then 0890043 113.40 113.40
If 0890043U then 0890043 113.40 (113.40*.80)= 90.72

If 0910048 0910048 116.76 116.76
If 0910048U 0910048 116.76 (116.76*.80)=93.41

 

[lbass]

So the basecost for "U" items can sometimes be greater than the base cost for non-U items?

Your sample isn't presented correctly--you are showing the adjusted base cost for the field, instead of the actual field. So let's say the sample really looks like this. What results would you expect to see:

Item ID @Item ID Base Cost @Base Cost

0890072 0890072 175.50
0890072U 0890072 0

0891042 0891042 701.40
0891042U 0891042 100.00

0890043 0890043 113.40
0890043U 0890043 300.00

0910048U 0910048 116.76

0940048U 0940048 0

-LB

 

[davismar]

tem ID @Item ID Base Cost @Base Cost

0890072 0890072 175.50 175.50
0890072U 0890072 0 175.50*.80 or $140.40

0891042 0891042 701.40 $701.40
0891042U 0891042 100.00 701.40*.80 or $561.12

0890043 0890043 113.40 113.40
0890043U 0890043 300.00 113.40*.80 or $90.72

0910048 0910048 116.76 116.76*.80 = 93.40
0910048U 0910048 0 116.76*.80 = 93.40

 

[lbass]

You changed my sample. Please redo the last two lines where the items are both U items in different groups, with only the one record in the group:

Item ID @Item ID Base Cost @Base Cost

0910048U 0910048 116.76

0940048U 0940048 0

-LB

 

[davismar]

Item ID @Item ID Base Cost @Base Cost

0910048U 0910048 116.76 116.76*.80 = 93.40

0940048U 0940048 0 116.76*.80 = 93.40

 

[lbass]

Why would 0940048U take on the base cost of another item group? What is the logic?

-LB

 

[davismar]

LB -

Item 0940048U in your example doesn't actually exist in our database tables.

The logic is that any "U" item, no matter what its' base cost, would take on 80% of the Non-U item.

So what do you think? Are we getting close to having a revised formula?

 

[lbass]

Do you not see that this is just an example of a logic you have to explain? All I am asking is that you clarify what happens in these THEORETICAL cases.

You have said that sometimes 'U' items have no corresponding non-'U' item. I'm assuming that in that case the 'U' item uses its own base cost.

But what if 0940048U (Imaginary and has no corresponding U item) has a value of 0? Can this happen? If it can be 0, how would you assign a value to it? What logic would you use?

-LB

 

[davismar]

I stand corrected. In the case of the imaginery item 0940048U, there should always been an item 0940048.

In that instance, the logic would be the same. The formula should
take the base cost of the non-U item 0940048 * .80.

I hope that helps clarify.

Thanks.

 

[lbass]

Okay, from the beginning. Please create the following formula in the field explorer->formula->new:

//{@ItemID}:
if right({{table.itemID},1)="U" then
left({table.itemID},len({table.itemID})-1) else
{table.itemID}

Insert a group on {@ItemID}.

Also create these new formulas

//{@NonUbasecost}:
if right({{table.itemID},1)<>"U" then
{table.basecost}

//{@UBaseCost}:
if right({{table.itemID},1)="U" then
maximum({@NonUbasecost},{@ItemID})*.8

//{@BaseCost}:
if right({{table.itemID},1)<>"U" then
{@NonUbasecost} else
{@UbaseCost}

//{@Result}:
{@BaseCost}*{table.qty}

Place the last row formulas in the detail section, along with {table.itemID} and {table.qty}.

-LB

 

[davismar]

Thanks LB - We are getting closer.

But the "result" for the U item base cost is not pulling the correct value.

The following is from the detail section with the result:

Item ID: Qty: @Base Cost @Result
0890043U 2 $0.00 $0.00

This should appear as follows:

Item ID: Qty: @Base Cost @Result
0890043U 2 $90.72 $181.44

Note: The Non-U item for the above which is 0890043 has a base cost in the table of $113.40. Therefore, 80% of $113.40 should reflect a base cost of $90.72 for the Non-U item.

Thanks!

 

[lbass]

You are implementing this incorrectly. Please try again.

-LB

 

[davismar]

When you state: Place the last row formulas in the detail section, along with {table.itemID} and {table.qty}, what last row formulas are you referring to?

 

[lbass]

Sorry, I meant "Last Two".

-LB

 

[davismar]

After modifying what the instructions were above, the data is still incorrect.

The base cost is showing $0.00 and so is the result value.

Do you have any other suggestions as to what might be wrong?

 

[lbass]

Sorry, but I am unable to provide any additional assistance.

-LB