Parsing multiple strings (numbers) from a single line of text column and adding them together

Question

I have a user who has requested a way to track vendors and their price quotes for project work. The user would like to have the list automatically calculate the total of all quotes listed. There can be an unknown number of vendors for each project, potentially upwards of 10 in some cases. One solution I'm considering is as follows:

Allow the user to format the vendors and quotes into a single line of text column using a naming convention "VENDOR - $12345678;" and use a calculated column (or some other solution) to parse out the values, convert them to numbers, and calculate the solution.

An example might look like this:

Vendor1 - $10000; Vendor2 - $73200; etc...

Thoughts? I also have access to InfoPath 2013. I'm just not sure where to go without generating a large number of columns and, as always, OOTB solutions are preferred for simplicity of long-term support.

Answer 1

Well one solution would be to have a Total field on the form the user has to manually input, breaking and parsing that is going to be difficult with any ootb tools.

A slightly more sophisticated method would be to use javascript or jQuery to break it apart as it is in a somewhat predictable parrern. Even this can be fragile if people don't adhere to the proper format.

I would steer towards InfoPath, it handles repeating data very well and would allow them to enter as many vendors as necessary and you can simply aggregate the totals and promote that to the list. If you don't want to worry about a large number of columns, store it as a file instead of a list form, and you can pick and choose what you want to promote to field values. The rest of the data lives in the form XML.