Re: Perform a basic calculation on integers inside a string

JayArmstrong · ‎Jan 31, 2022

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?

ScottWorld · ‎Feb 01, 2022

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.

To learn more about junction tables and many-to-many relationships, check out this article:

See Solution in Thread

ScottWorld · ‎Feb 01, 2022

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.

To learn more about junction tables and many-to-many relationships, check out this article:

JayArmstrong · ‎Feb 02, 2022

Thank you @ScottWorld for the thoughtful reply. I had suspected as much. They do actually live in another table as separate fields so I will head down the junction table avenue. Thanks again!