I like to have 2 master tables linked when they meet the same criteria.
Table 1: Office with the field 'City'
Table 2: Client with the field 'Client City'
A linlk from Table 1 to Table 2 should be created as soon as a new client receord is created. Expl.
When the client city is London, the associated London office shall be automatically linked to the client record.
Sure.. Assume Table Client and Table Office each has a field named City. .. and your Client form looks like
In the Client.City After Update Trigger.. put the following code:
Thanks Michael, that works.
Yet I have another challenge, the offices are having another criteria (choice field) and some are active and some inactive. When chosing the first record, it could be that the inactive office for example in London is getting selected, as they have not been entered in sequence. Well need to have an additional option to only select the active office.
@MB... not a problem.. Assuming only one "active" office per city... and the Office has a Status choice box of Active (1) and Inactive (2).. just add an "and Status = 1" to the selection criteria