New Action: Add a Row to a table

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.

  1. 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 = [Order Id], Product = “Bananas”, Quantity = 5. Note that [Order Id] here is the value of the column OrderId in the Order row on which the action is being performed.

  2. Use this action in the UI, or compose it with another action, or use it in a workflow rule when a new Order is created, etc.

Any columns not defined will get their initial values or app formulas. If a required column is not specified and does not have an initial value, you will be shown an error in the editor.

47 119 20.1K
119 REPLIES 119

Lynn
Participant V

Nice, works great. Thanks

So pumped for this feature! Thanks for the release.

What!!! Absolutely Outstanding!! A million thank you’s for this one! 🙂

Jonathon
Participant V

This is huge!

Jon_S
Participant V

Thanks for this! Really looking forward to the docs + some examples of dynamically adding multiple rows to another table. That would really enable me to stop spending cash on Zapier/Integromat integrations…

@praveen. Had a look at it last night. Couple of questions. Maybe I am missing how.

  1. How do you add multiple rows, meaning for multiple rows that fits a criteria or rule
  2. I see Columns marked as REF in the Parent table is not available in the list if Columns one can select. I have a case where I want the value in that REF column to also be “defaulted” to the Child Table Record

Here’s a sample: https://www.appsheet.com/Template/AppDef?appName=Customers3-16350

It is a variant of the OrderCapture sample. It has three actions and a workflow rule:

  1. ‘Prefill 2 Apples’: the action is on the Orders table and it adds a LineItem row for that Order, asking for 2 apples
  2. ‘Prefill 3 Bananas’: the action is on the Orders table and it adds a LineItem row for that Order, asking for 3 bananas
  3. ‘Prefill’: the action is on the Orders table and it composes the previous two actions. It can be invoked directly via a UI button (try it in the app) or via a workflow rule.
  4. A workflow rule that runs when a new Order is added — this automatically calls the Prefill action (which adds two LineItems to the order)

Now, I’ve marked this as a public sample, so that any of you can see it. However, this means you will not be able to save/sync any updates. So if you’d like to play with it, especially the workflow rules, please make a copy.

Hi Praveen

Just checking. It it possible to only create new one row per action?

I’m going to be building an App where it will need to do the following:
A row in TableA is changed
Based on a matching condition - TableA[ColumnA]=“Banana”
It copies all rows from TableB to TableC where TableB[ColumnA]=“Banana”
TableC is a subtable of TableA

Will this be possible or is it a Google Script job?

Simon@1minManager.com

Hi Simon, here’s how to model this:

  1. Define an action on TableB that copies a row over to Table C
  2. Define a “reference” action on TableA … it identifies a set of rows in TableB and for each of them, invokes an action (the one you defined in step 1) on that row
  3. Now trigger the reference action via a workflow rule with the matching condition.

Hi @1minManager,

I need to accomplish something very similar to what you describe here. Were you able to work this logic out? I’m having some difficulties figuring it out and wondered if you could share your solution?

Hi @Michael

I haven’t tried it yet. But I’ve planned to do a test app either this week or next week. Email me over the email you use for your Appsheet account and if I can get it working I’ll share it with you

Simon@1minManager.com

… Share with us on this thread??

@praveen

Hmm but that is pre-set to add 2 rows.

Here’s a typical scenario I get faced with.

Imagine we have a MASTER table which has an EnumList field called LINE-ITEMS which contains 5 choices - ‘Andrew’,‘John’,‘Tim’,‘Dave’,‘Mike’.

Naturally, I have got a DETAIL table where i expect the new line-item rows to be created based on my choices in the EnumList.

I then go ahead and create a new MASTER entry and select choices ‘John’, ‘Tim’, ‘Mike’.

The app then should create the following entries in the DETAILS table

+------------+-----------+
| DETAIL_UID | LINE-ITEM |
+------------+-----------+
| 1          | John      |
+------------+-----------+
| 2          | Tim       |
+------------+-----------+
| 3          | Mike      |
+------------+-----------+

