Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Lookup fields with numbers became text in sync table

2673 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Claudio_Brenni
6 - Interface Innovator
6 - Interface Innovator

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 7
Felix_Kreitschm
4 - Data Explorer
4 - Data Explorer

Hi Claudio,

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

Best regards,
Felix

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}&"")

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

Kendrick_Kirk
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Ricardo
7 - App Architect
7 - App Architect

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