I have table called Resources that contains a field named "Name". 

There are a number of other fields but the relevant ones are a field named "Category" and field named "Project".

I want "Name" to be a calculated value.

If I use the following simple calculation it works fine:

(Right(Year ( Get(CurrentDate )); 2)) &"-"& (Month ( Get (CurrentDate ))) &"-"& Substitute ( Project ; " " ; "_" ) & "-RetargetingPixel"

This is the result:          21-02-Project_Name-RetargetingPixel

However, if I enclose the same calculation in an IF or CASE function, for example:

If ( Resource_Category = "Retargeting Pixel"; (Right(Year ( Get(CurrentDate )); 2)) &"-"& (Month ( Get (CurrentDate ))) &"-"& Substitute ( Project ; " " ; "_" ) & "-RetargetingPixel"; "Test" )

and choose "Retargeting Pixel" as the Resource Category the result is:     21-02--RetargetingPixel

What happened to my Project?

Actually. I may have figured this out — to a point.

It works fine if I'm in the actual Resources table. The problem occurs when a record is created in the Resources table via a portal in a related table.

Operationally, that's the way I'd like it to work but it could it be the Project field (also a calculated value) is not populated before the Name field calculation runs.

Is there a way of ensuring that calculation occurs first or would I be better off using a script to populate the Name field?

If it is a calculation field, then it will recalculate every time a referenced field is modified. The behavior you describe suggests that it is actually a text field with auto-entered calculated value. You could get it to update similar to a calculation field by unchecking the 'Do not replace existing value' option. But then your use of current date is probably not a good idea, because that too will change whenever one of the referenced fields is modified. Consider using a field that auto-enters the creation date instead.

 

You're correct. It is a text field with calculated value. This is because, for some categories, the user will enter the information. For other categories, I want it to be entered automatically. I have resolved the issue with a script. I see your point about creation date rather than current date and will adapt the script accordingly.

Thanks.

It still puzzles me why the calculation works when it is stand-alone but doesn't work when encased in an IF or CASE.

 

Edited February 26, 20214 yr by millmaine
spelling