Using related fields or copied fields with synchronization issues?

[AndriesV]

I'm sure questions like this have been discussed/answered before. If there are some previous posts I should check out please point me to those links instead. I was in doubt putting this question here or in the 'Define Fields' Forum; if I entered it wrong please move it for me.

I'll describe the issue with 2 of the files/tables I'm using, there are actually more files in use for this database system but the same principle would apply. The 2 files are COMPANIES and CONTACTS, COMPANIES has a one-to-many relationship to CONTACTS

I started, as I think it should be done, by storing all unique information in one place only so e.g. the 'Company_name' field exists only in the COMPANIES file.

However after thousands of contacts had been created performance for finds on e.g. company name and contact-city, from within the contact-file, decreased so much that I had to add a field 'Copy_Company_Name' in the CONTACT file. Changes to the Company name are now only done scripted because it needs to update the COMPANIES-Company name and all CONTACTS-Copy_Company names etc. I've applied this technique for several fields that are frequently used in finds.

Is there maybe a better approach for this? The more files and relationships are added the more complex the synchronization of 'copied' fields becomes. Is there a way to e.g. trigger relookups of fields or to somehow store calculated fields that involve relationships so they can be indexed for fast searches?

Many thanks for any advice.

[Steven H. Blackwell]

quote:

---

Originally posted by AndriesV:

However after thousands of contacts had been created performance for finds on e.g. company name and contact-city, from within the contact-file, decreased so much that I had to add a field 'Copy_Company_Name' in the CONTACT file. Changes to the Company name are now only done scripted because it needs to update the COMPANIES-Company name and all CONTACTS-Copy_Company names etc. I've applied this technique for several fields that are frequently used in finds.

Is there maybe a better approach for this?

---

THis sounds like an architecture issue. A find on a related field will be somewhat slower than one on an indexed one. But your results sound abnormal.

Try finding on the company in the company file, then perform a go to related records script step for records in contacts.

HTH

Old Advance Man

[AndriesV]

Thanks for your reply Steven, let me try to explain my situation again with a different example.

- I have a file 'Contacts' with key field Contact_ID

- I have a file 'Quotations' with it's own unique Quote_ID and an auto-entered field rContact_ID.

- Every contact can have multiple quotations

In the 'Quotations' file I use a relationship on contact-ID to lookup fields like Contacts::SalesRep etc.

A frequently performed task for a salesrep would be to view his/her forecast which requires a (scripted) find on certain date and progress fields and the Contacts::SalesRep field. A find like this typically takes around 20 seconds. As soon as I change the Contacts::SalesRep field into a stored/indexed Quotation_SalesRep field within the Quotation file, the same find completes in less than 5 seconds. (Field Contacts::SalesRep field is also indexed)

Simulating this on a hi-cpu stand alone client shows that a major part of this issue is caused by the rather slow hosting server. This doesn't however change the fact that storing frequently used semi-static fields in related files rather then looking them up all the time takes away a lot of overhead on the hosting server. For me the performance increase outweighs the trouble of keeping the master and child files synchronized. The few times a contact salesrep changes so all child quotation-salesreps have to be updated (happens only a few times a week) a scripted update changes the few related child's in a second. Major updates for complete sales-territories reassignments require scripted updates of multiple files anyway.

So far so good, the above principle works fine for multiple occasions where I had performance issues.

To my question: Right now I update the 'copied' instances of fields in related files by going to a (hidden) layout with a portal for each involved relationship, by 'walking' through all portal rows I update/synchronize the changes. Is there a better approach for this to somehow force related records to re-lookup values on changes to the 'master' field.

Thanks for your time and help,

Andries