I'm looking for some help with a calculation field for final keywords that needs to substitute keywords from a related table. I have this working fine using Substitute and GetNthRecord. However, I'm missing some obvious logic to do this correctly. I currently have something like this: 

Substitute ( text  ; 

[ GetNthRecord ( searchString ; 1 ) & "," ; GetNthRecord ( replaceString ; 1 ) & "," ] ; 

[ GetNthRecord ( searchString ; 2 ) & "," ; GetNthRecord ( replaceString ; 2 ) & "," ] ; 

[ GetNthRecord ( searchString ; 3 ) & "," ; GetNthRecord ( replaceString ; 3 ) & "," ] 

)  

 

This works fine,  but I have over 100 records in the related table and will have several hundred eventually. So the way I'm doing this now, I need to have a line for every record. I obviously need a variable for the recordNumber that will include all records from the related table, or an easier way to do this, but I don't know how. Thanks in advance. 

Do this... in the related table create a calculated ROW that is just its one line... literal string:

 "[" & Quote ( searchString & ","  ) & " ; " & Quote ( replaceString & "," ) & "]"

Then when in the parent context in your script do a set $list  List ( keywords::row) 

that should return a list like:

["cat," ; "dog,"]
["mouse," ; "horse,"]
["pink," ; "red,"]

set that as a variable $substitute 

"Substitute ( "&  Quote ( data ) & " ; "&  
Substitute ( $list ; "¶" ; ";¶" )
&" )"

which will result in 

Substitute ( "cat, mouse, pink," ; ["cat," ; "dog,"];
["mouse," ; "horse,"];
["pink," ; "red,"] )

Then set your results by wrapping it with Evaluate

Set Field [ data::results ; Evaluate ( $substitue )

 

sample attached

Replace.fmp12.zip

Hi Ocean, 

Thanks so much for your response. I'm still working my way through this and I see that this uses a script to enter the results in a text field. This may be ok, but I think I'd prefer if somehow I can use a calculation field to calculate the final results. Is that possible? 

you could use a calc field but then the data would not be indexed as it is always evaluating related data. This would make it very slow to ever search on the contents of that field. 

But yes you can have that calculation be the results in a field. 

it may be ideal to add a word separator as the substitute will find and replace partial words...

 "[" & Quote ( " " & searchString & ","  ) & " ; " & Quote ( " " & replaceString & "," ) & "]"

"Substitute ( "&  Quote ( " " & data ) & " ; "&  
Substitute ( listof ; "¶" ; ";¶" )
&" )"

that way if the source data had catmouse, it would not change to cathorse, 

also there it can get odd with the very first item in the source as unless it has a space in front it may not be adequately replaced.

 

Does anyone know a way to do what is described here, but have it only work on whole words? 

I have this working fine now, except that I need for it to only work on whole words, instead of partial words as it does now. Can anyone help? 

Edited September 3, 20187 yr by Schneids

Hello,

Could you make an exemple of what you want ?

Tom

Here is an example of the partial word problem. Here is a substitute list: 

["cat," ; "dog,"]
["mouse," ; "horse,"]
["line," ; "single line,"]

Here is my data: 

"cat, mouse, gasoline, "

And here is the result: 

"dog, horse, gasosingle line, "

As  you can see, "gasoline," is being replaced with "gasosingle line," which is my first problem. 

Edited September 3, 20187 yr by Schneids

Hello,

I think you have to make a script.

Two options : first get the length of the word to replace is the same. So gasoline is different of line.

Or search first and last char are the same.

Better solution make a calculation for comparaing letter by letter.

Tom

I'm not sure that will work, since letter by letter would match. What I may need is to eliminate any matches that have any letters directly before the matching string, but I don't know how to do that. 

Edited September 3, 20187 yr by Schneids