Compare records

[Smzemke_(Guest)]

I need to setup an expression that will recognize when 2 records have the same piece of information - but I am not specifying what that informaiton should be - the records basically need to be compared to themselves, if that makes sense.____Here^s an example:____I^m creating a report that is printing out the sales margin % for orders that were below our pre-defined acceptable range of margins. A single order may contain multiple items. Right now, the report is printing only individual items that do not fall within the acceptable range. However, we^d now like the report to do the following: ____Say there are 8 line items on a single order. Of those 8 items, 5 of them have similar product Id^s (the first 8 characters are the same). The other 3 have completely different product id^s. Of the 5 that are similar, if one of those line items should fall outside the acceptable margin range, we want all 5 records to print out - even if the other 4 are within acceptable ranges.____Is there any way we can have RR look at the product Id^s and identify when the first X number of characters are the same? Without having to give it something to compare to?____Thanks__Sherri

[kfleming]

You could create a calculated field that takes the first 8 characters and then group by that field and then use a pre-processed total to determine whether to print the members of the group.__So you could do a calc that says IIF(flagcondition,1,0), sum this calculation and reset for the first 8 chars group. Make this total preprocessed and then set a condition on the record band to only print when this total is greater than zero.____If this does not make sense, send me an email and I will send you a sample report that illustrates the method.____Kathleen__R&R Support__livesupport@livewarepub.com

[Smzemke_(Guest)]

Ok - I created a calculated field that looks at the first 8 characters of the product_id (lets call this calcualation "First8". But, when I group by First8, then, items that the same first 8 characters are combined - when they need to stay seperate____example: __ __Inovoice# Product Id Footage Margin__41000 04fanaro-10 100 Ft 3% (not acceptable)__41000 04fanaro-12 500 Ft 20% (acceptable)__41600 04fanaro-14 600 Ft 20% (acceptable)__41200 05sbpnro-10 300 Ft 5% (not acceptable)__41300 06sbpnro-14 1000 Ft 18% (acceptable)______The report should print out: Items that fall within the non-acceptable range. If there are more than one items on the same invoice, where the first 8 characters of the product id are the same, and only one of those items has a margin % below the acceptable range, then all of the items with the same first 8 char. (on that invoice should print out.____Here^s what should print out on the report, based on the above information:____Invoice# Product ID Ftg Margin%__41000 04fanaro-10 100 Ft 3% (not acceptable)__41000 04fanaro-12 500 Ft 20% (acceptable)____41200 05sbpnro-10 300 Ft 5% (not acceptable)______If I were to group the items by First8, then the report would combine the two line items that contain the first 8 characters of: "04fanaro" These would end up falling into the acceptable range, and they would not print out on the report -- when infact they should.____Any thoughts on how to achieve the report example above - without grouping by First8 ?____Is there anyway to have each line item compared to see if they have the same First8 as another line item?____Thanks,__Sherri

[kfleming]

Are you printing the information on a record band line or on a group footer? A record band will show each individual item.____Kathleen__R&R Support

[Smzemke_(Guest)]

The information is printing in a group footer. This is because there are individual (tag) items that make up a particular product line item.____i.e. ____Invoice product:____Product_id Footage__ 04fanaro-12 300 Ft____This could come from three different tags:____Tag# Product _id Footage__41236 04fanaro-12 150__41237 04fanaro-12 40__41237 04fanaro-12 110____So... if we printed the information in the record band, then we^d get every individual tag printing out... which isnt^ what we want.____Does this make sense?____Thanks,__Sherri

[kfleming]

Then you need a total field that flags those invoices that are out of range and then add a logical condition based upon the total.____Kathleen__R&R Support

[Smzemke_(Guest)]

Ok - I believe I have the total field taken care of (that flags the out of range invoice) ... But I^m confused on how to setup the logical field.... ____Here^s the logic I need:____If there are more than 1 line items with the same First8, and only 1 of those line items fall outside of the acceptable range, then all of the line items (with the same First8) on that invoice should print out.____??____Thanks__Sherri____

[Smzemke_(Guest)]

Does R&R have any consulting services that can help me out with this?____Thanks

[kfleming]

Yes we do.____Give us a call at 800-936-6202____Kathleen__R&R Support