Discussions

Expand all | Collapse all

Formula Not Functioning as Expected

Jump to Best Answer
  • 1.  Formula Not Functioning as Expected

    Posted 19 days ago
    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
    ------------------------------


  • 2.  RE: Formula Not Functioning as Expected

    Posted 19 days 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
    ------------------------------



  • 3.  RE: Formula Not Functioning as Expected

    Posted 18 days 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
    ------------------------------



  • 4.  RE: Formula Not Functioning as Expected
    Best Answer

    Posted 19 days 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
    ------------------------------



  • 5.  RE: Formula Not Functioning as Expected

    Posted 18 days 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
    ------------------------------