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 |