Jump to content

Weird result with Calculated Value

Recommended Posts

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?

Link to post
Share on other sites

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.


Link to post
Share on other sites

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.


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 by millmaine
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.