Skip to main content
Solved

Create quantity of records based on a field

  • July 2, 2020
  • 2 replies
  • 21 views
A
Forum|alt.badge.img+4

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?

Best answer by Justin_Barrett

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?

2 replies

Justin_Barrett
Forum|alt.badge.img+21
  • Justin_Barrett
  • Inspiring
  • 4647 replies
  • Answer
  • July 17, 2020

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?

A
Forum|alt.badge.img+4
  • Aaron_Smith
  • Author
  • Participating Frequently
  • 8 replies
  • July 17, 2020

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


Terms & ConditionsAccessibility statement