Searching Related Table Fields & Indexing

[dayzrespite]

Hello,

I am new to this forum. I was reading through the posts here, which is the forum that is appropriate for my question, and was not able to find an answer to my problem.

I have a Client Table and an Address Table, (the Address Table related to the Client Table by the ClientID)

In my Client Table I want to create a field which contains various search criteria, one of them being the addresses.

The way I went about it was to create a calculated field with the List Function that lists all the addresses related to the client. However, this calculated field would be unstored because the referenced field is from a related table.

This would make my search slower, of course, and it is something I want to avoid. What would be the best way to still keep my related table, and store the addresses in an indexed field.

I appreciate your time and any information you can share.

Thanks,

Linndgberg Ruballos

[Ocean West]

Welcome to the forums!

Actually there is a few ways you can solve this... When searching for "address" info popup a new window into the address table and search that table first then do a GTRR (go to related record) matching all from the Address to the Client via the parent ID. Once there then finish with a constrain find to further restrict your criteria.

Another option is when ever you modify a record or any of its children records (address) use script triggers on the record commit or exit to run an update, setting the STORED field using the LIST Function based on the relationship.

[comment]

Why don't you simply place fields from the Addresses table on a layout of Client and enter search criteria into them (this is assuming you are doing a manual find, otherwise you don't need even that)?

[dayzrespite]

Thanks to you both for your answer.

Ocean West: I will go with the Script Trigger on Record Commit. In this case it is the most simple and best solution. I really appreciate your help! Hah, I didn't think of it.

comment: The reason I have a separate Table for the Addresses is that I want more flexibility on the number of addresses I can have, per my client's requests (sometime's there are quite a few). Also, I use the same Address Table for addresses for Clients, Companies, and for Offices that my client has in different parts of the world.

Thanks again,

[comment]

I understand the need for a child table of Addresses. What I am saying is that you CAN search fields of the child table when doing a find in Clients. I doubt anything could be simpler than that. Maintaining a duplicate set of data should be used only as the last resort in very extreme situations, IMHO.

[Ocean West]

comment is correct,

Unless you have a VERY VERY large child table and many many users who are searching over WAN.

I have a normalized table and occasionally do finds by address or related data and found performance to be acceptable.

[Ocean West]

If you want a really stellar way to do searching try this

http://www.seedcode.com/cp-app/ste_cat/fmsearchresults

[dayzrespite]

Thanks again comment and Ocean West.

Comment, I overlooked the context of what you mentioned earlier, and you are right. I had an idea in my mind, doing a search like Address Book for Mac OS X, where on one field you enter your search criteria (name, address, phones, email, notes) and you get results fast, on the same layout and without using any pop-ups and things like that, also performing the search each time you type a letter, and displaying merge fields on a portal on my layout rather than the actual fields. User's enter and edit addresses through a pop-up.

I will think a bit more how i want to implement the find, so it is easy for the users and fast.