how to change values of a coloumn of preceeding rows of a client on edit of first row

[wabukhari]

I have a table in app sheet wth the following structure:
Job ID(Key), Client ID, Name, Visit No, Visit Schedule Date.
there are multiple rows for each Client ID with different visit schedule date values.

The total period of service is defined by two columns, First visit date and End visit date of another table called Contracts. Now I need : on edit "visit schedule date" of a specific client for visit no1, the value of visit schedule date of preceeding rows of same client should auotmatically be changed.
how can we do it in app sheet?

[lynchk21 · Accepted solution]

You need a data action to set the values of a row whatever formula you are using to calculate the future "visit schedule dates". Then you need a data action to execute an action on a set of rows, which you will need to define with an expression that selects rows that have the same Client ID, and then you can have a bot that runs the second action anytime an update is made were [_thisRow_Before].[visit schedule date]<>[_thisRow_after].[visit schedule date].

[lynchk21 · Accepted solution]

You need a data action to set the values of a row whatever formula you are using to calculate the future "visit schedule dates". Then you need a data action to execute an action on a set of rows, which you will need to define with an expression that selects rows that have the same Client ID, and then you can have a bot that runs the second action anytime an update is made were [_thisRow_Before].[visit schedule date]<>[_thisRow_after].[visit schedule date].

[wabukhari · Accepted solution]

Dear @lynchk21 
Hi
I have tried the following expression for calculating the next visit date:
[_THISROW].[Visit Schedule Date]+(select(Contracts[End Visit Date],[Client ID]=[_
THISROW].[Cient ID])-[_THISROW].[Visit Schedule Date])/(Select(Contracts[No of Visits],[Client ID]=[_THISROW].[Client ID])-[_THISROW].[Visit No]). The following error appears. Can you guide what I am doing wrong?