Scripts need updating to work reliably after FM12 upgrade

[daveinc]

Hi guys, me again. I've solved this issue myself already but found it aggravating enough that I thought I would post it here and see if anyone else had any similar issues.

 

I have a complicated script that builds an Estimate after the entry of some base information. It is quite fast for the work that it does and has been in use for a couple of years without any problems.

 

The script builds a multi-tiered Estimate for printed materials based on discounting for different quantity levels. The user builds an Estimate from materials and work centers plus markup for the base Quantity then the script creates duplicate estimates for all of the higher quantities desired.

 

Since the upgrade to FM13, the users have reported that just the very first material line that was present in the base estimate was missing on all of the higher quantity estimates that were constructed via a certain sequence and never edited.

 

There are multiple triggers to update all of the estimates when the base estimate is changed, and if any of those triggers but a particular one is used, the missing first lines are generated properly if the estimate is edited after its original incarnation. If I run the offending script with debugger on and a stop right before the first line item is duplicated, it always works perfectly. We looked through the 12,000 or so Estimates generated by FM11 with the same script on the same server and there was never a missing first line item.

 

I had to add a .01 second pause to the script in place of the debugger stop and now it works correctly every time(so far). The script checks for the existence of a related record through a relationship at that spot. It would not work using IsValid, Count, or an unstored FoundCount until the pause was added.

 

Just curious if I need to be on the lookout for this anywhere where I use the existence of a related record created earlier in the script(which is in many of my scripts) because it could be wreaking havoc already and no one has noticed yet.

 

Thanks,

Dave

[Wim Decorte]

I am not aware of any issues like that.

 

But this:

 

 

 

There are multiple triggers to update all of the estimates when the base estimate is changed, 

 

 

is a bit of a red flag.  Triggers can be very treacherous and having multiple is something I would probably want to fix in a more tightly scripted workflow (perhaps the transactional model?).

[daveinc]

Hi Wim,

These triggers are all within the estimate and the estimate can only be edited by the Owner. The Estimate MUST update all of the iterations if there is a change to any one of tens of fields that affect price in the Base Estimate. Otherwise our estimators would be spending their entire day creating new estimates for minor changes to the original and piling up hundreds of old worthless records. 

 

But that is not the issue anyway. The issue is that a record created in another table isn't seen via any available function through a direct relationship unless a pause is put into the script. Committing the records has no effect, nor does resetting the primary key. The pause HAS to be there for FM to see that the record is there through the relationship. I have never had this issue before in 15 years of Filemakering.

 

And the question is whether I should add this pause as a safeguard to all of my scripts that assume FM can see a record through a relationship if it is there when it was created less than "x" seconds ago earlier in the same script.

[Wim Decorte]

 

 

And the question is whether I should add this pause as a safeguard to all of my scripts that assume FM can see a record through a relationship 

 

No, that's a red herring.  The underlying issue is not there.

 

These triggers are all within the estimate and the estimate can only be edited by the Owner. The Estimate MUST update all of the iterations if there is a change to any one of tens of fields that affect price in the Base Estimate. Otherwise our estimators would be spending their entire day creating new estimates for minor changes to the original and piling up hundreds of old worthless records. 

 

But that is not the issue anyway. 

 

Yes it is.  I'll bet you have triggers stepping on triggers.

 

It is easy enough to use a model where when someone wants to modify an estimate you take full control over that one and all of the dependent ones without having to rely on triggers.

 

The concept is called "transactions" and you can find one good implementation of it here: http://www.modularfilemaker.org/module/transactions/

 

It is the same concept that applies to all accounting transactions where one edit has to update any number of related items but they all have to work or fail at the same time.

[daveinc]

Wim,

I understand the transactional model, and in fact I am using it in this script repeatedly, but again that is not the question I am seeking an answer to. Because of the nature of our parts list, I have to create the initial Base Estimate Line Item records in their native table. The issue is NOT that my transactional edits after all of the records for the multilevel estimate are created are failing at any level. The script in question creates the initial Base Estimate Line Items and all of the initial Tiered Estimate line items based on the user's selection of a Part made up of many materials and work centers. After the initial Base Estimate line items are created in the Line Items table, my script uses relational transactions to create all of the initial Tiered Estimate Line Items. It successfully completes all of these creation transactions, but misses the first record in the relationship and throws no error because as far as FM is concerned at that point the first related record DOES NOT EXIST. I compared the number of related records for the Tiered Estimates at the end of the script with the number of records in the Base Estimate and it is always the same without any errors(but there is one less record in the Tiered Estimates in reality). I added a Perform Script at the end of the creation script just to compare the number of records a second time from a different layout with the same underlying table, and the second script sees the discrepancy.  Why would only the first record in the relationship be the only one that isn't seen(until after the script finishes and I switch layouts, where all is well), and ONLY when a certain script is used, and it never once happened through 12,000 previous estimates created with the exact same procedure/tables/relationships in FM11? And why would a .01 second pause after the initial record creation but before I use the relational transactions to create the tiered records eliminate the problem entirely( admittedly only so far on 100 estimates or so)? You have to remember that this problem is there when there are NO other users connected to the server after hours and can be reproduced consistently every single time. If I run the script in the old FM11 system from 1 month ago(which has around 200 fewer estimates) I can't make it happen even once, and it never happened during the 12,000 estimates made in 11 with all the users connected. The transactional model is obviously the best method, especially for updating the existing tiered estimate records, but I would like to know why a very recently created related record is not seen immediately by FM12 when it clearly is by FM11. This makes me question using relationships for anything where it is important that the script knows what is there when the related record was created in its native table earlier in the same script. Seems I would have to do a lot of extra layout switching or pausing to make sure if I can't rely on FM12 relationships to do this work.

[Wim Decorte]

AFAIK there are no known bugs ore behaviour changes between 11 and 12/13 that would produce this.  Without being able to step through your scripts, I'm going to say that there is something in your scripted process that assumes something that is not quite correct and that implicitly worked in 11 but no longer in 12/13.  Implicitly as in more by accident than by design.

 

Can you post your scripted workflow?

 

The other thing to consider is whether your FMS machine is up to the task, does it meet the minimum requirements for 12/13?

[David Jondreau]

Is the related record being committed between its creation and the check for its existence?

[daveinc]

Yes. That does not help. I'm even committing the parent record just for good measure.

[Justin Close]

I assume that this file is hosted; what kind of hardware does your server use?  Is it using SSD drives?  I am wondering if there is a speed issue going on, that is that your server isn't fast enough to quite catch up with the speed the client transacts at?  I know that there were some issues in FM12 were, if the server were fast enough, that some data corruption would be introduced.  Perhaps the inverse of your problem.

 

Does your script check for the existence of said record immediately after it might have been created?  Or is it many steps later down the process?