Forum Discussion

CurtisMiddleton's avatar
CurtisMiddleton
Qrew Trainee
6 years ago
Solved

Formula Not Functioning as Expected

Hi,

Can someone please help me with the following formula. I have comments under each line as to what it should be doing, but it is not doing that. It is just displaying zero.

If([Spent By]=ToUser("John Doe") and [Shared Purchase?]=true, [Amount]/2,
//If user is John Doe and Checkbox is checked, display the amount divided by two, otherwise execute next if statement.

If([Spent By]!=ToUser("John Doe") and [Shared Purchase?]=true, [Amount]/2,
//If user is not John Doe and Checkbox is checked, display the amount divided by two, otherwise execute next if statement.

If([Spent By]=ToUser("John Doe") and [Shared Purchase?]=false, [Amount], 0)))
//If user is John Doe and Checkbox is NOT checked, display the amount, otherwise display 0.

I also tried adding a couple extra sets of parentheses to break it up more, but that didn't work either.

------------------------------
Curtis Middleton
------------------------------
Formulas and functions
  • EverettPatterso's avatar
    EverettPatterso
    6 years ago
    Try this one

    If(UserToName([Spent By], "FF")="John Doe" and [Shared Purchase?]=true, [Amount]/2,
//If user is John Doe and Checkbox is checked, display the amount divided by two, otherwise execute next if statement.

UserToName([Spent By], "FF")!="John Doe" and [Shared Purchase?]=true, [Amount]/2,
//If user is not John Doe and Checkbox is checked, display the amount divided by two, otherwise execute next if statement.

UserToName([Spent By], "FF")!="John Doe" and [Shared Purchase?]=false, [Amount], 0)
//If user is John Doe and Checkbox is NOT checked, display the amount, otherwise display 0.​


    ------------------------------
    Everett Patterson
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    If you actually are using a person's name here

    Spent By]=ToUser("John Doe")

    Then there is no way for that to work, as you would need to put in email address to uniquely identify the user.  Lots of people have the same name.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • DonLarson's avatar
      DonLarson
      Qrew Elite
      6 years ago
      Curtis,

      Hard coding a formula to a specific user means you need the email address that they have associated with their Quick Base log in.  You can get it from the Users list at the beginning of the tables.

      However this makes for a difficult code base to support in the future.  Consider using the new UserRole function

      https://login.quickbase.com/db/6ewwzuuj?a=dr&r=gj&rl=y

      If John Doe is the Controller, Regional Manager or some other appropriate role, then when he leaves, a new person will automatically have access to the records for that role and you will not have to remember where to change the code.

      ------------------------------
      Don Larson
      Paasporter
      Westlake OH
      ------------------------------
  • EverettPatterso's avatar
    EverettPatterso
    Qrew Captain
    6 years ago
    Try this one

    If(UserToName([Spent By], "FF")="John Doe" and [Shared Purchase?]=true, [Amount]/2,
//If user is John Doe and Checkbox is checked, display the amount divided by two, otherwise execute next if statement.

UserToName([Spent By], "FF")!="John Doe" and [Shared Purchase?]=true, [Amount]/2,
//If user is not John Doe and Checkbox is checked, display the amount divided by two, otherwise execute next if statement.

UserToName([Spent By], "FF")!="John Doe" and [Shared Purchase?]=false, [Amount], 0)
//If user is John Doe and Checkbox is NOT checked, display the amount, otherwise display 0.​


    ------------------------------
    Everett Patterson
    ------------------------------
    • BabiPanjikar's avatar
      BabiPanjikar
      Qrew Assistant Captain
      6 years ago
      The solution provided by Everett will work perfectly for your case. However,  basing a check on username is not the right approach as two people can have same names and will comprise your data between two users.  You should be using the below options.

      ToText(UserToEmail([Spent By])) ="email address of the person"

      OR
      [Spent By] = User()  -- Match the current logged user with the field value in spent by field to control the rights.



      ------------------------------
      Babi Panjikar
      ------------------------------