Expression

[Judi_Phelps]

I have a column that summarizes the parts needed for a repair separated by commas and the cost of each part in parentheses (ie. keyboard ($24), bezel ($12), screen ($34). How can I extract and add the total cost of repair parts?

[LeventK]

@Judi_Phelps
Assuming your list is fixed: {“keyboard ($24)”, “bezel ($12)”, “screen ($34)”}, below expression will give you $70

CONCATENATE(
	"$",
	NUMBER(
		SUBSTITUTE(
			SUBSTITUTE(
				INDEX(
					SPLIT(INDEX([List],1)," "),2
				),"($",""
			),")",""
		)
	) + 
	NUMBER(
		SUBSTITUTE(
			SUBSTITUTE(
				INDEX(
					SPLIT(INDEX([List],2)," "),2
				),"($",""
			),")",""
		)
	) + 
	NUMBER(
		SUBSTITUTE(
			SUBSTITUTE(
				INDEX(
					SPLIT(INDEX([List],3)," "),2
				),"($",""
			),")",""
		)
	)
)

[Judi_Phelps]

Unfortunately, the list is not fixed. It is comprised of the repair parts selected from a list based on what was in need of repair on a device. The number of parts can consist of 1 to 8 and if it is a total replacement will be a triple digit number.

[Aleksi]

Could it be better to add parts as a child table? Then you could add as many parts as you need and the calculation would be easy.

[Judi_Phelps]

I have 1 table that contains a list of models, parts associated with each model and a column with the cost of each part. Our techs choose a model, then get a list of parts that they can choose. This comma separated list is in a column on my form table. I’m having difficulty getting the costs of parts associated with the selected repair part to get a calculation.

[Aleksi]

If your table structure is like that and I’m assuming the Parts column is an EnumList, you can sum the total directly from the Parts table like…
SUM(SELECT(Parts[PRICE],AND([MODEL]=[_THISROW].[MODEL],IN([PART],[_THISROW].[PARTS]))))

[Judi_Phelps]

the column name in my parts table is cost instead of price so changed that. added the expression to the cost column on my form table under “auto computer”. testing indicated a total of 0.00. when I enter a form, there is nothing calculated.

[Aleksi]

Is your “Parts” column an EnumList?

[Judi_Phelps]

yes

[Aleksi]

Please take a printscreen from your Parts table (spreadsheet)

[Judi_Phelps]

[Aleksi]

Please send your email address to aleksi@appsheet.com and I will share a sample app where it’s calculated.

[Judi_Phelps]

That’s exactly what I was looking for, thanks!

[Judi_Phelps]

I had a validation expression on the cost column that was causing an issue with the calculation. It’s working now. Thanks!

[Aleksi]

You’re welcome