Skip to main content

Lookup fields with numbers became text in sync table

  • March 12, 2021
  • 7 replies
  • 74 views
C
Forum|alt.badge.img+10

Hi!

I’m working on two separate base for my organization: the first manages the order, and the second the tracking and payment process.

With the sync tables I’m able to get the tables from the order in the second base for the planning and processing of the shipment and payments .

But I’m encountering a problem: there are some lookup fields about quantities that I need to process as number on the second base. The synced table show them as text, so the lookup on the tracking table it is also showing them as text, but I can’t modify the format to a number. I’ve also already tried to add another field with a VALUE() formula, but it gives an #ERROR! and doesn’t evaluate the text into a number.
Any idea how to proceed?

Many thanks,
Claudio

7 replies

F
Forum|alt.badge.img

Hi Claudio,

I’m experiencing a similar issue. Have you found any way to solve it yet?

Best regards,
Felix

A
ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • April 15, 2021

This seems like a bug in Airtable’s sync feature. When you sync a lookup field that was a number field in the source table, it becomes a text field in the destination field.

I truly hope that Airtable didn’t purposely design it this way, and that it is just a bug that they can fix.

Be sure to email support@airtable.com and report it to them, and write back here with what they say!

In the meantime, you can TRY to use the VALUE() function by turning the lookup field into a string (instead of an array) like this, but note that the VALUE() function also performs math and other strange number conversions, so it’s not without its own problems:

VALUE({Lookup Field}&"")

L
F
A
C
Forum|alt.badge.img+10
  • Claudio_Brenni
  • Author
  • Known Participant
  • April 16, 2021

Hi Claudio,

I’m experiencing a similar issue. Have you found any way to solve it yet?

Best regards,
Felix


Hi Felix!

My workaround was to include in the table to be synced a formula field that is reading the Lookup field and converting it in a number. So for exemple if your LOOKUP field is called A and contains a number, I than add another Formula field, I call it Number_A and just add the field A for the formula and I format this field as a number in the formatting menu. I then create a view which hides the LOOKUP fields and leave only the one with the formulas fields instead. I than sync this view to the desired base, so the data are recognized as number in the synced table and can be used for calculations and visualizations.

I hope my explanation is clear to you, otherwise don’t hesitate to recontact me.

Best,
Claudio

ScottWorld
K
F
F
Forum|alt.badge.img

Hi Felix!

My workaround was to include in the table to be synced a formula field that is reading the Lookup field and converting it in a number. So for exemple if your LOOKUP field is called A and contains a number, I than add another Formula field, I call it Number_A and just add the field A for the formula and I format this field as a number in the formatting menu. I then create a view which hides the LOOKUP fields and leave only the one with the formulas fields instead. I than sync this view to the desired base, so the data are recognized as number in the synced table and can be used for calculations and visualizations.

I hope my explanation is clear to you, otherwise don’t hesitate to recontact me.

Best,
Claudio


Hi Claudio,

Thank you for the explanation! This is also the way I decided to continue. The Airtable support suggested to switch to rollups instead but this didn’t work out.
The tip to create a separate view with the fields in output format really brings in structure and helps with the issue. Nevertheless, the Airtable team has the problem on their radar and will work on a solution.

Best regards,

Felix

Ricardo11
K
  • Kendrick_Kirk
  • New Participant
  • July 28, 2021

Emailed them. I can’t believe they did this on purpose. It makes no sense

ScottWorld
L
Forum|alt.badge.img

Emailed them. I can’t believe they did this on purpose. It makes no sense


Just an update here - actually swapping to a Rollup did work for me.

Ricardo11
R
A
Ricardo11
Forum|alt.badge.img+16
  • Ricardo11
  • Inspiring
  • July 5, 2024

Changing from look-up rol roll-up helped me sync a rich text field from one table to another.

Terms & ConditionsAccessibility statement