Create quantity of records based on a field

[Aaron_Smith]

I manufacture products, and have a customer that buys several items at once. They are requesting a spreadsheet with one Item and description of each item per row in said spreadsheet.

Example: My current base shows the following “3 Pieces of green widgets that weigh 50 pounds each and 3 pieces of red widget that weigh 10 pounds”. The customer is requesting the information to be displayed in a spreadsheet as below

green widget 50 pounds
green widget 50 pounds
green widget 50 pounds
red widget 10 pounds
red widget 10 pounds
red widget 10 pounds

Does anyone have a suggestion as how to do this automatically?

[Justin_Barrett · Accepted solution]

Sorry that you haven’t had an answer on this before now. There’s some info missing re: the structure of your base, so I’ll lay it out based on how it might be set up.

Let’s say you’ve got a single row per widget sold. In each record, you have a link field named {Widget} where you select a widget from a [Widgets] table, you have a lookup field to pull that widget’s weight from that linked table, and you have a {Quantity} field to specify how many were ordered. With that info, you could create a formula that outputs a series of rows depending on the widget selected and its specified quantity. Going back to your example, if one record records 3 green widgets ordered, and the lookup pulls in a weight of 50 pounds, the formula might look something like this:

REPT(Widget & ": " & Weight & " pounds\n", Quantity)

The REPT() function repeats a string a specified number of times. The output in this case would be:

Green widget: 50 pounds
Green widget: 50 pounds
Green widget: 50 pounds

You can then create a view where you can export that table’s contents as a CSV, which can then be imported into a spreadsheet and manipulated further if you wish.

Will that work for your needs?

[Aaron_Smith]

Wow! Justin thank you for the solution. I think this will work nicely. Once I have it implemented I will confirm. Thanks again.

Aaron