If I was to use your method, I would have to have a Grouped Action specifically for the scenario of the EnumList containing John, Tim, Mike… which means that I will be forced to create a ton of Group Actions for each possible combination.

I still think it’s a nice addition to have though, i don’t think it’s for me at this point (unless i missed something)

Hi Alex, here’s how you would achieve that.

  1. have a separate Details_Template table with five dummy rows in it, one for each of the choices.
  2. Define an action on this Details_Template table to copy a row into the Detail table
  3. Now define an ‘reference’ action on the Master table … this defines an expression that identifies a set of rows in the Details_Template table (those whose name matches something in the enum list column of the Master row) and invokes the copy action (defined in Step 2) on them

Hello

i’m a french developer working with AppMaker,

it took me 1 year to be able to create complex applications for customers. It was for me hard to learn, but now I’m fluent with it (2 years using it) and I can tell that it is a super product for creating web applications very fast, but it is more oriented for PC users then for smartphone users. All my customers needs, I was able to develop it, this tool has no limits, because using javascript css etc…

I’ve to check now if AppSheet can replace it for the applications I’ve made, and here are my questions.

i ‘m going to make a POC (a test application) to check if : all functionalities needed for the application can be done with AppSheet.

I need to make treatment done (complex select, adding data, updating etc … ) I’ve search on the AppSheet documents and video, and I’ve seen that I can use actions and workflow. I’ve founded OrderCapture sample and Customers3-16350 this but it is simple, I need more complexity? , I would like to avoid using API to directly work on the Spreadsheet. Where can I find more complex templates application for me look at them.

I’ve been looking at AppSheet since 3 days and I find it very easy to use.

Welcome to Appsheet. Sample app should have been prepared to concentrate on a particular function and features only so that the the learner can get the knowledge out of it to implement same or similar function into their app. We can build pretty much complex app but quite easily.

All depend what you want to do.

There is no direct solution to place custom css , HTML or javascript as Appsheet stays with notion to keep the platform as zero code platform. Personally I wish to have capability to place the own simple code though.

I have been developing app with appsheet for almost 3 years. But I only had few case only I could not meet with customer wishes meaning the appsheet quite powerful to cope with those unique and complex requirement to the app.

Hi

Thanks for your quick answer
here is my problem with only 3 tables

tables : Products, Customers and OrderLines
for each OrderLine.rows there is 1 Product and 1 Customer

Products.ID =Unique Key
Products.Name
Products.color

Customers.ID =Unique Key
Customers.Name
Customer.country

OrderLines.ID =Unique Key
OrderLines. ProductID Ref to Products
OrderLines.CustomerID Ref to Customer
OrderLines.Qty
OrderLines.Date

I want to have a search screen with these 2 fields, where the user input :

Country_IN : France
Color_NO : Yellow

This will display all the Customers.Name that are in the country France
and that haven’t ordered any product that are Yellow

how can I do that. my request is in fact much more complicated :

the users wants to know: who are the clients that haven’t bought this type of product since the last year, etc …

with AppMaker I create array or temporary table for this kind of problem
how would you do that with AppSheet using Google.Sheet?

do you have sample ?

thanks

Hi there,

There is no multi-conditional search function is available for Appsheet for now, but we can achieve with few different workarounds. There are few ways, but let me introduce two options for you right here.

  1. Option 1 - Using Slice

To identity if the single orderline is actually ORDERED or not, I suggest you add one more physical field to orderline table. Any sort of column you can capture the fact the orderline is actually ordered or awaiting for order etc. And for this Orderline table, add new Virtual Column to pull the COUNTRY fields from its parent using dereference. The expression should be [CustomerID].[country] This gives the country name per orderline row.

Then go to Slice for this OrderLine table with expression of AND([country]=“France”, [OrderStatus]=“Waiting for Order”) depending on your naming to new physical column.

