How to trigger auto-enter calculation from a related table

[infoweb]

Hi there,

 

We recently moved our FM13 database from our LAN to a WAN - and have noticed some very significant slow downs.

 

I think most of the issues are coming from non-stored calculation fields referencing related fields.

 

I'm hoping that I can switch these to number fields and use auto-enter calculations, the thing is I need them to update if the value in any of the related fields is updated.

 

I'm guessing I need to use some script triggers in the related records, but I'm not exactly sure HOW to to get the auto-enter to recalculate.

 

I'll explain a bit more.

 

I have 3 tables:

 

• Products
• Purchase Orders
• Purchase Order line items

 

in Purchase order line items I have "Purchase Orders Line items::Landed cost" field which is calculated using "Purchase Order Line Items::Cost" x "Purchase orders::xrate"

 

I then have "Products::Average Landed Cost" which takes the average from all related records in "Purchase Orders Line items::Landed cost"

 

When I use a Product in another table eg sales, repairs etc, the system takes at least 2+ seconds to simply add the product to the record.

 

I figure I need to use a script trigger to trigger an update when a field is modified in a related record eg. when "Purchase orders::xrate" is modifed the script triggers "Purchase Orders Line items::Landed cost" to recalculate - but I'm not clear on HOW to trigger the re-calculation.

 

Any tips or suggestions on best practises would be much appreciated.

 

Mark

[comment]

I think most of the issues are coming from non-stored calculation fields referencing related fields.

 

 

This should be relatively easy to test and thus avoid denormalization if not necessary.

 

 

but I'm not exactly sure HOW to to get the auto-enter to recalculate.

 

 

An auto-entered calculation referencing a related field will recalculate when the (local) matchfield is modified. In your example, re-entering the existing value into the PurchaseOrderID field in Purchase Orders Line items would cause the auto-enter to update itself.

 

Alternatively, you could lookup the parent xrate value into the child record and - when necessary - perform a relookup.

[infoweb]

@comment

 

Thank you for taking the time to reply to my post.

 

I took your suggestion and tested to see if it was indeed the non stored calculations that were causing the issue. Basically I changed the non-stored calculations to number fields. This resulted in some speed improvements. Hard to say exactly how much - but it seems to be around 50% faster now.

 

So I should be able to create a trigger script that will re-enter the match field from Purchase orders into all related records in Purchase Order Line items.

 

Either way it sound like I will need a trigger script to trigger some kind of action each time I modify a relevant field in Purchase orders. 

 

I was thinking of adding a "modification timestamp" to the "Purchase order line items", then create a trigger script that updated the timestamp whenever a relevant field in Purchase orders was updated.

 

then have the purchase order line items auto enter field watch the time stamp and re-calculate if it was changed.

 

Are there any issues with doing that?

 

Mark 

[comment]

I was thinking of adding a "modification timestamp" to the "Purchase order line items", then create a trigger script that updated the timestamp whenever a relevant field in Purchase orders was updated.

 

I am not sure what advantage the extra field brings to the game. That said, I don't see why it shouldn't work (subject to the note below).

 

 

Note that as is always the case with denormalized data, you need to consider the possibility of an update failure - for example due to one or more line item records being locked by another user, leaving your data in a state of discrepancy.

[infoweb]

the reason for the timestamp being set through a trigger script is that there is some lookup data that I do not want updated in the PurchaseOrderLineItems eg. Cost - which is looked up from the Product table.

 

ie. I only want the one field with the auto enter calculation to update.

 

My reasoning is that if I set the auto enter calculation field to watch the local timestamp field then when the script updates it from the Parent record then my auto enter calculation with re-calculate.

 

Just wondering what other methods there may be and the pro's and con's of them.

 

Mark

[comment]

there is some lookup data that I do not want updated in the PurchaseOrderLineItems eg. Cost - which is looked up from the Product table.

 

I don't think that modifying the matchfield to Purchase Orders will cause a relookup from Products.

 

In any case, wouldn't it be simpler to let the script recalculate the value and update the auto-entered field directly?

Edited June 3, 2014 by comment

[infoweb]

 lol - ummm yes, it would...

 

Sometimes the obvious just escapes me.

 

thanks for your input.