Perform a basic calculation on integers inside a string

Topic Labels: Formulas
Solved
804 2
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

I would like to achieve the following:

Multiply all the quantities inside Part List Output (2x, 8x, etc) by the Quantity value. So in the first row in the example provided I want to multiply all of the integers in the comma-separated strings by 6.

I’ve mocked up a demo output in Calculated Part List.

One complication to note - the strings can also contain integers that I do not want to target for calculations, eg. 1x Shelf 812mm.

I can easily manipulate the Part List Output if necessary.

For example, wrap in special characters:
`_2_ Large T Side with Holes, _2_ Large A Side, _1_ Large Base, _8_ Spring Clips`

Perhaps @ScottWorld has managed this?

1 Solution

Accepted Solutions
18 - Pluto

As far as I know, there is no way to do what you want to do without writing a custom script that might be able to parse the data for you.

The other solution would be to change the structure of your base by breaking out the quantities from the descriptive text into separate fields in the parts table, and then creating an entirely separate table that acts as a “junction table” to keep track of the parts for each order. This “junction table” would act as the “intersection” between orders & parts, so each record would be linked to one order & one unique part.

The math could be performed in the junction table, and you can also create a formula that gives you the appropriate text for each line item. Then, you could lookup the calculated text in the orders table which would create the full sentence you’re looking for.

2 Replies 2
18 - Pluto

As far as I know, there is no way to do what you want to do without writing a custom script that might be able to parse the data for you.

The other solution would be to change the structure of your base by breaking out the quantities from the descriptive text into separate fields in the parts table, and then creating an entirely separate table that acts as a “junction table” to keep track of the parts for each order. This “junction table” would act as the “intersection” between orders & parts, so each record would be linked to one order & one unique part.

The math could be performed in the junction table, and you can also create a formula that gives you the appropriate text for each line item. Then, you could lookup the calculated text in the orders table which would create the full sentence you’re looking for.