I have had trouble with counters in tallying various counts. Where I work there is basic business logic that an sales order, contains many codes. This system uses codes to describe all parts of what went into this order, from phone or web chat to shipping it. there is one table that holds orders and codes and sales persons.
Case. You have a table with sales persons and various codes for one order. these codes represent tasks which is part of the order. If the sales person has code 001, and BCS and WOC, we only want to tally the WOC.
If has BCS & 001 then would tally the 001. the logic is if there are only 2 codes (always an order will have an 001)
then tally the 001.
How would you do this in a formula?
Group data by Salesperson.
Create three formula
@WOC
If Transcode = 'WOC' then 1 else 0
@Other
If Transcode = '001' then 1 else 0
place this formula In salesperson group footer, you do not need to place the others on to the report layout.
@SPTotal
If Sum(@WOC, salesperson) >0 then Sum(@WOC, salesperson) else Sum(@001, salesperson)
Not as difficult as I first thought. If you want to then sum these as a report total you will need to use a variable.
Ian
Depends on your data structure
Are the codes 001, and BCS and WOC in a single field and thus there are multiple records per order, or is there a single record with multiple Code fields.
I suspect the former, in which case it would be easier to do this in a command, can you write SQL?
If not then you will have to create formula based on group counts, please show a sample of your data structure
Ian
The structure is like this.
order# Salesperson TransCode
12345 101 001
12345 101 BCS
12345 101 WOC
12345 99 CQU
Suppose for example we only want to count the BCS if there is NO WOC per that order#