How to create a variable number of rows with custom calculations

[dario]

Hi, I'm checking Honeycode for a simple application and it looks great, but I have an issue.

My application needs to create N rows according to an input:

The gist is that if the last field (Users[Total]) has value "1", I want to create Users[Total] * 5 rows with specific values in each one, using a simple formula to calculate each value.

The full workflow should be:

1. User fulfills the form.
2. A row is created in the table Users. This table also has a column "Position" that will be updated using =MAX(Users[Position]) + 1 from an automation on row creation.
3. Using THISROW[Total] value, create THISROW[Total] rows linked to the new row's rowlink, each one with a value calculated as THISROW[Position] + N + M where N is the current iteration and M will be:
   • 0
   • 2000
   • 4000
   • 6000
   • 8000

I hoped to have some kind of simple scripting or calling an external Lambda and store the returned result, but it seems this kind of features isn't currently available.

[Alyssa]

Hi @dario,

Welcome to the community, and thank you for posting here!

I'm happy to help, but I'm not quite sure what you're trying to achieve. Could you elaborate on the goal of the third step in your workflow? And perhaps explain the use case of your app?

Depending on what you're trying to do, I might be able to help you build a solution that could meet your needs.

[dario]

Sure @Alyssa. I'm trying to implement a simple tool to manage a local lottery. We want to track which numbers are handled to each person.

Each time a person buys a ticket, we want to assign them five numbers in the following ranges:

• 0000-1999
• 2000-3999
• 4000-5999
• 6000-7999
• 8000-9999

The first player will have the numbers 00000, 2000, 4000, 6000, and 8000. The second player, 0001, 2001, 4001, 6001, and 8001. And so on.

If a person buys more than one ticket at a time, we'll assign multiple sets of numbers following the previously stated logic.

Edit: I implemented an equivalent app in Ruby. This would be the core logic:

@player = Player.create!(player_params)

value = Ticket.maximum(:number).to_i
value -= 7999 if value > 0

tickets_bought.times do
  [0, 2000, 4000, 6000, 8000].each do |base|
    number = (base + value).to_s.rjust(4, '0')
    Ticket.create(player: @player, number: number)
  end
end

[Alyssa]

Hi @dario,

Got it! Thank you for that clarity.

Cool use case too of making a lottery app!

I'll start by noting that you cannot specify a loop that way in Honeycode (specifying how many times it runs from a user input), because the iterators in Honeycode instead loop over existing data to evaluate if an automation step will run again. This is a good use case though that I could file an improvement request for.

However, you can achieve what you're asking for if you have a defined number of ticket sets a user could select. For example, you could have a dropdown that a user selects the number of sets they would like. You can then have an automation with action steps that each write a new row for the defined amount.

This is the example app I built to show you this:

This is the form in app:

And the list in app:

I have a rowlink field (dropdown) for how many lottery ticket sets the user would like. I did up to 5 sets for this example:

Setting up the field in Builder:

This is the table I created to have a defined amount of sets:

And the dropdown in the app:

In Tables, I made a separate table with columns for the contact name, ticket set number, and for each lottery number for the set. I put in the desired default start data for the entries to count off from:

In Builder, on the Submit button, I created an automation with an "Add a row" action that uses the MAX() function increment the lottery number by 1 from the last entry (similar to how you did).

So in my example, the first action adds a row and updates all the columns. It starts with just numbering this as set "1":

And I update each column of the lottery numbers using the MAX() function.

Example formula I used: =MAX(LotteryNumbers[Lottery 1])+1

Then in the same automation, you can add more actions for each of the additional sets (Select the "Add a row" action again). In the run options for each of these successive steps, you would define the step to run only if the value is equal to or less than the specified amount of tickets. So for example, for the second set of tickets you would write the following in the run options:

And you can define it as the second set like this:

Then add the same column updates as in the previous step, using the MAX() function.

And for the third set of tickets, add another action that is set up similarly. And so on for each of the following sets.

And now when a user selects a number of sets, it will write the specified number of rows and increment the value of each lottery ticket by one:

In a list in the app:

In Tables:

I hope this example helps! The key is having a defined number of sets a user can select.

However, I'll still pass on the improvement request to our team, of being able to input a number to specify how many times an automation may run (as noted above in my reply).

Let us know if you have any further thoughts or questions on this.