I think that's a good approach you started with (3 copies of the NameAddress table).
(Have a look in the first "More like this" thread here to the right.)
For the search functionality you want, maybe you can do it with a search object, I am not sure.
Otherwise you may create a new table (TransNames) which links to Transaction on TransactionID:
Assuming you first have left joined so that you have the three different types of fullnames in the Transaction table
ClientFullName As AnyFullName
UnderwriterFullName As AnyFullName
ThirdPartyFullName As AnyFullName
Now your users can search for '*Koti*' in the AnyFullName field to get all transactions where a person with Koti in the name has been involved in any way (as Client, Underwriter and/or ThirdParty)
Thanks a lot for your post.
This is exactly how I am currently hadling it in my script. Only change is instead of TransactionID, I am using RowNo() in my script.
But the key issue I am facing here is, because the NameAddress table has close to 2M records, having 3 copies of it and again concatenating to create a new table is taking quite a lot of time to load data and also more RAM space when users access this using access point.
I posted this here to find out what I am doing is correct or there any other better ways to handle this.
You may consider changing your datamodel so you have
Type (here you will have Client/Undertaker/Thirdparty)
Transaction to Agent is a many-to-many relation
If your users now search in FullName they will get all kinds of Client/Undertaker/Thirdparty)
If you also give them the option to choose Type they can (if they like) specify what they are searching for
Need som more info on your data model:
Are the UnderwriterID and Thirdparty different for the ClientID in the two tables? If they are, then I would rename them as something different in one of the two tables and link only on the ClientID.
Eg LOAD UndewriterID AS ClientUnderwriterID, Thirdparty AS ClientThirdparty.... for NameAddressData
If the ClientID can have transactions with many combinations of UnderwriterID and Thirdparty, (ie the key to the transaction table is ClientID, UnderwriterID and Thirdparty), then you need to decide whether you need the UnderwriterID and Thirdparty in the NameAddress table - ie what purpose do they serve?
If you need to link on all three fields (ie the key to the relationship is all three fields, you can allow QV to create a synthetic key, or create your own composite key. Another option is to merge the two tables to create a single fact table. The best option depends on your data model and desgin objectives.
Remember that QV creates an association between two tables by using a field of the same name in the two tables, so you would use:
LOAD NameID as ClientID .... for the NameAddress data
Hope these help