Add multiple rows into a another table

[Romagi]

Hi there ! I am starting using Appsheet and i get stuck while trying to create an action that adds multiple rows at once. Let me detailed my issue :
I have a Table A where I have a [start date] and an [end date] that gives me a [duration] in number (eg : 7 days)
I have a Table B where I list events with a [date] column
Note that Table A and Table B are linked with a [Patient] column

What I am trying to achieve is the following: When a user submit a form for Table A with n as a duration, I want to add n rows in the Table B with the date column incrementing itself adding +1 to the day of the [date] column until [End Date] from Table A is reached.

I am obviously open to changes in my data structure to make it work . I Have been searching for a while and couldn’t manage to do it…
I hope I made myself clear as I am a non native english speaker.

Thanks you for your help.

[Kirk_Masden]

Hi @Romagi! Welcome to the community!

I know how to add one row at a time but I’m not sure about how to add multiple rows. I assume you have seen this:

New Action: Add a Row to a table Announcements

We just added an initial version of a new action ‘Data: add a new row to another table using values from this row’ Please try it and give us feedback. Documentation, etc coming later this week. The brief version is here: let’s consider an example with Orders and OrderDetails as two related tables in an app. Add an action ‘CreateChildRow’ to the Orders table that will add a row to the OrderDetails table. You can specify expressions to assign to each column of this row. For example: Order = [O…

How many rows do you think you would need to add at once? In other words, if your duration is 00:05:20, how many rows would that be? If you could get by with just adding one row, that would be easier I think.

[Bellave_Jayaram]

There’s no loop action for n rows - you will need to create as many actions as the max number of rows you want to have.

[Sid_dev · Accepted solution]

Workflows can be used to loop for n number of days, when you have a table with all the possible dates (Table C). After user enters a start & end date - use a webhook workflow to run the Add action in Table B by selecting the dates in range from Table C.

1. Create a workflow for when a new row is added to Table A
2. Go to Manage > Integrations & Enable ‘IN:from cloud services to your app’ - Make not of the App ID - this needs to be chosen in the next step
3. Add a new Workflow for ‘ADDS_ONLY’ for Table A. Create a ‘Webhook’ workflow action, with preset ‘Appsheet: Action’
   Body can be like this

{
  "Action": "Add",
  "Rows": [
<<Start:SELECT(Table C[Date],AND([Date] > [Start date], [Date] < [End date]))>>
    {
      "ID": "<<UNIQUEID()>>",
      "Date": "<<[Date]>>"
},
<<End>>
  ]
}

[Bellave_Jayaram]

This solution requires a business subscription plan though.

[Sid_dev]

No. Premium/Pro is enough.

[Bellave_Jayaram]

Ok, thanks. For those looking for documentation about this, here’s the link https://help.appsheet.com/en/articles/3454052-webhook-templates

[Romagi]

Hello Sid, it worked perfectly ! Thank you very much + I needed a table with all the date so it is perfect !

[LeventK]

Hello @Sid
May I ask why didn’t you used AppSheet: Add row preset instead ot AppSheet: Action preset? Is there a specific reason for that? Asking this because, AppSheet: Action preset is mostly used for triggering a Behaviour action rather than adding rows.

Besides; I notice that the Properties key is missing in your JSON template and normally when run, AppSheet server should murmur about this indicating that the Properties is missing.

[Sid_dev]

@LeventK
I guess you can skip one line ("Action": "Add") in the Body, if you use the ‘AppSheet: Add Row’. I’ve not tried that though. I don’t know of a functional difference.

There are defaults for all properties, so no complaints from AppSheet servers. Left that out for brevity.

[WillowMobileSys]

@Sid @LeventK @Kirk_Masden

Ok, I’ve been “biting my lip” on this but I’ve chewed a hole and now have to ask!

If there is a table created to select a list of dates from, why not use these provided Actions below?

• “Execute an Action on a set of rows” - the rows are the selected dates
• “Add a row to another table using values from this row”

I believe together they accomplish the goal with the added benefit that you can attach these actions as a group to the “Form Saved” property so that the user sees the new rows immediately on their device.

Using the Workflow, would require the user to perform a Sync to see the added rows which may not be ideal.

Am I missing something?

[Sid_dev]

@WillowMobileSystems You are right. Using actions is better.

One additional thing the workflow offers is ability to insert data from Table A into Table B - which is not an ask here.

[WillowMobileSys]

If you mean updating an existing row in Table B with info from a row in Table A. I totally agree! And i should check into handling it with a webhook myself!!

By the way, there is a Feature Request to add this Action take a look and vote it up! It’s near the top in votes as it is now.

[Romagi]

Hello guys,

I get stuck enriching my feature based on your solution @Sid. What I want to achieve now is this : I want to add a row every day out of 2 between a [start date] and an [end date]. I partially managed to do this with the following fomula:

{
  "Action": "Add",
  "Rows": [
<<Start:SELECT(Table C[Date],AND([Date] >= [Start date], [Date] <= [End date], MOD((DAY([Date])-DAY([Start date])),2)=0)>>
    {
      "ID": "<<UNIQUEID()>>",
      "Date": "<<[Date]>>"
},
<<End>>
  ]
}

But when [Start date] and [End date] belong to a different months (eg : from 7/25/2020 to 8/5/2020), the formula doesn’t work anymore as for instance MOD(DAY(“7/31/2020”) - DAY(“8/1/2020”), 2) = 0. The formula then adds a row for two consecutive days (the 31st of July and the 1st of August).

Any clue on how to add a row every 1 day out of 2 ? I couldn’t figure it out…

If it interests other, I also apply the formula for a selection of weekday that the user select. For instance, add a new entry for every Monday and Wednesday of every date between a [Start date] and an [End date].

Thanks a lot,
Romain

[aucforum]

Nice discussion.

Can someone please make a demo app so as to be able to look under the hood to see it function? Its a bit abstract to follow for those like me who have never used webhooks before.

[Steve]

Add any number of rows from any row within a table Tips & Tricks 💡

Definitely a frequently-asked question: how do I add some number of rows to a table automatically? It’s possible, but it’s not easy. This article details an approach that can be accomplished entirely within AppSheet (no API use), within the app itself (no workflow use), and accommodates an arbitrary number of rows (no built-in count maximum). While I’ve tried to make the guide accessible for less-experienced app creators, make no mistake: this is an advanced technique. I hope you find this us…

[aucforum]

I decided to suggest a new feature instead of this convoluted approach.

See this posting:

Add a Simple FOR LOOP condition to allow for Repeating a Sequence of Actions like Multiple Rows in a... Feature Requests

Continuing the discussion from Add multiple rows into a another table: I could also make a proposal that the action: “Grouped: executive a sequence of actions” could be simply expanded to be a loop by having an additional expression for us developers to state what the condition of that FOR LOOP would be. It would allow for iteration, repeating a Sequence of Actions like ‘Multiple Rows in another Table’ and more! You already have the logic that is scoped to constrain some conditions to particul…

[WillowMobileSys]

aucforum:

I decided to suggest a new feature

Good! That is exactly what we should do in the community. Find a need for some new feature and submit a request for it.

But also, at the same time, come up with or communicate a workaround to overcome the obstacles. Because it will likely take time to implement the requested features.

If you need such functionality. I wouldn’t wait for AppSheet to implement it. It will be a while before they can roll it out.

[aucforum]

WillowMobileSystems:

d! That is exactly what we should do in the community. Find a need for some new feature and submit a request for it.

I am careful in what I wish for. I would not want to ask for something too complex, like reusable components (I have but it sems to be forgotten), but this is rather small and achievable in a short time frame.

A side comment is that its hard to get mass support for new features if most users are not informed of them. (A bit like politics in the real world…)

[aucforum]

Is this demonstrated in via this wiki posting?

GitLab

Add n rows to table automatically · Wiki · ascommunity / Demo apps

Apps that demonstrate specific features available in AppSheet.

[Sid_dev]

@Romagi For calculating the difference in days use this MOD(HOUR([End date] - [Start date])/24, 2 )

[Romagi]

Thanks @Sid,

Here is how I managed to do it :

MOD(TOTALHOURS([Date] - [Date de début])/24,2)=0

[TeslaDom]

Hello. I’m trying to do something like the situation in this post.
I will like create several rows based on a number previously defined in another table.
I have table RUTINES in that table I save a number could be, for example 3, and I have another table called SESSIONS. I will like to create 3 new rows in SESSIONS table when I create a new row of RUTINES table.(Based on example of 3) The SESSION table will have information from ROUTINES table and own information. I tried to do following the answer of this post, but I couldn’t. Any help is appreciated. Thank you!