This slice only returns orderline where the country is France and order status is waiting for order.
Appsheet Slice is equivalent with SQL VIEW. you can make new virtual table with set of rows which meets the conditions you set out.

Then create the view whichever type you prefer, table view, deck view etc. out of this slice.

If you stick with color of YELLOW, you can place the conditional formatting as well. For instance, when rows in orderline table meets the condition of AND([country]=“France”, [OrderStatus]=“Waiting for Order”), then change the all the field text color to YELLOR for instance.

  1. Option 2 - Using interactive Dashboard

This is another workaround. You create the table for COUNTRY and make a ref connection and relationship with Customer Table, country column. This will generate new parent child relationship.
Also create the new table Order Status for example. It contains two rows, one is ORDERD, another row contains text WAITING. And make a ref relatoinship/connection with orderline table, with column of Order Status.

Create new dashboard with interactive mode.

Place 1) view made out of country master table 2) view made out of orderstatus master table. 3 ) view made out of orderline table to this dashboard.

Outcome? Country and Order status view will work as filter. For instance, you would have list of contry, UK, France US, and Japan etc. User select the country out of options and then it slice the rest of views.
At the same time, you can select either yes or no, or ordered or waiting for order in order status view, it will also rule out the rows in orderline views.

Once you get the knowledge of Appsheet, this is not a complex deal at all, but super easy and super basic operation in Appsheet.

Koichi

Hi

Option1
I agree with you concerning the Customer.country.
But concerning Product.color the request is to display Customer that haven’t ordered product that are yellow.
(it is not to display customer that are in a certain country and have ordered product of a certain color)

in a more technical way : (no so easy for me to explain)

if the user enter UK and Yellow (in a form screen)
I need to search for all the customers that are in UK, = CustomerList
I need to search all the products that are yellow, = ProductList
for each customerID in CustomerList
for each productid in ProductList
if (customerID + productid) doesnt exist in orderline
this customer is ok to be displayed

CustomerList and ProductList are array or temporary table

with AppMaker I’ve developed this javascript on the server side using Arrays.
but with AppSheet I’m not so good
how would you do that ?

Option2
in reality the user can enter many filters for the customer (not only the country) and many filters for the product (not only the color)
I will do that later when I know more about AppSheet

Stéphane

Probably I m not understanding your requirement 100 pct, but I believe interactive dashboard should work for you, although there will be bunch of other workaround available in other way around.

I created quick sample app for you.

This is what I can do the best.

Suggest you start with the basic app and learn appsheet one by one, that s the shortest way to get familiar with new tool.

https://www.appsheet.com/samples/Sample-App?appGuidString=b8fd5cc4-12b4-4c4f-b880-1a1c03308a87

Hi
Thanks you very much , I will look at this sample. I’ve to practice more AppSheet to understand the philosophy of this tool.

Do you know where I can find documentation about : how to organize the Apps and the data source for upgrade and deploy my application.
I found this : https://intercom.help/appsheet/en/articles/954431-app-upgrades

her is what I will do, can you tell me if it is the good direction, how you do that, and where I can find the best practice the documentation/community for this.

On the GoogleDrive I will create theses folders with dataSource inside (spreadsheet, workbook)
data_dev_1, data_test_1, data_prod_1
data_dev_2, data_test_2, data_prod_2
Dev is for development, test for test, prod is for production
1, 2 are the version, I create a version when I change the structure of a dataSource (new column, delete column, …)

is this the good way?

when the application is ready for prod, I duplicate AppName_test to AppName_prod
and I need to go in AppName_prod_1 to modify all the Tables sourcePath, all the link (attachement template, etc)

if I need to modify the structure (column) of the sheetSource,
I duplicate the dataSource_1 folder to dataSource_2
I add my column in sheet that is in dataSource_2
I duplicate App_1 to App_2,
in App_2 I modify the table.columns
etc …

