Finding text in one field, pasting it into another

[Scott_R]

I have a rather messy tab-delimited text file that I'm importing into FMP 8.5

I've done the best I can to format it for reasonable import, but I still have data mixed within fields. Basically, quite a number of one particular field (in >3000 records) looks like this:

MAINFIELDENTRY (wrongfieldentry1)

MAINFIELDENTRY (wrongfieldentry1, wrongfieldentry2)

The proper field contain the proper entries, but they may then be followed by additional data--contained within parentheses--that belong in a different field.

How can I automate--script, I suppose--cutting wrongfieldentry and pasting it into the correct one? Bonus if I can automatically get rid of the enclosing parentheses while I'm at it.

It would probably be best if I do this record-by-record, because there are more than a few

MAINFIELDENTRY (wrongfieldentry1) (otherwrongfieldentry1)

where the second parenthetical belongs in yet another field.

[Raybaudi]

First field calculation:

Let(

adj = TrimAll ( Substitute ( MAINFIELDENTRY ; [ "(" ; ¶ ] ; [ "," ; ¶ ] ; [ ")" ; "" ] ) ; 1 ; 1 ) ;

GetValue ( adj ; 1 )

)

Second field calculation:

Let(

adj = TrimAll ( Substitute ( MAINFIELDENTRY ; [ "(" ; ¶ ] ; [ "," ; ¶ ] ; [ ")" ; "" ] ) ; 1 ; 1 ) ;

GetValue ( adj ; 2 )

)

Third field calculation:

Let(

adj = TrimAll ( Substitute ( MAINFIELDENTRY ; [ "(" ; ¶ ] ; [ "," ; ¶ ] ; [ ")" ; "" ] ) ; 1 ; 1 ) ;

GetValue ( adj ; 3 )

)

[Scott_R]

OK, I suppose I'm just too much of beginner at this, but I've looked this over and have no idea what to do with it.

Also, "MAINFIELDENTRY" was just an example of field contents--that field will have a lot of different text contents. The only consistency is that the text I want to move from one field to another is contained within parentheses (which then need to be deleted).

[Raybaudi]

Basically, quite a number of one particular field ...

Change MAINFIELDENTRY with the real name of that "particular field" in all those calcs.

( I would remember you that those are 3 calc fields )

Also those calcs are good if that particular field contains data like this ( as your first example ):

MAINFIELDENTRY (wrongfieldentry1)

MAINFIELDENTRY (wrongfieldentry1, wrongfieldentry2)

and EVEN if contains data like this ( as your second example ):

MAINFIELDENTRY (wrongfieldentry1) (otherwrongfieldentry1)

[Scott_R]

Change MAINFIELDENTRY with the real name of that "particular field" in all those calcs.

( I would remember you that those are 3 calc fields )

Also those calcs are good if that particular field contains data like this ( as your first example ):

MAINFIELDENTRY (wrongfieldentry1)

MAINFIELDENTRY (wrongfieldentry1, wrongfieldentry2)

and EVEN if contains data like this ( as your second example ):

MAINFIELDENTRY (wrongfieldentry1) (otherwrongfieldentry1)

Thank you for your patience. I have *no* FileMaker scripting experience at all, so I've been trying to teach myself the basics just so I can figure out just where to plug the script into.

Now, I think I have something (or I could be totally off) in creating a new script and pasting the script into a Perform Find/Replace, and changed MAINFIELDENTRY into the field's actual name.

Result: "the specified table cannot be found"

Clearly, I have no idea what I'm doing.

[Raybaudi]

Back from the start point...

1) You have a field ( which has a name I don't know so let say its name is [color:red]particular field )

2) [color:red]particular field contains ( after importing ) data like this:

aaaaaaaaaaaa (bbbbbbbb) (cccccccccccc)

OR

aaaaaaaaaaaa (bbbbbbbb, cccccccccccc)

OR

aaaaaaaaaaaa (bbbbbbbb)

OR

aaaaaaaaaaaa

3) to parse that data and obtain what you need, we now define 3 new fields ( A,B and C each calculation fields ) each with the calcs above ( NO script, only calculations ).

Here there are those 3 calculations:

field A calculation:

Let(

adj = TrimAll ( Substitute ( [color:red]particular field ; [ "(" ; ¶ ] ; [ "," ; ¶ ] ; [ ")" ; "" ] ) ; 1 ; 1 ) ;

GetValue ( adj ; 1 )

)

field B calculation:

Let(

adj = TrimAll ( Substitute ( [color:red]particular field ; [ "(" ; ¶ ] ; [ "," ; ¶ ] ; [ ")" ; "" ] ) ; 1 ; 1 ) ;

GetValue ( adj ; 2 )

)

field C calculation:

Let(

adj = TrimAll ( Substitute ( [color:red]particular field ; [ "(" ; ¶ ] ; [ "," ; ¶ ] ; [ ")" ; "" ] ) ; 1 ; 1 ) ;

GetValue ( adj ; 3 )

)

At th end you'll have:

A contains: aaaaaaaaaaaa ( always )

B contains: bbbbbbbb ( sometimes is empty )

C contains: cccccccccccc ( sometimes is empty )

[Scott_R]

I see, thanks. My mistake was assuming it would correct the original field, rather than putting the data into separate fields--again, totally new to this sort of thing.

