Trying to auto enter Current Date in Date field based on Get (ModifiedFields)

[cbum]

Would appreciate help on what I thought was a simple problem:

I would like to auto-update a date field ("FinalOutcomeUpdate") any time a user changes the entry in a text field ("final_Outcome"). Something like using the Auto-enter Modification Date, but only triggered by changes in (a) specified field(s).

So far I tried variations of this calc, with no success:

Case ( IsEmpty (final_Outcome) ; "" ;

ContainsValue ( Get ( ModifiedFields ) ; 1 ; "final_Outcome" ) = 1 ;

FinalOutcomeUpdate = Get (CurrentDate) ;

FinalOutcomeUpdate )

 

Right now, this yield a "?" when modifying "final_Outcome", and entering a date into "FinalOutcomeUpdate" by hand is blocked.

not sure if this is a correct way to use the customF "ContainsValue", to evaluate the "Get ( ModifiedFields)" function. Any suggestions welcome.

Also, is there a way to define a "default result" for the case statement to be "do nothing"? (not "", since that blanks existing entries).

[comment]

1 hour ago, cbum said:

I would like to auto-update a date field ("FinalOutcomeUpdate") any time a user changes the entry in a text field ("final_Outcome").

Couldn't you do simply:

Let ( trigger = final_Outcome ;  Get ( CurrentDate ) )

This will re-evaluate anytime the final_Outcome field is modified and enter the current date.

 

1 hour ago, cbum said:

Also, is there a way to define a "default result" for the case statement to be "do nothing"? 

You can specify Self as the result for the case where you want to keep the existing value.

 

[cbum]

Thanks Comment.

I was considering triggers, but do they work in the context of another table layout? If so, this wold be perfect.

Thanks for the "Self" - is that relatively new?

 

c.

[comment]

11 minutes ago, cbum said:

I was considering triggers

I did not suggest using (script) triggers. I suggested using an auto-entered calculated value, where the calculation uses a variable named "trigger". The name itself is meaningless - you can use (almost) any name you like.

 

13 minutes ago, cbum said:

but do they work in the context of another table layout?

I am not sure what you mean by that. There is no mention of another table in your original question. All your attempts suggest that the two fields are in the same table. If that is so, the auto-enter calculation will be performed whenever the final_Outcome field in the same record is modified.

 

[cbum]

Thanks for the clarification - I did not pick up on that. I just tried it and it works great - thanks for that.

It also works fine if the field is modified on another layout.

24 minutes ago, comment said:

Let ( trigger = final_Outcome

Just trying to understand the mechanism here - if you define a variable as a field in a let statement, any modification in the field activates the autocalc after the semicolon?

Is that described somewhere?

 

Thanks again!

[comment]

50 minutes ago, cbum said:

if you define a variable as a field in a let statement, any modification in the field activates the autocalc after the semicolon?

A modification of any field referenced in a (stored) calculation will cause it to re-evaluate. Since the field is not required to produce the actual result, we just need to find a way to refer to it artificially. Defining a dummy variable is one such way, but any other method such as:

If ( final_Outcome ;  Get ( CurrentDate ) ; Get ( CurrentDate ) )

or:

Left ( final_Outcome ; 0 ) &  Get ( CurrentDate )

etc. would work just as well. Still, I believe the intention is much clearer when using the 'trigger' variable.