Can a formula text field return only some data?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I am trying to add a formula field that combines multiple fields. I have that formula all set, the problem is that I need this formula field to return blank if neither field is filled in, or to only show part of the information if only half of the info is filled in. 

The back story is that some of our part numbers have multiple pieces. So if a part is multi-piece the user is required to enter the part and a serial number of each of the other pieces. This is done using 4 additional fields. I have attached an image to better show what I mean. 


So if a user has a multi-part item they check the "Multi-Part Item" checkbox, which shows the "#2 Additional Part #", "#2 Additional SN", #2 Description", "#3 Additional Part #", "#3 Additional SN", #3 Description". The User then enters the data in these new fields. However they may only enter one additional part number, and may not include an additional serial number at all.  

So the data in these fields feeds into the formula text field "Additional Part Numbers Ref". (This field is used in a PDF version of the form). As you can see in this image the user did not enter a 3rd part number, but the formula field still shows the SN option. 

So my question is what formula can I use to leave this field blank when no info is entered in the 2nd and 3rd part number boxes, as well as only return a value if only the 2nd part number info is filled out. If possible I would also like it to not show the text portion "SN: " if the #2 and #3 SN boxes are blank. Here is the formula I have in the field as of now that returns what you see in the image. 

[#2 Additional Part #]&" SN: "&[#2 Additional SN]&" "&[#2 Description]&", "&[#3 Additional Part #]&" SN: "&[#3 Additional SN]&" "&[#3 Description]

Thank you!

Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,670 Points 50k badge 2x thumb
"List" function to the rescue.

The List function is most useful as it allowed you to separate text values in a list by an separator you like, and the function is smart enough not to include the separator of there is nothing to separate. your situation is a bit messy as you have the "SN: in the middle of the string.

Try this


List(" ", 

if(trim([#2 Additional Part #])<>"", [#2 Additional Part #] & " SN: " & [#2 Additional SN]&" "&[#2 Description]),

if(trim([#3 Additional Part #])<>"", [#3 Additional Part #] & " SN: " & [#3 Additional SN]&" "&[#3 Description]))
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
Thank you so much! This worked perfectly!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,670 Points 50k badge 2x thumb
:)