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!
Solved! Go to Solution.
Sorry for the many reply’s but there’s a lot in this thread
From this status change, you can then trigger off all sort of automations.
Unique()
that list…eventually it will reduce to a single “Complete” value
and(
count(UNIQUE([Related Whatevers][Status]) - list("") = 1,
in("Complete", UNIQUE([Related Whatevers][Status]) - list(""))
)
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.
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.
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?
Thank you for your detailed reply and analysis of reference actions.
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.
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.
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.
My question is just: WHY?!
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.
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:
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
You are right, I was just joking about its oversimplification
Yes please!! Help me answer the WHY question pleeeeeeeaaaaase
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.
The privilege of knowing you is great my friend
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.
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.
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.
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).
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.
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:
Sorry for the many reply’s but there’s a lot in this thread
From this status change, you can then trigger off all sort of automations.
Unique()
that list…eventually it will reduce to a single “Complete” value
and(
count(UNIQUE([Related Whatevers][Status]) - list("") = 1,
in("Complete", UNIQUE([Related Whatevers][Status]) - list(""))
)
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
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
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |