Hello,
Iโm seeing some problems when updating some columns to which there are dereferences โฆ
I hope Iโm gonna explain this right:
So there is a table called - Types of Projects,
a table called - Projects
Types of Projects have many Projects.
Then there is a table called Tasks and itโs rows are Tasks, each belonging to a Project
In the Tasks table there is a column - Projects - that is a reference column to the Table Projects. And there is a column called Types of Projects, that is a dereference to the Type of Project that Project is.
In a deck view of Tasks, I have a show if condition, to show only the Tasks belonging to a certain Type of Project. So the point is when I edit the Project to a different Type of Project, all of its Tasks will not show up in this view anymore.
But the problem is that when I edit a Project and change itโs Type of Project, in the Tasks Table I can see that there is no change on the Type of Projects column, so they still show up.
Shouldnโt they update automatically through the dereference?
What I notice is that, if I do the change, and then open the edit form for the Task and just close it again, without changing anything but hitting the Save button, the Type of Project updates โฆ but that would be really โฆ annoying to have to do. There must be a better way or something Iโm missing.
Any ideas what I forgot to click?
Thank you,
Sorin
Solved! Go to Solution.
I donโt know how many times Iโve heard from clients, โShouldnโt it just automagically doโฆโ
Then I have to gently remind them: computers and software are stupid, you have to tell them to do things (and how), they donโt just do things on their own; if we didnโt create a way of updating records based on the edit of another recordโฆ why should it automagically work???
AppSheet is amazing and has seriously removed barriers for everyday people to build software specific to their needs; I freakin love it!
Many years ago when references were first introduced, there was discussion about this very topic: updating child records automatically.
The official statement, if I remember it correctly, was something along the lines of,
โWe [AppSheet] donโt want to make something that will start affecting or changing an appโs data automatically without the app user or developer knowing about it.โ
Something like that; they didnโt want to make something hard-coded that would always do that because there are times when you DONโT want that.
There are methods you can do to accomplish what youโre wanting; I recently made a sample app showing how you can take a signature, timestamp, and user email from the parent and copy it into all the related child records - using automatic background actions so no one knows whatโs going on and it just happens.
Hope it helps!
I donโt know how many times Iโve heard from clients, โShouldnโt it just automagically doโฆโ
Then I have to gently remind them: computers and software are stupid, you have to tell them to do things (and how), they donโt just do things on their own; if we didnโt create a way of updating records based on the edit of another recordโฆ why should it automagically work???
AppSheet is amazing and has seriously removed barriers for everyday people to build software specific to their needs; I freakin love it!
Many years ago when references were first introduced, there was discussion about this very topic: updating child records automatically.
The official statement, if I remember it correctly, was something along the lines of,
โWe [AppSheet] donโt want to make something that will start affecting or changing an appโs data automatically without the app user or developer knowing about it.โ
Something like that; they didnโt want to make something hard-coded that would always do that because there are times when you DONโT want that.
There are methods you can do to accomplish what youโre wanting; I recently made a sample app showing how you can take a signature, timestamp, and user email from the parent and copy it into all the related child records - using automatic background actions so no one knows whatโs going on and it just happens.
Hope it helps!
Me too
Definetly, it shouldnโt change things unless you want it to, great decision. I was expecting it to because I did make a link directly between them so I thought it would update everywhere it is referenced or dereferenced. But if thereโs a way to set it up, thatโs great.
So as I see from your app, thereโs a lot more I need to learn about actions and what they can do because it does what I need it to do, which is update the Project category in the Task table where that Project is referenced.
So thatโs great study material
If I get stuck, Iโll ask here and if I donโt, Iโll make an explanation of how I made it work in case somebody else searches for this.
Thank you
Youโre welcome, and good luck!!!
Man, it really took a while but I managed to do what I needed, following your app So thanks a lot for laying out and pointing it out
But I have to say, I see your point and itโs a smart decision to have users build their automatic update mechanism, because itโs better that we figure out what we want by trial and error, rather then have the app do something automatic and in some cases unexpected and not really be able to figure out how to stop it.
BUT, when you reference two tables apart, I would expect there to be a button letting me do this connection directly with just one click. I hoped the IsPartOf would do just that but itโs not meant for that, however, another option like that when defining the reference would be great and used. It would just these system generated buttons build and have the connection between the tables set up so that any change in one is updated also in all itโs children no matter how far it is brenched out, as long as the user is opting for that option.
Would you think that is a sane and implementable request?
Again, really thanks.
I couldnโt have made my app useful without building these links that I learned from your app.
Thanks @sorin_mihai for the kind words, Iโm always happy to see the resources Iโm putting out there helping people achieve their apps.
I agree, and this is a very good idea for a feature request for a system generated action to appear when you set the โIsAPartOfโ to true for a reference - allowing you to cause a recalculation of all the related records.
@Aleksi Your thoughts?
Hey,
I have another question on these lines.
So I see now how to update columns on related records of the children when saving the Parent Form.
But how can I update a specific non-list column on the record of the parent when saving a Child Form?
So every Task has Times added to it (2 hours, 3 hours etc.)
(Tasks Tabke is the Parent and has a virtual column of Related Times, whilst the TImes Table has a reference column to the Tasks Table)
And I make a column in the Tasks table to Sum all the related Times added to that Task:
(Call this column - Task - Sum of Times)
sum([Related Times][Times - Hours])
And the sum works BUT it doesnโt actualize when I add a new Time. After adding the Time, I have to go to the Task, hit the Edit button and Save and only then, the sum actualizes.
Of course, I want it to actualize when I hit the Save button of the Add Times Form.
Any idea how I can do that?
(I canโt through the same method)
Thanks,
Sorin
Youโll need to create an action to update the sum, then you can place this action in the child formโs save event or in a workflow (but a workflow has a sleight time delay before itโs โseenโ in the app, though itโs actually updated you just need to sync for the workflow update to be reflected in the app).
But thatโs the thing, I canโt place the action in the childs form save, because the action option is to Execute an action to a set of rows, and it wants me to put in a List column but I have a Sum column (which is a decimal column) in the Parent โฆ I donโt have a list to relate to that sum column in the Child โฆ
So I can do the first part - the action to update the sum in the Tasks Table, but I donโt know how to activate it for the Task when I hit the Save button from the Form of the Add New Time in the Times table.
How do I do this?
My bad, I forgot the ref action to initiate the parent update action.
You need to create a ref action on the child table, this calls the update action on the parent table to run it on a set of rows:
In this sample, you can see the actual action lives on the Invoice_Payments table, but it updates rows from the Line_Items table.
Weโre in the same problem.
The reference action MUST reference a List Column from the parent.
In my case this would Update the list of Referenced Times (since I added a new one). But how do I activate the Action that updates the Sum of Times column from a Task (which is a decimal column) when a new Time is Added?
Well @MultiTech_Visions, I went and solved it through the spreadsheet. I got the formula to search and sumif in the actual spreadsheet on the Tasks table, and that updates on refresh.
And it works.
I couldnโt do it with the actions because I donโt see how to activate a non-list type column action from a child โฆ maybe Iโm not explaining it right or not following your advice right, but itโs not working. Iโll leave it like that for now and move on, and Iโll be back on it later to solve it through Appsheet only
Thank you mate. You helped me a lot with your sample app.
I made a couple of guides that show how to do similar things; check out these guides:
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |