automatism that recalculates the last date

[bolognesiedalla]

Hello everybody
I am trying to set up this automatism, but I am not clear if it is the best procedure and how to complete it.

I have a ATTIVITA table, child of a TRATTATIVE table (each negotiation can have many activities). I would like to make an automatism that every time a row of the ATTIVITA table is added, updated or deleted, it recalculates the TRATTATIVE[PROSSIMA_ATTIVITA_IL] column (date field).

The value to be taken would be the higher date entered in ATTIVITA[RISENTIRE_IL] of the activities with [TRATTATIVA_LINK] = [ID_TRATTATIVA].

these are the tables.

 i try this way, but my biggest problem is when i have to give a condition to filter the rows. 

maybe I'm a little confused about the process

[TeeSee1 · Accepted solution]

If you are OK to use a virtual column you can simply obtain the latest date from the related child records of a parent by

INDEX(
 SORT(
  [Related ATTIVITA][RISENTIRE_IL], 
  TRUE
 ),
 1
)

[TeeSee1 · Accepted solution]

If you are OK to use a virtual column you can simply obtain the latest date from the related child records of a parent by

INDEX(
 SORT(
  [Related ATTIVITA][RISENTIRE_IL], 
  TRUE
 ),
 1
)

[bolognesiedalla]

yes but my problem is that it should be recalculated every time any record in the activity table is updated, added or deleted. and I think a virtual column is not enough. or am I wrong?

[TeeSee1]

The col should be updated as you make changes to the child records.

Try it. It's simple to implement and test.

[Suvrutt_Gurjar · Accepted solution]

I believe your expression for the referenced rows will be LIST([TRATTATIVA_LINK] )

You will also need to add a data change action called say "Change PROSSIMA_ATTIVITA_IL" as a referenced action as shown below.

The expression of this data change action ( type: change values of some columns in this row) that will change the [PROSSIMA_ATTIVITA_IL] column in TRATTATIVA table will be something like MAXROW("ATTIVITA", "RISENTIRE_IL", [_THISROW].[TRATTATIVA_LINK] = [ID_TRATTATIVA])

[bolognesiedalla]

thanks I try now !!

[Suvrutt_Gurjar]

@bolognesiedalla , if you do not want to store the date value in a real column and are just looking for showing the value in views, @TeeSee1 's solution is a simple one.