Discussions

Expand all | Collapse all

How do I return one result from three fields?

  • 1.  How do I return one result from three fields?

    Posted 12-20-2017 18:11
    I have 3 broker fields per order and need to figure out a way to return a total of commissions per broker when that broker's name is in any of the 3 fields. I am writing a report and this is the formula I was attempting to use in the calculated column. (I also filtered my report to show records only where this calculated column was greater than zero.)

    If(
    [Broker 1 Name]="ABC"
    or
    [Broker 2 Name]="ABC"
    or 
    [Broker 3 Name]="ABC",

    [Commission 1] + [Commission 2] + [Commission 3], 0)

    This worked except that if there was more than one broker (who we will call "XYZ") but one of the names was the "ABC" that I was searching for, it still added the commission for XYZ into the formula. (understandably)

    How do I tell it to only calculate Commissions 1, 2, or 3, when the broker is ABC? I tried to add in additional IF formulas:

    If([Broker 1 Name]= ?ABC?,[Commission 1],0)

    +

    If([Broker 2 Name]= ?ABC?,[Commission 2],0)

    +

    If([Broker 3 Name]= ?ABC?,[Commission 3],0)

    ,0)

    but that did not work either. I'd like to have all of this info on one report rather than having to run a separate report for each broker field. I am open to suggestions!

    Thank you!


  • 2.  RE: How do I return one result from three fields?

    Bronze
    Contributor
    Posted 12-20-2017 18:22
    Try a case statement

    Case(true,
    [Broker 1 Name]="ABC",[Commission 1],
    [Broker 2 Name]="ABC",[Commission 2],
    [Broker 3 Name]="ABC",[Commission 3],
    "")


  • 3.  RE: How do I return one result from three fields?

    Posted 12-20-2017 18:31
    Thank you! It is still returning the same formula error. "Formula syntax error - Expected a valid expression after the "=" and it specifies the "=" after [Broker 1 Name]. I double checked all parentheses etc.


  • 4.  RE: How do I return one result from three fields?

    Bronze
    Contributor
    Posted 12-20-2017 18:58
    It should be a formula text field. Then pull that into the report instead of calculated column just in case. Here is the reference to the formula.

    https://login.quickbase.com/db/bcgahn76w?a=dr&rid=19&rl=bds">https://login.quickbase.com/db/bcgahn76w?a=dr&rid=19&rl=bds">https://login.quickbase.com/db/bcgahn76w?a=dr&rid=19&rl=bds


  • 5.  RE: How do I return one result from three fields?

    Posted 12-20-2017 19:11
    Ok, thank you! Trying this now.


  • 6.  RE: How do I return one result from three fields?

    Posted 12-20-2017 19:12
    Chuck,
    Doesn�t this need to be a formula numeric field type? And Jensen the �else� part of the Case needs to be else 0.


  • 7.  RE: How do I return one result from three fields?

    Bronze
    Contributor
    Posted 12-20-2017 19:23
    You're right it should be numeric.


  • 8.  RE: How do I return one result from three fields?

    Posted 12-20-2017 19:34
    Thank you both! This worked as a numeric formula.