Actions to update other tables. Why not how

[Joseph_Seddik]

Hello,

I would like to know please whether there is a reason for which we can “Add rows to another table using values from this row”, but we can NOT "Update rows of another table … "

Instead of having to use complex (and in some cases non-reliable) lookup operations, I was wondering why is AppSheet missing such action?

Personally, I use a personal method based on custom keys to locate values, I’ve read several older topics regarding the subject so I’m not facing an issue and thanks to the community I know how it can typically be done. I just couldn’t find the reason for making this feature unavailable and I’m really interested in knowing why.

Thank you!

[Suvrutt_Gurjar]

KJS:

but we can NOT " Update rows of another table … "

Could you take a look at the reference actions demonstrated in the sample app below? Reference action can edit/delete “referenced” rows in another table.

AppSheet

AppSheet : mobile apps from spreadsheets : Template: for the industry

This app shows how to use reference actions. Reference actions are used to modify a row in one table via an action defined on another table. In this example, we have a Parent table and a Child table. The Parent table has an action called "+1 All"...

The relevant community posts are in the link below

https://community.appsheet.com/search?q=reference%20actions%20%23ask

You may wish to elaborate if you are looking for something else.

[Joseph_Seddik]

Thanks dear Suvrutt as always. I have twenty-something reference actions in my app without knowing they have a name… now I know

Regarding the sample app, I had to ask myself why would I do all of this just to do a “+1”, and why would I want to perform a unified action on all rows of a table indiscriminately it becomes redundant.

I need to be able to pass states changes between tables that may or may not be related to each other.

Maybe I’m wrong, and I’d appreciate your correction, but the way I see AppSheet handling this communication is the following. Imagine this conversation between a taxi driver and his dispatcher please. The dispatcher calls the driver:

• Dispatcher: I have received a service-trigger phone call and I need you to launch the action to go pick up a client from Acme Hotel and drop him at the airport.

• Driver: Sure, please give me the client’s name.

• Dispatcher: No. I have the client’s name in front of me but I can’t give it to you. You have to figure that out by yourself.

• Driver: and how would I do that?!

• Dispatcher: You should call the Hotel’s reception and ask them: Who was the last client who called for a taxi, whose check-out date is today and has 4 large suitcases.

An example of one of my use cases concerning only one column in the row:

• I have a table of Project with a DateTime column named: Estimated Time of Completion (ETC) and another named Actual Time of Completion (ATC). A project can have many Tasks running independently of each other.

• The Tasks table has a “Due Date” and “Fulfilment Date (FD)” columns. A task can have many Activities running sequentially and/or in parallel.

• All states of “Tasks” and “Projects” are controlled only by the status of relevant Activities.

• When the last Activity of a certain task has been marked as “Complete”, I need to update the parent Task’s FD with the completion date of it’s last action.

• Similarly, when the last Task of a Project is “Fulfilled”, I need to update to the parent Project ATC accordingly.

How can this be done please?

• (Update: sorry, thanks to you all, I know how it can be done. I meant in a way other than the dispatcher-driver scenario)

[Suvrutt_Gurjar]

Thank you for your detailed reply and analysis of reference actions.

KJS:

Regarding the sample app, I had to ask myself why would I do all of this just to do a "+1"and why would I want to perform a unified action on all rows of a table indiscriminately it becomes redundant.

Please always evaluate sample apps available at option Customer Support - AppSheet as mostly just AppSheet feature demonstrating apps. So most of those sample apps may not be ideal to relate to real world practical business scenarios. Those are much simplified feature demo. apps.

For business sceanrio apps , the option is App templates . However even these are more simplified versions of real world business scenarios. I believe all these apps are for new app creators to get them a few useful quick references to build further.

KJS:

An example of one of my use cases concerning only one column in the row:

Your use case essentially seems to be updating a parent table record value based on one or more child records. If so, it can be done with reference actions.

In the referenced table setting of the action settings you name the “parent table” and referenced rows can be defined as something like SELECT(Parent Table[Parent Table ID], IN([_THISROW].[Child Table ID], [Related Children Table IDs]))