Two more questions:

1) to clarify, I need to populate two fields. The field that needs to be cleaned up looks like

aaaaaaaaaa

OR

aaaaaaaaaa (bbbbbbbbb)

OR

aaaaaaaaaa (bbbbbbbbb, ccccccccc)

OR

aaaaaaaaaa (bbbbbbbbb, ccccccccc, ddddddddd)

(and so on--there could be quite a bit of text inside the parentheses)

I want that info put into *two* fields:

aaaaaaaaaa

AND

bbbbbbbbb, ccccccccc

OR

bbbbbbbbb, ccccccccc, ddddddddd

and so on. One field to contain everything NOT in the parentheses, the other everything that IS in them.

-- I've pretty much managed to clean up the

aaaaaaaaaa (bbbbbbbbb) (ccccccccc)

or, at least, I hope. There should now be no more than a single pair of parentheses in that field.

2) since these are calculate fields, won't that mean I can't get rid of the messed-up fields, since if I delete them the clean (calculated) fields will then empty?

thanks again

[comment]

It's difficult to advise with such an abstract example.

In general, you must import your data as is. Which means that the field designated as the target for the import, let's call it Field A, will initially contain the entire "cell" from the source file.

You then have 3 options to deal with the problem:

1. Define 2 additional calculation fields, Field A1 and Field A2. Each field extracts a portion of the imported data in Field A.

2. Same as 1, except the additional fields are regular fields, auto-entering the extracted portions.

In both these options Field A will remain unused (but NOT deleted) in all subsequent dealings with the data, and Field A1 and Field A2 will be used instead.

3. Run a script immediately following the import, extracting a portion of Field A into Field A1, AND deleting the extracted portion from Field A.

Each of these has its pros and cons, so it's hard to say which one may be best for you. There's also the possibility of importing into a temp table, doing all the post-processing there, and importing the cleaned data into the "real" table.

As for the actual calculation, **IF** your data follows your example literally (i.e. no spaces within the blocks), then it could be simply:

Field A1 =

LeftWords ( Field A ; 1 )

Field A2 =

RightWords ( Field A ; WordCount ( Field A ) - 1 )

This gets the bonus for removing any parentheses (or other punctuation) surrounding the extracted portions.

[Scott_R]

To try and make things a little more concrete:

this is a roundabout attempt to create an image database. I've tested the commercial ones out, and they don't do what I want: I mainly need a TEXTUAL database for keywords and the like and (as I'm mucking about with now), a single sample image from each folder.

That said, I have HUNDREDS of folders, each containing anywhere from a single to over a hundred subfolders, each therein containing JPEGs.

Up to now, the folders have been basically organized by general descriptions, often with additional descriptions following in parentheses (when the description applies to the contents as a whole). So, the top level folder will be something like:

descriptive name (alternate description, another alternate).

Within those top level folders are folders that hold particular sets:

one particular set

another particular set (additional description)

When I was able to, I often appended dates:

this particular set (27-March-2008)

Yes, a total mess--it started out without planning, snowballed, and now I'm trying to organize it.

I managed to catalog the paths using Simple Cataloger, exported as a text file, then use Text Wrangler to clean it up as best I could. After initial cleanup (removing extraneous path info) the text file looked like the following (top level folders in bold, subfolders in italics):

descriptive name (alternate description, another alternate):one particular set

descriptive name:one particular set

descriptive name:one particular set (27-March-2008)

descriptive name (alternate description, another alternate):one particular set (additional description) (27-March-2008)

descriptive name (alternate description):one particular set (additional description) (27-March-2008)

--thousands of lines of that (the cataloging app used a colon to separate the folder levels--I removed the rest of the path information).

I then substituted a TAB for the colons, and a TAB for the parenthesis preceding the date as a marker for importing to the different fields.

When imported, it worked out as:

FIELD1-->descriptive name

FIELD2-->one particular set

FIELD3-->date

--or, of course:

FIELD1-->descriptive name (alternate description, another alternate)

FIELD2-->one particular set (additional description)

FIELD3-->date

I couldn't figure out how to differentiate the other parentheticals in order to get them to their own fields--that would have messed up import. The problem was that I couldn't rely on each line having the same set of information:

descriptive name TAB alternate description TAB one particular set

descriptive name TAB one particular set TAB 27-March-2008

descriptive name TAB alternate description TAB one particular set TAB additional description TAB 27-March-2008

descriptive name TAB alternate description TAB one particular set TAB 27-March-2008

would have put info into the wrong fields.

My current task is to get (alternate description, another alternate) from FIELD1 into its own field (say, FIELD4)--just one field, regardless of how much text is inside the parenthetical, and (additional description) from FIELD2 into yet another (FIELD5) In the end, this:

FIELD1-->descriptive name

FIELD2-->one particular set

FIELD3-->date

FIELD4-->alternate description, another alternate

FIELD5-->additional description

Presumably, what works to clean up FIELD1 will work for FIELD2.

(whew! See why I avoided specifics?)

[comment]

Well, that's certainly a LOT of information, though I am not sure how much of it is relevant. Anyway, I think your question has already been answered.

I'm just wondering if you have thought this out beyond the question of importing. I don't quite see how splitting multiple descriptions into two fields is going to make the database more functional.