Using Date based on criteria

[Labone]

Hi,

Could anyone help? What i have is a db with the following fields
Part_Num
Date
Cost_set
in the part field there can be multiple entries for the same part all relating to the cost set ( cost set 1, 2, 3 etc ) and the date field has the date of when the particular cost set was added, when i create a report for the above ( i have added a few formulas to only show the entries where cost_set1 and cost_set5 are different )
but the date it gives is for cost_set5 i would like it to show cost_set1 does anyone have any ideas how to do this?

Sorry if the explanation is not clear....

Thanks
Mick.

 

[Madawc]

You need a formula field that selects one date or the other, based on your criteria. If XXX then cost_set1 else cost_set5.

You can also group using such formula fields, provided that they make their calculation based on just a single record.

Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[ClowneUser]

Hi Madawc,

Thanks for the explaination, just one thing how do i get it to look at the cost set number? what i tried was
if {PART_COST.DATE} then {PART_COST.COST_SET}1 else {PART_COST\.COST_SET}5

where can i put the cost_set values? do i need to enclose {PART_COST.COST_SET}1 with brackets?

Cheers

Mick.

 

[Madawc]

Do you mean these are values from different records? Sorry, I misunderstood.

You can't directly reference data from more than one record at the same time, except using the Next and Previous commands for adjacent records.

Waht you need to do is accumulate each in a running total. In the group footer, or wherever, compair the two running totals and display the one you want.

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.


Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[ClowneUser]

Madawg, To be honest i don't know if explained it right at the beginning, I'll have another go, Sorry im new to Crystal so just learning the basics....
I have a table with the following fields ( and an example of data)
Part_Num
a-123
b-123
Date
10/10/2005
20/08/2005
Cost_set
1
5
Cost
0.0040
1.4560

Each Part_num can have multiple cost sets and both are more than likely put on at different dates
The purpose of this report was to check all part numbers with cost sets 1 and 5 and only show the results that have different costs, this is working fine, but the results i get back when i run the report for the Date relate to cost set 5( or at least thats what it seems, it could also be the latest date?) what i need is for it to show cost set 1's date, so i think the first reply you gave was about right i just need to know what i need to use in the formula to get it to do this.
I hope this makes a bit more sense... I should try to explian myself a little better i think.

Thanks in advance
Mick.

 

[Madawc]

Are you selecting for a date?

You can do complex selections: (date1=x and set=1) or (date1=y and set=5)

Does that help?

Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[ClowneUser]

Hi, not sure what you mean by selecting a date?
Could you elaborate on your example? What would i need to use for it to work?
As i said im prety new to crystal so help would be appreciated.

Mick.

 

[Madawc]

This is the Report > Selection Formulas > Records. If you're new to Crystal, you've probably been using the Select Expert which is fairly inflexible.

Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[lbass]

Try a record selection formula like:

{table.cost_set} in [1,5]

Insert a group on part number. Then go to report->selection formula->GROUP and enter:

minimum({table.cost},{table.partno}) <>
maximum({table.cost},{table.partno})

This should return only those part numbers where there the costs are different for cost sets 1 and 5.

-LB