If you need further help on this please feel free to share more details of column structures and we can construct the action , expressions.

KJS:

I’d appreciate your correction, but the way I see AppSheet handling this communication is the following

Nice one. Generally it seems to be in right direction. However, I did not get the below statement. Anyway, as long as your understanding analogy and understanding is correct, I believe it is great.

KJS:

Dispatcher : No. I have the client’s name in front of me but I can’t give it to you. You have to figure that out by your self.

[Joseph_Seddik]

My question is just: WHY?!

Scenario 1:

I have Table A: when a row_of_ A is added with some column values say (1, 2, 30, 40), AppSheet gives the possibility to trigger the action to add a new row to Table B and directly set the desired values of the newly-added row_of_B to equate row_of_A’s values of (1, 2, 30, 40). All is done with one simple, direct action.

Scenario 2:

HOWEVER, if the desired row_of_B already exists, I cannot do the same and pass row_of_A’s values of (1, 2, 30, 40) which I already have in front of me but cannot pass. Instead, I have to do the following:

1. create an action and associate it with Table A, just to select the desired row_of_B, and launch a blind action on Table B
2. create the blind action associated with Table B, that will have to:
   1. Figure-out what row_of_A should be read, using complex expensive expressions that in some cases would also depend on the unreliable _RowNumber.
   2. Retrieve the desired values, the same (1, 2, 30, 40) that we had all the time and update row_of_B.

The question is: “WHY?”

Why do we have:

AND:

but something like this is made unavailable to users:

Data: set the values of some columns of a row in another table using values from this row

I can see no sense at all and I feel I’m getting crazy, or An answer is much appreciated.

---

To dear @Suvrutt_Gurjar

Suvrutt_Gurjar:

Please always evaluate sample apps

You are right, I was just joking about its oversimplification

Suvrutt_Gurjar:

If you need further help

Yes please!! Help me answer the WHY question pleeeeeeeaaaaase

Suvrutt_Gurjar:

I did not get the below statement.

It is just an analogy to the case I ask you to do something using some information I possess, however, I don’t give it to you and force you to look for it yourself incurring costs in the process.

Suvrutt_Gurjar:

I believe it is great

The privilege of knowing you is great my friend

[Suvrutt_Gurjar]

KJS:

Yes please!! Help me answer the WHY question pleeeeeeeaaaaase

Nice query.

I get your point of difference between two scenarios. In the first scenario, while adding new row ,
A) it does not need to search for an existing key out of several existing in the other table.
B) Also the first scenario is just to add a row.

In the second scenario
A)it has to search the keys of rows to apply the action among several existing to change
B) And the more important is second action can be even delete the related rows apart from editing them. So you can assign more than one type of actions as a second action in scenario 2.

KJS:

I can see no sense at all and I feel I’m getting crazy, or both An answer is much appreciated.

I think this question is more appropriate for answer by AppSheet team.
Maybe someone in the community has more insights.

Also you could possibly suggest it as a feature request. AppSheet team is receptive and I have seen this platform continuously evolving for the better in last 5.5 years of my association with it.

KJS:

It is just an analogy to the case I ask you to do something using some information I possess, however, I don’t give it to you and force you to look for it yourself incurring costs in the process.

I of course understood the hint you were giving in the anology. However just wanted to know where that exact issue you were facing. You explained it very well in the second post.

[MultiTech]

KJS:

Scenario 2:

HOWEVER, if the desired row_of_B already exists, I cannot do the same and pass row_of_A ’s values of (1, 2, 30, 40) which I already have in front of me but cannot pass . Instead, I have to do the following:

1. create an action and associate it with Table A , just to select the desired row_of_B , and launch a blind action on Table B
2. create the blind action associated with Table B , that will have to:
3. Figure-out what row_of_A should be read, using complex expensive expressions that in some cases would also depend on the unreliable _RowNumber.
4. Retrieve the desired values, the same (1, 2, 30, 40) that we had all the time and update row_of_B .

Actually you can use the same method from before to update records, you just have to pass it an ID

