5 Replies Latest reply on Jan 12, 2017 4:52 PM by rbogdanoff

How to populate a new field based on content of several already existing fields

(Environment: Filemaker12 / WIndows10)

I need to populate a new field - fieldA, for hundreds of existing records based on what is in existing field1, field2 and field3.

In simplest terms, if field1 is not empty then put a 1 in fieldA, same for field2 and field3.

Therefore fieldA could contain any combination of 1, 2 and 3, ie:

1

12

13

2

23

123

I know how to write a script to test the three fields for content and construct a variable

that can then be written to fieldA but I don't know how to make the script work to populate new fieldA for all the existing records.

I also wonder if there is a way to make fieldA a calculation field and write the proper steps in the calculation to automatically populate the field. Your suggestions and help would be greatly appreciated.

• 1. Re: How to populate a new field based on content of several already existing fields

If you decide to make this new field a calculation field, then you use the Let Function:

Let ( [

f1 = If ( IsEmpty ( field1) ; "" ; 1 )  ;

f2 = If ( IsEmpty ( field2) ; "" ; 2 )  ;

f3 = If ( IsEmpty ( field3) ; "" ; 3 )

] ;

f1 & f2 & f3

)

• 2. Re: How to populate a new field based on content of several already existing fields

or another way. because List() takes only non-empty values:

Substitute ( List ( f1 ; f2 ; f3 ) ; Char(13) ; "" // change return-delimited )

beverly

Sorry, I totally missed the need for the 1,2,3...

NEVERMIND

• 3. Re: How to populate a new field based on content of several already existing fields

If you would rather not make FieldA a calculation field another option is to use "Replace Field Contents" and select the "Replace with calculated result" option and then apply Ryan's calculation.

• 4. Re: How to populate a new field based on content of several already existing fields

rbogdanoff wrote:

I know how to write a script to test the three fields for content and construct a variable that can then be written to fieldA but I don't know how to make the script work to populate new fieldA for all the existing records.

If you can write the script to do this with one record. all you need to do is make that script loop through all records. Something like:

Show All Records
Go to Record/Request/Page
[ First ]

Loop

Set Variable [ ]

Set Field [ ]

Go to Record/Request/Page [ Next; Exit after last ]

End Loop

Exit Script [ ]

• 5. Re: How to populate a new field based on content of several already existing fields

Thanks that is the solution. I don't have much experience using LET so I

tend to forget it is there

Robert