AnsweredAssumed Answered

Find specific word occurrence from a related table in a text field

Question asked by pet2 on Sep 14, 2016

Hi all,


My first post here so bare with me....


I have a simple database with two tables: tbl_1 and tbl_2.


On the tbl_1 I have two fields: 'text' and 's_code', and on the tbl_2 I have two fields: 'name' and 'n_code'.


What I'm trying to achieve is that if a 'tbl_1 :: text' field contains a word form 'tbl_2 :: name' then the 'tbl_2 :: n_code' be copied to the tbl_1 :: s_code'


For example in my tbl_2 I have a list of names and its codes:

Name             n_code

D/D                 Direct debit

Hx46               Meals


and tbl_1 I would like the s_code to be automatically filled


Text                                        s_code

d/d loan 32890004               Direct Debit

VDC Hx46 5615                    Meals



So far I added this formula to the tbl_1 s_code without success:


If ( PatternCount ( text ; tbl_2 :: name ) = 1 ; tbl_2 :: n_code ; "No match found" )


Any help would be appreciated