Auto-Enter Calculation to extract text of varying length found between two characters.

[asterix]

Hello to you all.
I am looking for an Auto-Enter Calculation to extract text of varying length found between two characters.
I have two text fields:
The first field is named [FileName] and contains image file names e.g. "NIK000223_hab-ven.jpg". The second field is named [ImageType].
Now, what I need is to extract from filed [FileName] and insert to field [ImageType], the any given text that is found between the underscore character “_” and the period character “.”
I will greatly appreciate any help.
Thank in advance.

[comment]

Try the following formula:

Let ( [
start = Position ( FileName ; "_" ; 1 ; 1 ) + 1 ;
end = Position ( FileName ; "." ; start ; 1 )
] ;
Middle ( FileName ; start ; end - start )
)

Why do you need this to be an auto-entered calculation? Why not simply use a calculation field?

[asterix]

Oh! Perfect, works like a dream.
Thank you very much. I truly appreciate your help, you saved my day. I thought the solution was a Let calculation which so far I haven't managed to master.
Thanks again.
No any particular reason really, it could just as well be a calculation field. Does it matter? 

[comment]

41 minutes ago, asterix said:

I thought the solution was a Let calculation

Not really. The Let() function doesn't actually do anything except allow you to make your calculation more readable and - most importantly - avoid repeated calculations of the same thing. In this case, the start position is used for calculating both the end position and the length of the text to extract. Without using Let(), the calculation would have to be:

Middle ( 
FileName ; 
Position ( FileName ; "_" ; 1 ; 1 ) + 1 ; 
Position ( FileName ; "." ; Position ( FileName ; "_" ; 1 ; 1 ) + 1 ; 1 ) 
- ( Position ( FileName ; "_" ; 1 ; 1 ) + 1 )
)

which is both difficult to read and wasteful as it calculates the same thing 3 times.

 

41 minutes ago, asterix said:

it could just as well be a calculation field. Does it matter? 

In this case, not so much - except it allows the user to override the calculated value. In other cases it can make a crucial difference, because only a calculation field can be unstored. Calculation fields also have the advantage of the formula being visible when browsing the fields list in Manage Database.

I would suggest this rule of thumb: always use a calculation field, unless you have a good reason to use an auto-entered calculation.

 

Edited August 27, 2021 by comment

[asterix]

On second thoughts I do have a reason for the field been text. It has a drop-down value list in order to select it's values in find mode.
And thanks for the advice, it is always useful to acquire new knowledge 🙂

[comment]

13 minutes ago, asterix said:

I do have a reason for the field been text. It has a drop-down value list in order to select it's values in find mode.

What makes you think you cannot format a calculation field as a drop-down list?

 

Edited August 27, 2021 by comment

[asterix]

Because I've tried it. If the field is a calculated field, the drop list displays only a question mark "?"
Perhaps it's something I am missing. Am I?

[comment]

Perhaps I am missing something, because I am not able to reproduce the issue - see the attached file.

 

DropDownCalc.fmp12

[LaRetta]

If you are getting a '?' then do you have the calculation set as number?  It should be text.

[asterix]

Yes, the calculator was as number. Now I changed to be as text and the drop down list displays all the values. Great, thanks a lot LaRetta  🙂