Print one record based on another

[MikeN_(Guest)]

Hi Kathleen! I know you know Mail Order Manager to some extent and hopefully you can help me...____The PRICE table contains special pricing information, one record for each price level that a product may have. There are start and end date fields in the table for temporary pricing. What I^m trying to do is figure out how to tell R&R to print the sale price record if today is within that date window, otherwise print the standard price record.____Conditionally printing the sale price record based on the date is easy enough, but how do I NOT print the regular record if it prints the sale record??____Thanks!!____MikeN

[kfleming]

You could create a logical calculated field that returns a true when the date is in the price range.__You could have record two band lines and use this calc field for each. One would be a print when true and the other a print when false. ____Kathleen__R&R Support

[MikeN_(Guest)]

Like I said, printing the "sale" record when the date is valid is easy enough, I just don^t get how to NOT print the regular record. Let me give you an example:____ITEM START END PRICE__---- ----- --- -----__WidgetA $10__WidgetA 8/1/08 8/5/08 $8______The record without a date range is the "regular" price, but I want it to *not* print that record from 8/1 - 8/5. Instead, I want it print the "sale" record price of $8. Then, on 8/6, go back to printing the $10 price.____See my dilemma?____Thanks____MikeN

[MikeN_(Guest)]

Of course it stripped the spaces out of my table when it posted, try this:____ITEM......START.....END......PRICE____WidgetA......................$10__WidgetA...8/1/08....8/5/08...$8____I hope my example table makes sense... :(____Miken__

[kfleming]

I am sticking with my original idea.____You create a logical calculated field that returns a true when the date is in the price range.____You then have record two band lines the first with the regular price and the second with the sale price. Then you select each line and in line properties select this calculated field asthe field to control printing. It is the same calc field for each line but the first is set to print when true and the second is set to print when false. ____Kathleen__R&R Support________Kathleen__R&R Support

[MikeN_(Guest)]

I see what you^re saying but it doesn^t work because it^s not an "either/or" situation. It^s a "choose one of many" situation. Let me expand on the table...____item, start, end, price__-----------------------__WidgetA,,,$10__WidgetA, 10/1/08, 10/10/08, $9__WidgetA, 10/15/08, 10/20/08, $8__WidgetA, 10/25/08, 10/30/08, $7____...so the record without a date is the regular price and there are 3 other records with dates.____Today is 10/13/08, so I want it to print ONLY the regular price. Then, from 10/15 through 10/20, I want it print ONLY the $8 sale price... and so on.

[kfleming]

Try this.____Use an item group footer.__Create two total fields. __REGPRICE will be a sum of price that resets by item. It will have the condition:__isblank(date_start)____SALEPRICE will also be a sum of price that resets by item. It^s condition will be:__inrange(date(),date_start,date_end)____Then create a calculated field called TODAYSPRICE with the expression:__IIF(SALEPRICE<>0,SALEPRICE,REGPRICE)____and place this on the item footer. It will show you today^s price.____Kathleen__R&R Support

[MikeN_(Guest)]

Brilliant, as usual.____Thanks again!____MikeN