You could combine this into an IF statement:
IF (UsePerferredName = "Yes" ; <<Use data in preferred name field>> ;
Case ( ….
Use the IF statement to decide to use the preferred name or run the case statement. Be sure to surround the close out the IF statement after the case statement.
Put the "UsePreferredName = 'Yes'" as the first statement in your case statement. Since case statements run until one row is true, it will stop if the global field is "Yes". Otherwise it will keep running.
Thank you Jeremy for your reply. Good evening/morning.
I've just tried that, but if the global is set to "Yes" and the preferred name field is empty, it does not pick up the Case statement and leaves the field blank, I need to calculation to proceed and look up the correct name based on the datatype. When it is not set to "Yes" it successfully picks up the right Datatype in the Case statement.
Here is the calculation as you suggested (If I have it correct!)
If (Use_Preferred_g = "Yes" ; SchoolName_Preferred ;
DataType = "Last, First (eg: Smith, Jane)" ; TextStyleAdd (Right ( SchoolName_LastCommaFirst ; Length ( SchoolName_LastCommaFirst ) - Position ( SchoolName_LastCommaFirst ; ", " ; 1 ; 1 ) - 1 ) ; Titlecase) ;
DataType = "First, Last (eg: Jane, Smith)" ; TextStyleAdd (Left ( SchoolName_FirstCommaLast ; Position ( SchoolName_FirstCommaLast; ", " ; 1 ; 1 ) - 1 ) ; Titlecase) ;
DataType = "First and Last name in separate columns" ; TextStyleAdd ( SchoolName_First ; Titlecase )
Do you have an idea what I am doing wrong?
Ah. I missed the "And the perferred name can't be empty".
Adjust the beginning of your IF statement to say :
If (Use_Preferred_g = "Yes" AND Not IsEmpty (SchoolName_Preferred) ; SchoolName_Preferred ;
You're a gem thank you Jeremy!
Thank you so much.
1 of 1 people found this helpful
Hi Paul –
here's a somewhat more structured approach to reading the desired name:
not IsEmpty ( "Use Preferred Name" ) ;
"Preferred First Name" ;
Let ( [
d = DataType ;
typeIsLF = d = "Last, First (eg: Smith, Jane)" ;
typeIsFL = d = "First, Last (eg: Jane, Smith)" ;
typeIsSep = d = "First and Last name in separate columns" ;
lastFirst = SchoolName_LastCommaFirst ;
firstLast = SchoolName_FirstCommaLast ;
GetValue ( Substitute ( lastFirst ; ", " ; ¶ ) ; 2 ) ;
GetValue ( Substitute ( firstLast ; ", " ; ¶ ) ; 1 ) ;
) // end inner Case()
TextStyleAdd ( theName ; Titlecase )
) // end Let()
) // end outer Case()
Erolst, thank you. Does such a structure improve the performance of the calculation, or is it better 'code' to run within just the Case calculation without the if?
Hi Paul –
Case() can do everything If() can, so I simply don't use If(); and if you ever want add more test/result pairs to Case(), you can just do that, while If() is limited to just one test, so you'd have to change it to Case() …
In this example, you could have used If() instead of the outer Case(), but not the inner one.
As to the performance: in a simple calc like this, the difference between your version and this one using Let() should not be noticeable (the story would be different if you reference related fields and/or function results over and over again …)
But I like to think that it increases the coder's performance (yours & mine …), because a) the calculation is clearer in regard to the test conditions, b) if you need to make changes, you can perform them at just a single place (.e.g. change a referenced field, or the formatting of the output), and c) it's easier to read overall.
Excellent, thank you, that makes sense.
And here I thought I already was being verbose …
Thanks for sharing.
It seems I never use a sentence when a few pages will do the trick. ;-)
Hi Erolst, how to I extend my formula in the same way:
age < 365 and sex = "Female" ; "Calf-Female" ;
age < 365 and sex = "Male" and Castration = "Yes" ; "Calf-Male" ;
age < 365 and sex = "Male" and Castration = "No" ; "Calf-Male" ;
age < 730 and sex = "Female" ; "Heifer" ;
age < 730 and sex = "Male" and Castration = "Yes"; "Steer/Tollie" ;
age < 730 and sex = "Male" and Castration = "No"; "Young Bull" ;
age ≥ 730 and sex = "Female" ; "Cow" ;
age ≥ 730 and sex = "Male" and Castration = "Yes" ; "Ox" ;
age ≥ 730 and sex = "Male" and Castration = "No" ; "Bull";)
to include Let CowHeifer = "Cow" or "Heifer" so that I will be able to use "CowHeifer" the same way that I would the others.