Skip to Content
avatar image
Former Member

Effective use of counters in a formula.

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Aug 02, 2017 at 02:04 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 02, 2017 at 08:12 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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#