11 Replies Latest reply on Apr 23, 2014 1:00 PM by deathrobot

    Script behaves differently in Script Debugger

    deathrobot

      Title

      Script behaves differently in Script Debugger

      Post

           I have a script that duplicates an invoice record as well as its children (line items). I'm having a very strange situation where everything works fine when I use the script debugger, but the script behaves incorrectly when I trigger it normally. Basically the script:

           1) Grabs the current invoice id as a variable
           2) Duplicates the invoice
           3) Grabs the duplicate invoice id as a variable
           4) Goes to the related Lines table and finds the first invoice's children, using the variable
           5) Duplicates all these records, and resets their related ids to the duplicate invoice id

           The bold lines below are the critical ones to the problem I am experiencing. When using the script debugger, the Set Field to $idNew script step works as it should. When I trigger the script normally, the Lines::id_QuotesInvoices fields get set to a new id. I must be doing something wrong, but I'm not even sure how this possible.

           #
           # Grab the original record id and the duplicated record id
           #
           Set Variable [$idOrig; Value:QuotesInvoices::id]
           Duplicate Record/Request
      Set Variable [$idNew; Value:QuotesInvoices::id]
           #
           # Duplicate child records
           #
           New Window [Name: "hidden"; Left: -5000; Style: Document
           Go to Layout ["Lines" (Lines)]
           Perform Find [Restore: Lines::id_QuotesInvoices = $idOrig]
           Unsort Records
           Go to Record/Request/Page [First]
           Set Variable [$recordPosn; Value:1]
           Set Variable [$foundCount; Value:Get ( FoundCount )]
           Loop
               Exit Loop if $recordPosn > $foundCount]
               Duplicate Record/Request
          Set Field [Lines::id_QuotesInvoices; $idNew]
               Set Variable [$recordPosn; Value$recordPosn + 1]
               Go to Record/Request/Page [No dialog; $recordPosn]
           End Loop
           Close Window [Name: "hidden"; Current file]

        • 1. Re: Script behaves differently in Script Debugger
          philmodjunk

               Exactly how does QuotesInvoices::id  get a value in the new record when you duplicate it? Is it an auto-entered serial number? or is Get ( UUID ) used to enter a UUID value? If a serial number, is the value generated "on create" or "on commit"?--options you can select for this auto-enter feature.

               There are cases where a script will behave differently in the debugger due to the need for the windows to update with each script step, but I wouldn't expect this script to fail if the id field auto-enters a serial number with the default setting of "on  create" specified.

               Here's a similar script that is a bit simpler as it uses Go to Related Records to pull up the child records for duplication: Duplicating Bill Of Materials (duplicating portal line items)

          • 2. Re: Script behaves differently in Script Debugger
            deathrobot

                 Hi Phil,

            QuotesInvoices::id is an auto enter calculation:

                 Case (
                      not IsEmpty ( Self ) and creationTimestamp = modificationTimestamp;
                           UniqueID ( UUIDTimeNIC );
                  
                      IsEmpty ( Self );
                      UniqueID ( UUIDTimeNIC );
                  
                      Self
                 )
                  
                 I would think that if were a problem of the field not updating fast enough, $idNew would be set to blank, and therefore so would the Lines::id_QuotesInvoices fields. However, Lines::id_QuotesInvoices  is being set to some UUID, but it seems random; it's not the original QuotesInvoices::id, nor the new QuotesInvoices::id, nor any existing Lines::id, nor any existing Lines::id_QuotesInvoices value.
            • 3. Re: Script behaves differently in Script Debugger
              philmodjunk

                   I would try adding a Commit Records step immediately after the Duplicate Record step. That should ensure a proper update of the id field before your script tries to capture the value in a variable.

              • 4. Re: Script behaves differently in Script Debugger
                deathrobot

                     I tried your other script, and got the same problem. Then tried adding the Commit Records step in both scripts, and again got the same problem in both of them.

                     I also tried changing $idNew to $$idNew so that I could keep better track of it. It changes every time (so it's not holding on to an old value), and almost never matches any of my existing id's, though it seems related to the correct primary key id. I ran the script four times. One time, it worked. The other three times, it is almost the same id, but one part of it is changed. Here are the four tests; the first id of each pair is the QuotesInvoices::id and the second id of each pair is $$idNew:

                     1-2-063533777692-2400000-06287-075696725372376
                     1-2-063533777692-1000000-06287-075696725372376

                     1-2-063533777776-1000000-06287-075696725372376
                     1-2-063533777776-1000000-06287-075696725372376

                     1-2-063533777824-2400000-06287-075696725372376
                     1-2-063533777824-1000000-06287-075696725372376

                     1-2-063533777907-2400000-06287-075696725372376
                     1-2-063533777907-1000000-06287-075696725372376

                     Is there an obvious way it is possible that this change is happening?

                     Thanks very much, Phil!!

                • 5. Re: Script behaves differently in Script Debugger
                  philmodjunk

                       That leaves me suspicious of your UniqueID custom function. I have no idea what that custom function does so all I can say is that I'd look at it very closely if it is not getting the value expected.

                       But I just noticed something. I would not trust QuotesInvoices::id to stay unchanged in a given record. From the expression used, all you have to to is edit a field in the record--triggering a new timestamp in the modification timestamp field, and you get a new ID auto-entered. That's hardly the recipe for the proper Primary key here.

                       I'd use either an auto-entered serial number here or Get ( UUID ) to enter a value as the primary key. Or you might still use the custom function, but pare down the auto-enter calculation to just: UniqueID ( UUIDTimeNIC ) with the "do not replace existing value" option checked. In your duplicate script, use:

                       Set Field [ QuotesInvoices::id ; UniqueID ( UUIDTimeNIC ) ]

                       immediately after the duplicate records step to assign a new value to the duplicated record.

                       Note: if you used Get ( UUID ), you'd need the same script step.

                  • 6. Re: Script behaves differently in Script Debugger
                    deathrobot

                         Hi Phil,

                         You are correct that it was the id. I tried changing it to a serial number instead, and the problem went away. I'm a bit embarrassed, but realize I omitted one step when I posted my script; that is a Set Field step right after the Set Variable [$idNew] step. And that was where the problem lies. The ID calculation I'm using (Matt Petrowsky's) is set up so that you can duplicate a record and not have issues with duplicate ID's. The idea is that the only time the creation and modification timestamps will be the same is at the moment of record creation (e.g. when a record is duplicated). However, FM only seems to be accurate to the second, not the millisecond. So in my script, the record was duplicated, given a new id (which I would grab), the Set Field step would cause a modification fast enough that the modification timestamp would be the same (change happened in less than a second), and so would get a new id; one that no longer matched. Adding a 1 second pause fixes this.

                         I did also try your id calculation suggestion which makes a lot of sense, and have one question. It seems that it is necessary to remove the unique value validation in the field auto-enter box; otherwise FM throws an error at the moment of duplication since the ids are not unique. If I turn that off, how do I then prevent accidental duplicate values?

                         Thanks as always for all your help!!!

                         Michael

                    • 7. Re: Script behaves differently in Script Debugger
                      philmodjunk

                           Yes, I hadn't correctly analyzed how that calculation kept from updating to a new ID when the record was subsequently updated. blush

                           There is a new get function you can try to use in FileMaker 13 that would auto-enter the time in milli-seconds: Get(CurrentTimeUTCMilliseconds).

                           Good question on the Unique values validation. You might try adding in a set error capture script step to your script at some point prior to duplicate record and see if that enables you to keep the unique values validation and still duplicate the record.

                           I can think of another option, but wouldn't want to use it unless the above method simply doesn't work: Set a variable to the original record's ID. Clear the Id field. Duplicate the record. Go back to the original record and use set field to return the id value to the field. That's an awful Kludge so I wouldn't "go there" unless I had to.

                           PS. I find that auto-entered serial numbers are much nicer to work with. I stay with them in all cases except where there is a specific reason for not using an auto-entered serial number--such as merging data from multiple, independently edited copies of a database or when several parent tables link to different records of the same child table. The downside to that choice is that Import Records scripts used to pull data into a new copy of the file have to also include code that updates the next serial value settings, but that's old hat for me and not a complex thing to include in such a script.

                      • 8. Re: Script behaves differently in Script Debugger
                        deathrobot

                             Thanks, Phil. I will experiment with the Get(CurrentTimeUTCMilliseconds) function (I think it even works in FM12, though it is undocumented), and consider moving to serial numbers. I have no plans on using multiple copies, but figured if I ever did in the future, I'd already be set up. But maybe it's more trouble than it's worth!

                        Michael

                        • 9. Re: Script behaves differently in Script Debugger
                          deathrobot

                               Just read this:

                          GetAsTimestamp ( ( Get ( CurrentTimeUTCMilliseconds ) + ( Location::TimeAdjustment * 3600000 ) ) / 1000 ) returns 11/10/2015 1:43:55.304 PM if the time in UTC is 8:43:55:.304 PM on 11/10/2015 and the TimeAdjustment field has a value of -7.

                               So, would setting my table::creationTimestamp and table::modificationTimestamp fields to be auto-enter calculations of:

                          GetAsTimestamp ( ( Get ( CurrentTimeUTCMilliseconds ) + ( Location::TimeAdjustment * 3600000 ) ) / 1000 )

                          be the right way to use this?

                          • 10. Re: Script behaves differently in Script Debugger
                            philmodjunk

                                 In version 12, this function is both undocumented and has a different parameter name. I didn't mention that as I can't recall the actual name.

                                 Good question, but I'm looking that the Modification time stamp and trying to figure out a way to auto-enter a new value each time any field in the record is modified--that looks a cumbersome thing to set up given the fact that "calculation" isn't an option we can select for the Modification auto-enter setting...

                            • 11. Re: Script behaves differently in Script Debugger
                              deathrobot

                                   Oh yeah, forgot about that…

                                   Just to follow up, one simple tweak that fixed everything was to do the Set Field step, then a Commit Records, then grab the id. Not sure why I didn't think of it before, but it eliminates the need for the Pause Script step, and works every time.

                                   Michael