I this the good way ? is it possible to have a global variable with the pathToSource, like that I change it only once ? where can I find the best practice on this topic?

thanks
stéphane

Hi there,

Basically, my quick answer is no. And good words for you is “Take it easy, as Appsheet will look after versioning as well for you.”

For instance, you create the app by connecting to your data source, i.e the google sheet, Appsheet will generate folder for your app within your google drive. This folder is your app particular.
Inside this folder, the image file, or other files will be saved, if you do so through the appsheet.

When you EDIT and save the change through the app, each time Appsheet give the incremental number as the version of your app. You can bring the app back to the old version as well, as Appsheet remember retain the record for the older version.

In terms of data schema, for instance, you add new column to the sheet table or delete one(s), then you go to table column view of that and “rearrange” the data schema. Appsheet will access to your data source and refresh the data structure, schema for you. At the time you have new version of the app.

It is difficult to define what is the prototype app or what is Production as far as Appsheet is concerned, as technically App is ready to deploy UPON GENERATION of new app… This is not a joke, the appsheet app is ready for production upon creation, as far as you are happy with app.

But practically we play around with App for development, adding new column, tables etc. But you dont need to do any thing on the backend side, rather than adding sheet to Google sheet (for new table) or add column within worksheet.

Is this addressing to your concern?

Hi
you should sleep at this time (in France it is the morning, and in US it is the night ) !
if a understand well your answer, this mean that AppSheet is the master for the datastructure.

i’m new with AppSheet and i thought that first you create your sourceDataSheet in GooglDrive, and then you create your App and connect to this GoogleSheet

I like more and more the AppSheet product!
I hope I will be able to create my POC App.

Thanks

Don’t worry I’m in JAPAN, haha.

As you start to use appsheet, you get addicted to it for sure. Enjoy life with Appsheet, It will be your game changer.

Is it possible Choose the product based on an entry? I basically want to add two rows but the value of one column of each row will vary and will be selected on a form. Is this possible with what you did?

Jervz
Participant III

This is awesome! i’ve been waiting for this for a long time!! Thank you so much!

GreenFlux
Participant V

@praveen, this is EXCELLENT! Thank you!

How would this work when you want to copy a different number of rows each time?

e.g. A Customer wants to repeat an Order, with all related Line Items. Each time they want to copy a different order, with a different number of line items- copied to the new parent Order.

Would we have to just guess at the max number of related rows (Line Items) per Order, and have that many individual actions grouped to copy them? How do we loop through the list of REF_ROWs when the number of values is not fixed?

Combine this new kind of action with a reference action.

In a reference action, you provide an xpression to compute a set of row references. And an action to take on each of those rows. If you invoke an action that creates a new row in another table (effectively a “copy”), then it achieves what you want, I think.

I tested this and working perfect. Thank you for implementing this awaited feature!

On the backend, API POST call should be running. POST methods could be relatively simple rather than Update row(s), but does your team have a plan and roadmap to introduce similar for the other verbs , especially, update!

… Including HTTP GET

Very true, thank you.

Great to hear, been waiting awhile for this feature!

Great, then i dont need to struggle with API and testing on Postman !!! This will save massive time.

Sean_Lim
Participant V

Love you guys! I’m a med student who doesn’t have much time to tinker with webhooks and this is going to make my med app dreams come true!

Thank you for that new feature.
I used a form with EnumList. After saving, every item in the EnumList gets a new row in the child table. That will help us speed up the processes.

@Fabian. Can you guide us through the steps how you did this please. This is awesome functionality
BTW how do you get the Enum to pop up after clicking in that Action?

@Henry_Scott
Use of LINKTOFORM deeplink with that action and activating “Advance Forms Automatically” property from UX > Options > Forms produces the result

@LeventK, thank you, let me try that out

@Fabian

I need this in my life - would you be able to do a quick video walk-through of how you set this up? Would be very grateful!