Skip to main content

Hey there,


My production team is using one base to handle our production. (supply, suppliers etc)

One table in this base is dedicated to our PRODUCTS.

We have one column for price (currency type).

An other table, in this same base, is dedicated to VARIANTS.

It is simply a linked record to a PRODUCT and a size.

The price is “looked up” from product, and thus is showing as a currency type on the variant record.


On the other side, the SALES team is sync the VARIANTS table from production to another base (SALES) to get a list of all of our product.

To create a sale in the LINE ITEMS table, they pick a VARIANT from the synced table in their base.

They write a quantity. And obviously the quantity should be multiplied by the VARIANT PRICE to have the line price.



My problem : in the synced table (in SALES base), the price is shown as a TEXT. (“€60.00” for instance)

And I cannot manage to make the formula work.

I tryed VALUE({price})*quantity but it says #ERROR!



It seems like sync records converts currency to string. (confirmed in the Airtable doc)

But this string is not really working as expected when pulled through a LOOKUP.


Anyone has encountered this issue ?

Thanks !

Welcome to the community, @Eric_NEMO! :grinning_face_with_big_eyes: This isn’t necessarily an issue with synced tables. It’s an issue with how lookup fields behave.


Long story short, lookup fields will return an array—a collection of values, not just a single value—most of the time, even if the field is only looking up a value from a single linked record. To force-convert the array into a string so that the VALUE() function won’t choke, concatenate it with an empty string:


VALUE({Wholesale HT (from PRODUCT) (from VARIANT)} & "")

A while back I made a table to help demystify what’s going on with lookup fields when retrieving data of various types. You can find that here:


Welcome to the community, @Eric_NEMO! :grinning_face_with_big_eyes: This isn’t necessarily an issue with synced tables. It’s an issue with how lookup fields behave.


Long story short, lookup fields will return an array—a collection of values, not just a single value—most of the time, even if the field is only looking up a value from a single linked record. To force-convert the array into a string so that the VALUE() function won’t choke, concatenate it with an empty string:


VALUE({Wholesale HT (from PRODUCT) (from VARIANT)} & "")

A while back I made a table to help demystify what’s going on with lookup fields when retrieving data of various types. You can find that here:




Hey Justin,


Thanks a lot that is indeed what i figured out !



https://support.airtable.com/hc/en-us/articles/360042312194-Lookup-field-overview

Under the hood, the information shown in a lookup field is gathered into a list of values, also known as an array of values. In a formula field you cannot access a lookup field’s values directly, rather you can access the list of values together, as a unit. Meaning, that if you plan to reference a lookup field within a formula field, you may have to first convert the values into a data type the formula you are working with can accept. We recommend checking out our array functions when working with a lookup field directly within a formula. Alternatively, we recommend using a Rollup field which can use a formula directly on a linked record’s fields.



I had to play with the array functions to make it work !

I leave this topic here in case someone else is stuck on this 🙂

Reply


Terms & Conditions