Discussions

Expand all | Collapse all

Formatting a Formula - User field for a report

Donna Troestler03-28-2018 21:39

Donna Troestler03-28-2018 22:07

Donna Troestler03-30-2018 15:54

  • 1.  Formatting a Formula - User field for a report

    Posted 03-28-2018 20:46
    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]


  • 2.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 20:55
    If(not IsNull([Created By Id]), [Assign to],[Created By Id])

    --- or ----

    If([Created By Id] = null, [Assign to],[Created By Id])


  • 3.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 21:18
    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!)


  • 4.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 21:32
    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


  • 5.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 21:39
    It's a "List - User" (Multiple choice)


  • 6.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 21:46
    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.
     


  • 7.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 21:52
    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."


  • 8.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 22:02
    This is indeed confusing.  What type of field is [created by]. Maybe it is just an email field type?


  • 9.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 22:07
    It is "Text - Multiple Choice


  • 10.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 22:17
    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]))


  • 11.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 22:25
    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?


  • 12.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 22:35
    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.


  • 13.  RE: Formatting a Formula - User field for a report

    Posted 03-28-2018 22:38
    Fantastic! That works, too. :) And, thanks much for my intro to formulas....


  • 14.  RE: Formatting a Formula - User field for a report

    Posted 03-29-2018 16:27
    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."


  • 15.  RE: Formatting a Formula - User field for a report

    Posted 03-29-2018 22:38
    Try this

    If([SF Case Number] > 0, [SF Case Number], [QB Case Number])


  • 16.  RE: Formatting a Formula - User field for a report

    Posted 03-30-2018 15:54
    Yep, that one works! Thank you...