Forum Discussion

MorganHall's avatar
MorganHall
Qrew Member
2 months ago

If this user and this, then this. If not this user and this, then that

Hi, so I have a complicated formula for a field I am using for calculating commissions based on whether it's a specific user. I know I can create a commissions structure table to make this easier, however the complication comes from a legacy employee we have. Everyone in who is a superintendent in our company has the same commission structure except one person who has a legacy to a previous structure. That is why there is no need to create the table. Every superintendent from now on and always will have this commission structure except the one guy. I want my formula to base the structure off of whether or not he is the user in a specific field.

Normal Commission Structure:

if GM%<=0.4299 you earn $0

if GM%>0.4299 you earn $100

Legacy User: Mike Andrus

His commission structure is this:

if GM%<=0.4299 you earn $0

if GM%>0.4299 & <0.4499 you earn 1% of the total invoiced dollars

if GM%>0.4499 & <0.4999 you earn 2% of the total invoiced dollars

if GM%>0.4999 you earn 3% of the total invoiced dollars

I have the user field as a text field, and I get no errors from the formula, however, every time I try to use the field when he is the user it returns $100 instead of the correct amount.

This is what I currently have:
If([Customer - Superintendent 1 - Name]="Mike Andrus" & (ToText(ToNumber([GM%]>0.4299) & (ToText(ToNumber([GM%]<=0.4499))))),[Total Invoiced]*0.01,
([Customer - Superintendent 1 - Name]="Mike Andrus" & (ToText(ToNumber([GM%]>0.4499) & (ToText(ToNumber([GM%]<=0.4999)))))),[Total Invoiced]*0.02,
([Customer - Superintendent 1 - Name]="Mike Andrus" & (ToText(ToNumber([GM%]>0.4999)))),[Total Invoiced]*0.03,
([Customer - Superintendent 1 - Name]="Mike Andrus" & (ToText(ToNumber([GM%]>0.4299)))),0,
([Customer - Superintendent 1 - Name]<>"Mike Andrus" & (ToText(ToNumber([GM%]>0.4299)))),100,
([Customer - Superintendent 1 - Name]<>"Mike Andrus" & (ToText(ToNumber([GM%]>0.4299)))),0)

What in my formula is causing this issue? All of the (ToText(ToNumber( functions are there because I was getting errors without them. Is this somehow the issue?



------------------------------
Morgan Hall
------------------------------

2 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    2 months ago

    I didn't do an exhaustive rewrite but you're using & instead of 'and' as your conjunction which is probably the main reason it's not dong parts of your formula. & is used for string concatenation where as 'and', 'or' is for logic. So take your first line for example, it should look more like this: 

    [Customer - Superintendent 1 - Name]="Mike Andrus"  and [GM%]>0.4299 and [GM%]<=0.4499,[Total Invoiced]*0.01,

    Using & is why it kept failing and forcing you to make it text, the above is an example of using and as the operator that you should switch all your conditions to then retest. 



    ------------------------------
    Chayce Duncan
    ------------------------------

    • MorganHall's avatar
      MorganHall
      Qrew Member
      2 months ago

      That was it! Thank you so much! I knew it had to be one small thing messing me up.



      ------------------------------
      Morgan Hall
      ------------------------------