How to split a cell into multiple rows.

mlogan101

I am working on an input sheet where the user is prompted about whether their submission is a batch order of parts. If they select yes then they are supposed to input however many into the sheet in a comma separated list. What I am trying to do is see if there is a way for smartsheet to be able to:

1. Identify each part number between each comma and parse it out into a new row for each separate part.
2. Copy over the other cells from the submission into these new rows.
3. Assign a batch order number to group these parts together.

Thank you to anyone who can help clarify this!

SueinSpain

What you are looking for is a relational database type date where there is a header/common data and then details associated with that header.

Smartsheet is not a relational database in the same way Excel spreadsheets are not.

I have a similar issue but for fixed customers.

They will send us header information and then a number of product rows. I need to accept this "batch" of information, process each product individually, and then return information back for the "batch"

Currently looking at a solution and how to minimise repeating data entry

1. for that customer assign a customer number and hold all the header information in a separate table. This enables the data entry to just put in customer number e.g. 63 and let a formulae lookup populate the fields with the header information for each row.
2. have a reference number (NOT automatically generated as that is at line level) to batch / group them together. In my solution this is the customer PO reference number, They have sent a sheet with header details, their PO ref and then 10-200 products and quantities they want. So I use the PO ref to know these individual lines are all together
3. when reporting back group by PO ref as of the 10 lines 8 may be totally fulfilled and 1 partially fulfilled and 1 no stock

Hope this helps

My other solution when I know there are not a huge number of detail lines is to have them as separate fields (detail1, detail2 etc.) within the records. So for example my repairs smartsheet has all the customer details / header information in the first columns, then det1 product code, det1 product description, det1 quantity, det2 product code, det2 product description, det2 quantity etc. as following columns. However I ussually limit this to a maximum of 10. For the data entry form I have det1 details as mandatory, det2 product code as optional but if filled in the desc & quantity mandatory. If det2 quantity is not blank then show optional det3 product code on form etc. so the for expands only as the entreis are filled in. If just one prodict then all they will see on the form is the details for det1 plus a product code for det2 that if they leave blank means they carry on - it saves having to scroll past a load of fields they do not intend to use.