Skip to main content

Hello all,

New to Airtable and I'm trying to build a product costing and BOM system.

I have worked out from searching for posts on this forum that I can split a Lookup cell based on text using the following

 

IF( FIND(",", {Product Costings}),
LEFT({Product Costings},
FIND(",", {Product Costings})-1
),
{Product Costings}
)

 

But when I try to use this same formula to split the cost cells it doesn't seem to work.

I basically have a 'Total Cost' column which is currently filled with £0.70, £1.00 and £0.11

I want to split these into separate cells as I want to specify quantity for each value. For some reason the above script will not work for numericals, and it just lists the full 0.70, 1.00, 0.11

Is there something I need to change to make this work? Or are there any other alternatives?

What I want is -

Kit Row - Combined Individual products that make up the kit - Number of each individual product needed to create the kit - Price of each individual item - Total price of each individual item (individual item cost * qty)

I've sorted this for the individual product names (BOM1) but can't make it work for the cost (BOM1 cost)

Thanks

Hi,
The formula is "if value contains comma, show left part before the comma, otherwise show full value"
So, you should fix it.
But you also have second issue - LEFT is text function, it will fail with number. To convert number to text, you can add it to the empty string or wrap into concatenate. So, instead  of {FieldName} use {FieldName}&""  or CONCATENATE({FieldName})

Note that when you convert currency, result will be like '0.70' . Data stored in column as number, while currency symbol is a column property, so it is not converted to text.

Hi,
The formula is "if value contains comma, show left part before the comma, otherwise show full value"
So, you should fix it.
But you also have second issue - LEFT is text function, it will fail with number. To convert number to text, you can add it to the empty string or wrap into concatenate. So, instead  of {FieldName} use {FieldName}&""  or CONCATENATE({FieldName})

Note that when you convert currency, result will be like '0.70' . Data stored in column as number, while currency symbol is a column property, so it is not converted to text.


Thanks, I actually went with a different route for this, making a separate BOMs table then linking it.

Reply


Terms & Conditions