get data from field

[sirtemplar]

two questions:

1) i have FIELD 1 wherein i input data separated by semicolon (. sometimes there can be 2 or more values. what i want is on FIELD 2, to put automatically the first value. so if field 1 contains: group1 ; group2 ; group3 then field2 should contain only group1.

2)somethins like q.1 above but this time the separator is not ; but a space between data.

thank you for any help

[_henry_]

For both question, please refer to this custom function:

LeftSeparator

For Question 1, make a calculation field for FIELD_2. Result: (text)

Here is the calculation:

LeftSeparator ( [FIELD_1] ; ";" ; 1 )

For Question 2, make a calculation field for FIELD_3. Result: (text)

Here is the calculation:

LeftSeparator ( [FIELD_1] ; " " ; 1 )

Hope that helps...



EDIT:



Or if you do not want to use custom function, you can enter this inside the calculation field.



Q.1: For FIELD_2, the calculation (result:text) would be:

Left ( [FIELD_1] ; Position ( [FIELD_1]; ";" ; 1 ; 1) - 1 )

Q.2: For FIELD_3, the calculation (result:text) would be:

Left ( [FIELD_1] ; Position ( [FIELD_1]; " " ; 1 ; 1) - 1 )

Edited July 18, 2008 by Guest
Add another option

[comment]

Keeping multiple values in a field is generally not good practice. However, Filemaker does interpret return-separated strings as distinct values, so you can do:

GetValue ( Substitute ( FIELD 1 ; ":" ; ¶ ) ; 1 )

---

Note: if the semi-colons are surrounded by spaces (as in your example), you need to include those in the substituted string, or use Trim() on the result.

Edited July 18, 2008 by Guest

[sirtemplar]

thanks to you both for the reply. i still have to try them out.

regarding multiple values in a field. i am forced to do it since for example one person can have more than 2 phone numbers or email. i do not want to create so many fields for each.

or do you have some idea regarding this?

[comment]

It depends on how they're going to be used. The proper solution would be to have them as individual records in a related table. Although sometimes a repeating field or a value list (i.e. return separated) is quite satisfactory for auxiliary data, it seems that you need to single out ONE of multiple values as significant, so that would suggest the relational approach is the most appropriate here.

BTW, you might find this video tutorial interesting:

Data Tagging

[sirtemplar]

i used the calculation above:

Left ( [FIELD_1] ; Position ( [FIELD_1]; ";" ; 1 ; 1) - 1 ) 

it works BUT when there is only 1 data from field1, field 2 is empty.

[Cabinetman]

I am NOT anything more than a novice + but I took the challenge to see what I could come up with... and got this to work.

If (PatternCount (item name ; ";" ) = 1 ;  Left (item name ; Position ( item name; ";" ; 1 ; 1) - 1 ) ; item name)

May be all wrong but when there is a ";" in the text field it diplays the first part and if not the first part still showed in my calc field...