Updating dereferences

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

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??? :slight_smile:


AppSheet is amazing and has seriously removed barriers for everyday people to build software specific to their needs; I freakin love it! :nerd_face:

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. :slight_smile:

Hope it helps!

https://www.appsheet.com/samples/How-to-use-Actions-to-copy-a-signature?appGuidString=ce60ae1e-0154-4e2f-af1d-c424969aa17b

4 Likes

Me too :smiley:

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 :smiley:

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 :smiley:

1 Like

You’re welcome, and good luck!!!

Man, it really took a while but I managed to do what I needed, following your app :smiley: So thanks a lot for laying out and pointing it out :pray:

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.

1 Like

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. :nerd_face:

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?

1 Like

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

1 Like

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 :smiley:

You wrap the reference column, in the child record, in LIST().
LIST([Parent_Ref_Column])

If for example, if the column in this image WASN’T a list column, I would change it like this:

LIST([Line_Item_List])

Thank you mate. You helped me a lot with your sample app.

1 Like

I made a couple of guides that show how to do similar things; check out these guides:

  • Update Row App Formulas
  • Update Related Records From Parent Update
1 Like