Re: New Action: Add a Row to a table - Page 2

pravse · 08-12-2019 10:15 PM

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 = [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.
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.

ashishsin9h

can u please share the process for adding multiple rows as shown above or sample appsheet app. i am a newbie for appsheet.

MultiTech

Hmmm… It seems that some initial values are not being calculated when you create a record via this new mech.

App formulas are calculated, the columns that are set with the data action are set - but all initial values aren’t evaluated, some are simply missed.

@praveen
App: TTGamma-96620
Version: 5.102072
Table: Line_Items
Columns: LineItem_Title, LineItem_Rate

Action (on the Invoices table) that creates the Line_Item row:
Create | Auto Invoicing Item

When pressing that button, there’s an initial value formula to set the title - which is dependent on values set by the action - but you can see there’s nothing being written.

The action creating the record set’s what is being created, then initial value formulas pick that up and set titles, descriptions, hourly rates, etc. based off that data; but it seems that any value written to the record by this feature isn’t “seen” by the initial value formulas.

Yayasan_TL_BP

Hello…

can you explain about the view model you use ?

thank you

MultiTech

@Yayasan_TL_BP that’s an interactive dashboard:

I’ve got an inline list on the left
and the detail view on the right.

Yayasan_TL_BP

Yuppsss

Thank you …

MultiTech

Upon further investigation I think I’ve nailed down what’s going on. @praveen

It seems that values set by this new action are not available to initial value formulas.

Let me explain my testing setup: I’ve got an action to make a new row like this:

where the week number and year are populated by the action.

I’ve got another column that’s a concatenation of these values

To give us a nice looking label like:

Week 34 (2019)

When I run this action, this is what I get for my initial value:

The numbers supplied by the action are not included in the initial value formula result.

But when I change the year formula so it’s hard-coded to 2019

That value is included in the initial value concatenation - but the value set by the action (the week number) isn’t.

MultiTech

@praveen any news on this?

Fabian_Weller

Sorry for my late response.
Because of my bad English, I was not brave enough to make a video
I just did a sample App to show how to turn an Enum into Children.

https://www.appsheet.com/samples/Sample-App?appGuidString=485bc2e2-f1b8-4357-b822-50c27ef99b99

There are 2 ways:

In the Table “Standard Items” you can select 5 Items and the Amount.
When you create an Order you define also the Category.
Then you can click on the Action “Standard Items” and it will add the Items you selected in the Table.
PRO: You can define the Amount.
CONTRA: You have to pre-define the items.
After you created an Order you can click on the Action “Enum”. This will open a Form. This is just a Helper Table. You can select max 10 Items. After selecting the items, the Form gets saved because it is Auto save. The Event Action of this Form is “Enum Items” which is an Action that groups 10 Actions: Adding all selected Items als Children.
PRO: You can select the items.
CONTRA: The Amount is always the same.

Jon_S

Thanks @Fabian this is excellent!

Checking it out now

Jon_S

Hey Fabian

This solution is really nice! It works great!

There’s a slight drawback in the fact that the selection of items is limited to the amount of “actions” you’ve got in the back end (the ones that are part of that grouped “Enum-list” action)

It will obviously be annoying for the users of the app to refer to their developer every time they add a extra grouped item.

Other than that - It’s a very nice approach!

Fabian_Weller

@Jon_S You are right. It’s up to you how many actions you want to group. 10, 100, 1000

Jervz

Thanks for your great work Fabian!

Henry_Scott

What revision did you apply to Fabian’s solution?

Jervz

I just fixed the 2nd way which the amount of each item is always the same.

Edit: Not fixed…i misunderstood the functionality

Henry_Scott

So it is more dynamic based on a value and not the same hardcoded value?

Fabian_Weller

Thank you @Jervz. It’s awesome how enthusiastic this community is working together.

Your solution is working for stable lists. But if you use the EnumList for a dynamic list, you cannot determine the amount. Like:
LIST(All Standard Items) + (LIST(All Spare Parts to this machine) - LIST(Items that are already added manually)

Mikko_Palmu

Hi, for some reason the method 2 with enum list column doesn’t work in my app.

I have action in Table A where I’m triggering "execute an action on a set of rows” in Table B.
Action executed in Table B is the “Enum item” group action which includes the “Enum item1” to “Enum item5” actions that ultimately should create rows to Table C. Table B already has EnumListColumn that contains up to 5 items.
Each row in Table C should consist of [Key], [Column1] and INDEX([EnumListColumn],1…5), the same way your app creates as many rows as there are items in the enumlist column

I have the same criteria ISNOTBLANK(INDEX([EnumListColumn], i ) ) in “Only if this condition is true” for each “Enum item i” action. It creates the first row ok, but after that it just creates 4 empty rows with [Key] populated, no matter how many items is in enum, and also omits the static Column1 from the rows.

any idea why this happens?

Fabian_Weller

Hi @Mikko_Palmu. Thank you for reaching out. There might be different reasons. Would you mind sharing the app to me as a Co Author? And also the Google Sheet.
wapplabor@gmail.com
You can also make a copy of the App and share the Copy. So I could look into it.

Mikko_Palmu

Hi @Fabian, I’ll make a copy of the app and try cleaning it a bit, translate the important table/column names to english and share it with you

Mikko_Palmu

Hi @Fabian

I added you as co-author and shared the Google sheet. I appreciate if you have time to check it!

Fabian_Weller

Hi @Mikko_Palmu thank you for sharing your App.
I’ve checked it and I would say that it should work.
Therefore please send an E-Mail to support@appsheet.com and if you can, please add a short Video of the steps.
For me it looks like a bug.

Marc_Dillon

Has anyone used this to auto create a parent record with several child records? I have only thought it through so far, without any testing, but I’m stuck on how to save the parent’s new key value, a uniqueid(), in order to assign it to each child record.

Jervz

Not sure if this is the one you are trying to achieve but hope it helps

https://www.appsheet.com/samples/Sample-App?appGuidString=d25da694-02b8-4b3d-9a49-911a0677ea8d

Fabian_Weller

@Jervz This is awesome thank you!

Suvrutt_Gurjar

Thank you @Jervz for this practical extension of the already very useful action of “add a row to a table” added by @praveen and AppSheet team.

Marc_Dillon

Sorry for the super late response, guess I missed this, was just randomly reading this thread again and saw this. @Jervz @praveen

Yes @Jervz that’s almost exactly what I planned to do as well! Using the linktoform() at the end for the parent record is a great way to circumvent the issue in the following post…
@praveen actually you can’t do that because of what I’ve brought up here: 'Data: add new row...' does not allow manually inputted key

pravse

@Marc_Dillon if you want the action to add a parent row and a set of children rows at one shot, you would need to be able to get back the reference to the parent row added, and then run an action on that. We don’t have this mechanism yet. But we know we need it. If you give a mouse a cookie … :]

Marc_Dillon

Fair enough.

Although I think the key value could be generated in the table from which the action is run, which could be used to populate the parent’s key, and the child’s ref.

pravse

@Jervz that is very smart! Using the KeyGen field to store a temporary key value. Actually, I think you don’t need to open the form to create the Parent row. You could create that automatically via an action too. So then your composite action would be: DefineKey, CreateParent, CreateChild1, CreateChild2, etc…

Markus_Malessa

@praveen would there be any way to add the functionality to just generate a temporary key value for a process such as this rather than needing an additional column to hold this temporary key in? I know this would be more similar to assigning a variable, which AppSheet might not be as happy with, but for those of us working with SQL databases it does not make much sense to add a column to a table to hold a variable for a process. I suppose the alternative would be allowing to use the ‘set a value in this row’ action but on a virtual column vs a fixed column. Just speaking my mind here and hoping that some of these suggestions might be an alternate way of handling things like this.

Jervz

@MultiTech_Visions just simply move
CONCATENATE("WEEK “, [Week_Number], " (” , [Week_YEAR], “)”)
from INITIAL VALUE to FORMULA

MultiTech

But what if I the formula needs to be evaluated only once, and that’s upon creation???

The actual formula is moot, the error in functionality is what I’m concerned about.

Jervz

@MultiTech_Visions Im not good at explaining things but i made a test app that maybe can help you… cheers

EDIT: MultiTech_Visions is right, thanks for explaining about the bug

MultiTech

I appreciate the attempt, but this is ultimately a bug with the new feature.

Initial value formula should evaluate themselves for the duration of the initial edit, even if the formula is using values from other columns in the same (or other) table, the formula should evaluate itself continuously during the creation of the record; it’s only after you physically edit the field with the initial value that the formula stops evaluating and instead the field becomes a manual field.

You can read more about initial value formulas and their specifics here:

(^_^)

Grant_Stead

I agree with you.

pravse

Agree too. We’ve looked into it. Might take a bit of time to fix — we have to fix consistently in both the app itself and in the workflow rule invocations.

Jervz

@MultiTech_Visions i get it now, sorry for being quite a bit slow lol
thanks for letting us know about the bug and hope they could fix it too.

MultiTech

No worries @Jervz! That’s the spirit of this community, we’re all here to help each other. (^_^)

Kirk_Masden

Great!!! I’m super happy to have this and find it very easy to implement. The only slightly negative comment I would make is that

add a new row to another table using values from this row

is slightly misleading. The “using values from this row” phrase gives the impression of a limitation that doesn’t exist. When I first saw that phrase I was afraid that I might not be able to pull data from other parts of my app and collate it freely. If fact, however, I have found that I can use select() and lookup() expressions quite freely to bring data from other parts of the app together. Moreover, I don’t need to make virtual columns that might reduce the efficiency of the app; I can do everything I need to with expressions in the “Set these columns” part of the action. So, perhaps

add a new row to another table and populate with data

or

add a new row to another table and designate values

might do a better job of representing the flexibility of this GREAT FEATURE!!

One more point:

I’m triggering this action with an “execute and action on a set of rows” action on a separate table. None of the data that I want to write with the “Add a Row to a table” action is specific to any single row; any old row within that table will do because I’m using select expressions to grab what I need from the relevant rows. So, here’s the expression that I put in this “Referenced rows” part of the “execute and action on a set of rows” action:

list(any(Select(Target table[Key],([Key]=[Key]))))

This randomly picks one row for me and that does the trick.

Thanks again for developing this very valuable feature!!

Kirk_Masden

Here’s a little followup to my own post:

One restriction that I need to avoid is expressions that access data in other virtual columns, which may need to be recalculated just before I use this feature to archive the statistics on another page. What I found is that if I use expressions that access rather simple data directly, all of my calculations are done correctly.