Text Substitution from another table

[simonen]

Hi all, I am trying to set up a text substitution that can look up a matching text in another table and substitute new text from another field in that same table record.

I am working with a database that contains words that should have greek unicode symbols in them, like NFκB, however they have NFkB written. I am trying to design a calculation that can match against a list of words and replace it based on that match.

Right now it seems to get stuck on the first record in the table it is looking into. It won't scan through the records in that table.

Thanks for any help you can offer.

[Raybaudi]

Hi

give a look at the:

Perform Find/Replace [ ]

script step

[simonen]

Thanks, I will have a look, but I am hoping for a solution that doesn't involve creating a script.

In addition, I want to keep the original info that was entered for searching purposes and have the new field take the same data but simply substitute the words present in the other table if that makes sense.

Edited August 9, 2010 by Guest

[Raybaudi]

So you are going to create another ( calculation ) field in that other table ?

How many words contains the original text field ?

( one or many )

[simonen]

Maybe I can explain more clear. I have 2 tables. Table one contains a field that is the title of a scientific publication. These titles often contain greek symbols. However in my case they are the roman character equivalent. "k" for the kappa "κ" unicode symbol.

My goal is to have a calculation field that will take that title and look for matching words in another table (Table Two) and if any are found, substitute the proper word.

Example Title: "NFkB Inhibition by omega-3 Fatty Acids Modulates LPS-Stimulated Macrophage TNF-a Transcription"

"NFkB" should be "NFκB" and "TNF-a" should be "TNF-α". I am trying to create a calculation that would substitute those words.

The bad word and proper word would be stored in another table as a 2 field list, because I don't really know how big it will get. My assumption will be a few hundred records.

My problem seems to be that it will only compare against the first record in Table 2. I used a relationship between the 2 tables that has the same value in all records.

Edited August 9, 2010 by Guest

[simonen]

Maybe I could do something like this, but it is cumbersome and I would have to insert a lot of lines into the calculation.

Substitute(Full Citation;

[GetNthRecord ( Symbol_tbl::Orginal ; 1 );GetNthRecord ( Symbol_tbl::Replacement ; 1 )];

[GetNthRecord ( Symbol_tbl::Orginal ; 2 );GetNthRecord ( Symbol_tbl::Replacement ; 2 )]

)

[Raybaudi]

Neither it will work if:

1) the tables aren't related

2) the Original word could be a part of another word in the Full Citation

In the above cases, you'll need two global fields and a calculation field along this one:

Trim ( Substitute ( ¶ & Full Citation & ¶ 

; [ " " ; ¶ ] 

; [ ¶ & GetValue ( Symbol_tbl::Orginal ; 1 ) & ¶ ; ¶ & GetValue ( Symbol_tbl::Replacement ; 1 ) & ¶ ] 

; [ ¶ & GetValue ( Symbol_tbl::Orginal ; 2 ) & ¶ ; ¶ & GetValue ( Symbol_tbl::Replacement ; 2 ) & ¶ ]

...

... 

; [ ¶ ; " " ] 

) )

[comment]

What you really need is a recursive operation - and if you don't have the Advanced version, a script would be your best choice, IMHO.

Note also that Substitute() works on patterns, not words (and "TNF-a" is two words anyway). You need to be careful not to modify patterns that contain substituted patterns - for example, "TNF-alpha" would become "TNF-αlpha".

[Raybaudi]

I used a relationship between the 2 tables that has the same value in all records.

I didn't see this part.

[simonen]

This seemed to work well.

Substitute(Full Citation;

[GetNthRecord ( Symbol_tbl::Original ; 1 );GetNthRecord ( Symbol_tbl::Replacement ; 1 )];

[GetNthRecord ( Symbol_tbl::Original ; 2 );GetNthRecord ( Symbol_tbl::Replacement ; 2 )];

[GetNthRecord ( Symbol_tbl::Original ; 3 );GetNthRecord ( Symbol_tbl::Replacement ; 3 )];

[GetNthRecord ( Symbol_tbl::Original ; 4 );GetNthRecord ( Symbol_tbl::Replacement ; 4 )];

)

I used excel to generate up to 150 record positions to look at.

You are right though. It will convert TNF-alpha into TNF-αlpha. However if I put TNF-alpha to TNF-alpha in a record above TNF-a to TNF-α it won't get converted improperly.

It's kind of clumsy though.

[bruceR]

I used excel to generate up to 150 record positions to look at.

Why not use FileMaker?

[simonen]

Some things are just easier in excel.