Skip to main content
Solved

Combining different data fields into one column

  • September 13, 2023
  • 9 replies
  • 41 views
B
Forum|alt.badge.img+4
  • bartdubs
  • Participating Frequently
  • 6 replies

Hi there,

I want to have a column which displays two bits of data. Those are a cumulative $ amount and below that a cumulative % amount. I have these two lookup fields separately however need to combine them into one field column. Any help would be greatly appreciated,

Bart

Best answer by Sho

for example like this

IF( {Rollup profit ($)}, "$" & {Rollup profit ($)} ) & IF( AND({Rollup profit ($)},{Rollup profit (%)}), ", " ) & IF( {Rollup profit (%)}, INT({Rollup profit (%)}*100) & "%" )

9 replies

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • 560 replies
  • September 14, 2023

Hi @bartdubs,

Two fields can be displayed as one by using the "&" operator symbol in the Formula field.

Name & " - " & Age => Jane - 28 "\\""&{Product Name}&"\\"" => "GreatProduct" Name & " \n" & Age => Jane 28

Formula Field Reference - Overview | Airtable Support

B
Forum|alt.badge.img+4
  • bartdubs
  • Author
  • Participating Frequently
  • 6 replies
  • September 14, 2023

Hi @bartdubs,

Two fields can be displayed as one by using the "&" operator symbol in the Formula field.

Name & " - " & Age => Jane - 28 "\\""&{Product Name}&"\\"" => "GreatProduct" Name & " \n" & Age => Jane 28

Formula Field Reference - Overview | Airtable Support


Hi @Sho 

Thanks that makes sense. However, when I do that it inserts both values as basic numeric values. How would I be able to do it where it keeps the percentage as a percentage and the $ value as a $ value?

Thanks,

Bart

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • 560 replies
  • September 14, 2023

If you want to display percentages or amounts as strings in the Formula field, you must also write such formatting yourself.

"$" & {Value} INT({Value}*100) & "%"

 

B
Forum|alt.badge.img+4
  • bartdubs
  • Author
  • Participating Frequently
  • 6 replies
  • September 14, 2023

@Sho When using the formula

 

"$" & {Rollup profit ($)}, INT({Rollup profit (%)}*100) & "%"
 
It is just saying invalid formula. Is there any specific reason this might be?
(The fields labeled as rollup are both lookup fields)
S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • 560 replies
  • September 14, 2023

A little more!
"," is also a string.

"$" & {Rollup profit ($)} & ", " & INT({Rollup profit (%)}*100) & "%"

 

B
Forum|alt.badge.img+4
  • bartdubs
  • Author
  • Participating Frequently
  • 6 replies
  • September 14, 2023

A little more!
"," is also a string.

"$" & {Rollup profit ($)} & ", " & INT({Rollup profit (%)}*100) & "%"

 


That's a lot better thank you very much

Last bit.....Is there a way to remove the excess symbols?

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • 560 replies
  • Answer
  • September 14, 2023

for example like this

IF( {Rollup profit ($)}, "$" & {Rollup profit ($)} ) & IF( AND({Rollup profit ($)},{Rollup profit (%)}), ", " ) & IF( {Rollup profit (%)}, INT({Rollup profit (%)}*100) & "%" )
Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • 1260 replies
  • September 14, 2023

That's a lot better thank you very much

Last bit.....Is there a way to remove the excess symbols?


If you mean floating point issue when displaying currency value, use ROUND({Rollup profit ($)}, 2) , for example, to preserve 2 digits after comma.

B
Forum|alt.badge.img+4
  • bartdubs
  • Author
  • Participating Frequently
  • 6 replies
  • September 15, 2023

for example like this

IF( {Rollup profit ($)}, "$" & {Rollup profit ($)} ) & IF( AND({Rollup profit ($)},{Rollup profit (%)}), ", " ) & IF( {Rollup profit (%)}, INT({Rollup profit (%)}*100) & "%" )

thank you very much!!


Terms & ConditionsAccessibility statement