CurtisMiddleton
Qrew Trainee
5 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
------------------------------
• 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
------------------------------

4 Replies

• 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
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------
• DonLarson
Qrew Commander
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

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
------------------------------
• 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
Qrew Assistant Captain
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
------------------------------