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)
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.
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.
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:
Is there an obvious way it is possible that this change is happening?
Thanks very much, Phil!!
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.
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!!!
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.
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!
Just read this:
GetAsTimestamp ( ( Get ( CurrentTimeUTCMilliseconds ) + ( Location::TimeAdjustment * 3600000 ) ) / 1000 ) returns 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?
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...
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.