Help

Create quantity of records based on a field

Topic Labels: ImportingExporting
Solved
Jump to Solution
1162 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Aaron_Smith
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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?

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

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?

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

Aaron