Formatting a Formula - User field for a report

  • 1
  • 1
  • Question
  • Updated 8 months ago
  • Answered
Newbie here. I have created a User - Formula field called "Who" within my "Activities" table which is for reporting purposes only, where I want to display the User who created the Activity. We imported data from Salesforce, so I need a formula to handle the old data in unison with the new. I want this "Who" field to first look at the [Created By ID] field (data from Salesforce) and use that value if it's there. However, if that field is empty, I want the value that's in [Assign To] to be used. Here's what I came up with, but I'm getting the message "Formula syntax error. The argument list of the function Boolean must begin with a left parenthesis." even though I have left parenthesis. HERE IS THE FORMULATE I ENTERED: If(Boolean [Created By Id], NULL, ..., else-result)[Assign To]
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb

Posted 8 months ago

  • 1
  • 1
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb
If(not IsNull([Created By Id]), [Assign to],[Created By Id])

--- or ----

If([Created By Id] = null, [Assign to],[Created By Id])
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
Thank you! I tried both of these, but got the same message for both:

"Formula error -- Bad or missing arguments in function call

The types of the arguments or the number of arguments supplied do not meet the requirements of the function If.

The function is defined to be If (Boolean condition1, result1, ..., else-result).

You may need to do one of the following to fix the problem:
  • Choose the right number and type of arguments.
  • Use a type conversion function to convert the arguments you are using to the correct type.
  • Choose a different function."
(I'm using Formula - User, so not sure why this wouldn't work!)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
What type of field is [Assign to]?  If it is a text field then you can't mix that with a user field type as a result of an IF
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
It's a "List - User" (Multiple choice)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Hmmmm, so then this field type for the formula will need to be a formula list user field type. (not sure what it is now, but if it is  a formula user field type, I do not think it will allow you to change it to a formula list user type, so you will probably have to make a new field.

If(not IsNull([Created By Id]),ToUserList([Created By Id]),  [Assign to])

The original formula, I think, was backwards.
 
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
Thanks, again. Yes, it's set as a Formula - User type, so was go there. 

I tried the last formula you supplied and now get this:

"Formula error -- Bad or missing arguments in function call

The types of the arguments or the number of arguments supplied do not meet the requirements of the function ToUserList.

The function ToUserList can be used with the following arguments:
  • ToUserList (User u ..).
  • ToUserList (UserList ul ..). 
You may need to do one of the following to fix the problem:
  • Choose the right number and type of arguments.
  • Use a type conversion function to convert the arguments you are using to the correct type.
  • Choose a different function."
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
This is indeed confusing.  What type of field is [created by]. Maybe it is just an email field type?
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
It is "Text - Multiple Choice"
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
OK, let's start over here.

So we need the result to be a text field, not a user field or a list user field type.



If(Trim([Created By Id])<>"",[Created By Id],  ToText([Assign to]))
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
That works. :) Thank you!!! I've learned quite a bit about formulas.

Question: when it returns the [Assign To] in the table, it's that person's email address. Is there a way to have it return the person's Name (first and last name), i.e., NOT User Name?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Yep, indeed there is a function for that

If(Trim([Created By Id])<>"",[Created By Id], 
     UserListToNames([Assign to]))

The full list of functions is here.
https://login.quickbase.com/db/6ewwzuuj?a=q&qid=1000080

and yes working with UserList fields is confusing at first.
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
Fantastic! That works, too. :) And, thanks much for my intro to formulas....
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
I want to use this same formula for a report from our "Cases" table, where I want to display the Case Number for a Case, which might be a Case Number imported from our Salesforce records or else a new Quick Base Case Number. I want the field "Case #" (a Formula - Numeric field) to first look at the [SF Case Number] (a Numeric field) and use that value if it's there. However, if that field is empty, I want the value that's in [QB Case Number] (a Formula - Numeric field) to be used.

So, this is what the new formula looks like:

If(Trim([SF Case Number])<>"",[SF Case Number], ToText([QB Case Number]))

However, this is the error I received:

"The types of the arguments or the number of arguments supplied do not meet the requirements of the function Trim.

The function is defined to be Trim (Text t).

You may need to do one of the following to fix the problem:
  • Choose the right number and type of arguments.
  • Use a type conversion function to convert the arguments you are using to the correct type.
  • Choose a different function."
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Try this

If([SF Case Number] > 0, [SF Case Number], [QB Case Number])
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
Yep, that one works! Thank you...