• and make sure that you set every column to itself (except) for the one’s you wish to change.

In this way, a data action that “creates a row in another table” can be used similarly to how you would use the API to update the record(s).

• This is a very easy way to mess up your data if you’re not careful… perhaps that’s why it’s not talked about too much.

---

FYI: if you’re doing this:

KJS:

using complex expensive expressions that in some cases would also depend on the unreliable _RowNumber.

You’re doing something wrong.

• [_RowNumber] should never come into play for… pretty much anything.
• I get what you’re saying, you’ve gotta lookup the record - but using row number is not the way.
  • The way I go about this is to physically flag the record by putting a temporary variable inside an extra column I have (specifically for this purpose) in all my tables.
  • I create a slice that’s watching for that variable, which when filled populates that slice with a single record
  • From that slice, I can easily INDEX() whatever I need out of it.
  • When I’m done, I clear the flag and everything resets for use again.

I’ve used this method to copy parent-child-grandchild data from one set of tables to another, having to flag the parent, then the child, in a systematic way when generating all the data.

• And it all kicks off without a hitch, and most of the time you don’t even notice so much has happened in the app.

[JMVF]

Yes, agree, it makes no sense. I've been scouring the "help" pages for hours for this seemingly simple workflow that is made extremely complicated and unreliable. Trying to move over from Podio where updating a table from another is baked in the automation cake. No loosy goosy lookup or selects, or relying on virtual columns, just a simple value from here goes there.

Example from Podio:

[MultiTech · Accepted solution]

Sorry for the many reply’s but there’s a lot in this thread

KJS:

• Similarly, when the last Task of a Project is “Fulfilled”, I need to update to the parent Project ATC accordingly.

One way you might go about this…

• There could be a virtual field on the Parent, that’s watching the child data, waiting to change a status value
  • I typically call these [Something_Auto_Status]
• This column watched all the related columns, and depending on the data there - changes it’s value from one status to another.

From this status change, you can then trigger off all sort of automations.

But what I would do in this scenario:

• Have an [Auto_Status] column watching the [Status] field of the child data
  • The idea being, there will be a bunch of statuses from the tasks - but eventually they will all say “Complete”
  • So if I generate a list of all the status values from the children, then Unique() that list…eventually it will reduce to a single “Complete” value
    • At which point the project is complete

and(
  count(UNIQUE([Related Whatevers][Status]) - list("") = 1,
  in("Complete", UNIQUE([Related Whatevers][Status]) - list(""))
)

• The fields for the [Project_Completion_Time] could then be a “ChangeTimestamp” column type
  • Watching the [Auto_Status] column
  • Triggering off the “Complete” value

[Joseph_Seddik]

MultiTech_Visions:

Sorry for the many reply’s but there’s a lot in this thread

Please don’t be keep them coming Please!

I’ve seen there’s a wealth of helpful information that is new for me to learn, but I’m in the middle of something so I preferred studying your replies later today in order to give them their due attention.

In the mean time please reply as much as you like, I sincerely appreciate every one of them

[Joseph_Seddik]

@MultiTech_Visions

Dear Matt, I’m happy

I’m happy the day I finally got the chance to carefully read your replies coincided with your birthday. So my friend HAPPY BIRTHDAY I wish you all success, health and happiness for your and your loved ones!

And I’m happy that I’ve got to know your name

I sincerely enjoyed reading every one of your replies, watching the videos for their contents and your amazing humour and subscribing to your channel

This is really brilliant, creative, well explained, and the last reply about the status updates was the crème de la crème. You left me no opportunity to ask any questions

Much appreciated Matt! Thanks a million!
KJ

[MultiTech]

This warmed my heart; thank you very much!

[MultiTech]

KJS:

I was wondering why is AppSheet missing such action?

• Around the time when actions were first introduced, the method below for updating records was introduced into the community:
  Quick Update System - How to update records in your app with a push of a button
• Since that’s such a clear and simple solution, I don’t think the devs ever gave this a second thought.
  • When I think back to the days when @Praveen was largely active in the community, he would regularly suggest people go this route (using the quick update system)