| Author |
Message |
chloesnowling
Newbie
Joined: 29 Jan 2009
Location: United Kingdom
Online Status: Offline
Posts: 7
|
  
Topic: Record Selection Formula
Posted: 05 Dec 2010 at 10:20pm |
Hi Experts
I'm stuck on a formula and any help would be very much appreciated!
I have an orderheader table which contains all orderlines for 2010 (50000+ rows).
I have grouped the data by order number, and each group contains from 1 to 50 orderlines (products) which the customer has ordered on that order.
I need a formula to display all orders where a customer has ordered both products A,B,C,D,E,F,G,H,I,J,K,L,M together.
I attempted a record select, using products equal to A,B,C,D,E,F,G,H,I,J,K,L,M but this will display orders where customers ordered just one of the above.
The fields I have are:
ordernumber
sku
qty
Thank You.
|
|
Thanks, Chloe.
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Dec 2010 at 3:46am |
If you want a crystal only solution you will need to use group record selection criteria.
I am unclaear on your criteria as you say order 'both' but list out 13 different letters. Exactly what is your criteria?
|
| IP Logged |
|
chloesnowling
Newbie
Joined: 29 Jan 2009
Location: United Kingdom
Online Status: Offline
Posts: 7
|
Posted: 06 Dec 2010 at 3:57am |
Sorry - let me try and make it a little clearer.
I would want it to display all orders where parts....
A and B were on the same order
or
A,B,E were on the same order
or
F,A,B were on the same order.
Etc.
Basiclly, every order where any of these parts were ordered TOGETHER.
I would not want it to display orders where they ordered on there own.
Hope this makes is clearer. Thank You.
|
|
Thanks, Chloe.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Dec 2010 at 4:00am |
No problem, just need to make sure I understand.
Can you ever have more than 1 row of any of the same types in the same order number (e.g. A and a second A inj order #1)? Edited by DBlank - 06 Dec 2010 at 4:08am
|
| IP Logged |
|
chloesnowling
Newbie
Joined: 29 Jan 2009
Location: United Kingdom
Online Status: Offline
Posts: 7
|
Posted: 06 Dec 2010 at 4:17am |
Ah thinking about that it gets a little tricker...
If I explain the reasons behind it, it may be clearer.
I am attempting to montior how many orders for beds are being sold with accesories. So for example.
A, B, C, D are beds
E, F, G, H, I, J, K, L, M are the accessories
So, I wouldnt want it to display orders where there are two beds ordered together with no accessories on the order. Or more than one accessory and no bed!
Hope this doesnt make it impossible!!
Thank you for your help.
|
|
Thanks, Chloe.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Dec 2010 at 4:26am |
makes it a bit easier.
you will have to clean these up as I do not know your field names or data types but:
create one formula to 'flag' an ID if any bed is ordered
@BedFlag
if field in A, B, C, D then 1
create a second formula to flag if an accessory was sold
@AccFlag
if field in E, F, G, H, I, J, K, L, M then 1
Use the insert summary on each of these as as a SUM at the grouplevel of Order Number.
Go into the record select expert
expand it and flip it to use "group selection"
add your criteria here
SUM(@BedFlag,table.OrderNumber)>0
and
SUM(@AccFlag,table.OrderNumber)>0
Note that group record selections are applied AFTER the report is built so these omitted groups will still appear in the group tree and will still be in your counts. To get a count you will have to do a running total that uses similar select criteria (if needed). Edited by DBlank - 06 Dec 2010 at 4:27am
|
| IP Logged |
|
chloesnowling
Newbie
Joined: 29 Jan 2009
Location: United Kingdom
Online Status: Offline
Posts: 7
|
Posted: 06 Dec 2010 at 4:49am |
Thats excellent.
On the @BedFlag
Rather than me keying in all of the bed codes, they all start with BED- so I attempted to put a % at end of formula, but it didnt work. Do you know where I am going wrong?
If I put this it works -
if {OrderItemHeader.Sku} = 'BED-001' THEN 1
but this it doesnt -
if {OrderItemHeader.Sku} = 'BED-001%' THEN 1
Thanks
|
|
Thanks, Chloe.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Dec 2010 at 4:57am |
crystal uses * where SQL uses %
also change it to LIKE rather than =
if {OrderItemHeader.Sku} LIKE 'BED-001*' THEN 1
|
| IP Logged |
|
chloesnowling
Newbie
Joined: 29 Jan 2009
Location: United Kingdom
Online Status: Offline
Posts: 7
|
Posted: 06 Dec 2010 at 5:14am |
Fantastic - I'm there!! =]
Now just for the count - As you said, the normal count displays the wrong count of orders. When using the running total, what do I put in the evaluate section?
Thanks
|
|
Thanks, Chloe.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Dec 2010 at 5:19am |
you want a count of orders correct?
Name=whatever
Field to summarize=ORDERNUMBER
type=DistinctCOunt
Evaluate= use a formula (same as your group select formula)
SUM(@BedFlag,table.OrderNumber)>0
and
SUM(@AccFlag,table.OrderNumber)>0
Reset=Never
Place in report Footer (RTs show the values like whileprintingrecords so they do not work in headers)
|
| IP Logged |
|
|
|
