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

Outcomes