Duplicate a record but not the serial

[DanBrill]

Hey,

I have a contact management db were each record is given a unique auto-entered serial number. The serial number is used as the key for lots of relationships. If a user creates a record for Mr. Smith, with all of the address and phone info, and then has to create a record for Mrs. Smith, the first thought is to duplicate the record and then change the name, saving all of the time of address and phone data entry.

The problem is that the serial number duplicates too. I've the ID set to be unique, and don't allow user overrides, but the duplicate record command creates two records with the same ID, and this has the potential to be fatal.

Any ideas on how to make the ID unique in absolutely positively every situation?

Thanks,

Dan

[dbruggmann]

Is it a networked multi user solution?

[dbruggmann]

I only think loud, but

[LaRetta]

Hi Dan,

I wonder if more is going on here, because FM knows to create a new ID when a record is duplicated. That was my understanding and I just tried it. I have a serial ID with auto-enter set to increment it. It creates a new ID each time I duplicate the record.

What version are you using? Did the way FM handle this change through versions? Unless you are creating your own UniqueID but using a serial field as part of your UniqueID? But, even then, the serial portion would increment appropriately.

LaRetta

[Ugo DI LUCA]

LaRetta said:

Unless you are creating your own UniqueID but using a serial field as part of your UniqueID? But, even then, the serial portion would increment appropriately.

Not if set as an auto-entered calculation field...

A tedious solution could be the use of a validation calculation involving a selfJoin on the ID of type not Count(::SelfJoin:serial)>1 and a message alerting the user that he must hit the scripted button for this.

Then, include a step in that script that would delete the previous dupe and create a new one.

But still, locking mens and forcing the user to use a script.

[LaRetta]

Oh! Of course, Ugo!

Thanks for catching that for me. Sorry for almost mis-steering you Dan! It's been so long since I've allowed regular duplication of a record that I had forgotten that's why I don't use it.

I disable that option in privileges and script it because a regular duplication works for old-fashioned serial numbers - but not auto-enter calcs.

LaRetta

[DanBrill]

Thanks for all the help guys. I think I found the problem. LaRetta's comment that there is more going on here got me thinking.

I've got my solution set up so that when I create updates I can send out an empty set of files to my users. If they are new users, then they just start using the empty solution and the first serial number is 1. If they are updating an old version, then a whole series of scripts runs to import the data from their old files into the new version. (And yes, after writing all of those import scripts I'm a big proponent of FM ver. 7 divorcing the layouts from the data!)

Somewhere in the process of testing I've imported records from another version, but didn't reset the serial number to start numbering after the largest serial number of the imported records. So I need to run this:

If (Max(Self::ID)=""

Set Next Serial Value ["ID", "1"]

Else

Set Next Serial Value ["ID", "Max(Self::ID)+1"]

End If

Once this is run, then the duplicated records are given unique serial IDs.

To prevent this in the future I should probably just run this as part of my "OnOpen" script.

Thanks to all of you for the help.

Dan

[Vaughan]

Beware of the Max and Min functions: I have a file with 42,000 records, and it takes FMP about 5 seconds to calculate the Max function even on a fast 2xcpu machine. This is long enough for users to think the system has crashed, or at least be a great source of complaints.

[LaRetta]

Hi Dan,

This may not work for you but I was wondering if these off-site Users even need Serial Numbers. What I mean is ... let them run their own SN series so their solution works but when you import their new records into your solution, don't import their SN (or UniqueID) field(s) at all. Set Import to 'perform auto-enter' which would continue your SN series where it left off. Are they always NEW records coming into your solution?

I was also wondering how you plan to get your question answered - the one about...

a user creates a record for Mr. Smith, with all of the address and phone info, and then has to create a record for Mrs. Smith, the first thought is to duplicate the record and then change the name, saving all of the time of address and phone data entry.

... because I provide Users with a button which switches to a layout with a checkbox (based upon FieldNames() - actually a modification of it) so Users can select which fields to duplicate - it's more dynamic than setting the fields within the script and gives Users flexibility - yet allows the Developer to restrict fields that Management has determined they shouldn't be allowed to duplicate. And it works without GetField[]. If you are interested, I can write it out for you. It's pretty simple really and it makes adding fields to the checkbox (and thus the script) painless for the Developer as well. Just drop or remove any fields onto a secret Developer layout and it becomes part of the scripted process and then appears in the checkbox for your User to select.

LaRetta

[DanBrill]

Thanks for your follow ups. Vaughn, your note on Min and Max functions is good to know. I'll have to think about that one. I have had similar problems with calculation fields that compute Standard Deviations and Z-Scores. Trying to have a constantly-up-to-date Standard Deviation is a horrible drag on the system. I've had to change it so that it calcualtes by script when the user really needs it to be accurate. (Between the scripted calculations they have to live with it being close to accurate.) Seems like the Min and Max functions suffer the same problem. (However, I just tried a similar process of finding the Min, Max, Mean, and SD of 50,000 random number in Excel. It doesn't seem to suffer the same problem. This must be a deficiency in the FM algorithm.)

LaRetta, the first part of your comment won't work in my situation, if I understand you correctly. If I don't import their serial numbers, won't they be assigned new numbers by the 'perform auto-enter' option? If so, then any related file that references their Serial Number as a related field will loose track of them.

The second part of your comment is a great idea for me. It's especially helpful since I have realized that my entire worry that started this thread is a non-issue. As I use the solution -- in "developer mode" -- I have access to all of the menu items. But, because of restrictions that I've set for other password, regular users won't be able to select the Duplicate Record command anyway. So if they want to duplicate a record they'll have to do it through a script I provide. Can I see how you set this up?

Thanks again,

Dan

[Ugo DI LUCA]

One or 2 side questions here....

Max and Min definitely aren't choices here for sure, but in a multi-user scenario, and for my own knowledge, wouldn't there be a risk anyway that the same record be allowed if 2 users were creating a new record at exactly the same time ?

Would it be possible and more reliable to store any new record in a global field, in the Global File ?

About your way of duplicating, it also depends on your exact structure I assume. You could have them looked up from a Main Contact File, looked up from the previous record, conditionnally if needed.

I'd also like to see how LaRetta is doing it...

[BobWeaver]

Going back to the original problem, it appears that you have a main file with contacts, and a related file with addresses. Mr Smith has a record in the contact file and a related record in the address file. You now create a duplicate of the record in the contact file for Mrs. Smith. But, you keep the same address record in the address file. It seems to me that the place where you need to be creating the serial numbers is in the address file, and then assigning them to the contacts file records. And, in the contacts file these 'serial numbers' are not serial numbers, they are address reference keys.

So, what I would do is have an auto-enter serial number in the address file. Whenever a new unique address record is created, it gets a new serial number. Then have a script assign that number to the address reference field in the contact file.

[Ugo DI LUCA]

I'm afraid this isn't the structure Dan adopted but 100% this is the structure he should use...

I messed up with the "Main Contact File" in my last post. This was also my point in fact. You can even get more than one adress per customer this way.

That's currently what I'm using, and involving Dj's Renumbering portal technique, this allows to even pick the mail adress I'd like to use as a default adress (could be professional for Mr and Mrs, while having a home adress hided for other use).

Bob, could you enlight me a bit about the global vs Max solution in a Multi-User environment... ?

[BobWeaver]

Sorry, I misread Dan's original post. Don't know what I was thinking about (apparently very little) . I thought he wanted the serial number to duplicate, not the other way around. So, anyway Ugo, as you pointed out, if you are using an autoenter calculation then you will get a duplicate of the original record no matter what formula you use. If you are using an autoenter serial number, the serial number field will enter a new unique number every time.

The only autoenter options which generate new values are:

serial number

creation/modification date

creation/modification time

creator/modifier name

all other autoenter options will be overwritten with the data from the original record when duplicated.

Regarding the Max vs. global in a multiuser system, I wouldn't trust either one to generate unique numbers. I would go with an autoenter serial number (with suitable control to ensure that the next serial number value is set correctly after an import, and that users can't modify them), or better yet, Captkurt's unique ID method.

[DanBrill]

Hey guys,

Just one file here with both names and addresses. The serial is generated from an autoenter serial number, not an autoenter calculation. The duplication problem was, I'm fairly certain, caused by importing the records into an empty file and not resetting the serial number so that the next value would be Max+1 of the serial numbers that I had just imported. Once I ran the max+1 script, duplication provided unique serials for the resulting records.

I'm going to check out Captkurt's method too.

Thanks for all the help,

Dan

[LaRetta]

Hi Dan! Hi Ugo!

Sorry I didn't get right back to you. I swore off FM for a full 24 hours. I really needed a break as I was going brain-dead and forgetting important things, such as Round() rounding both ways as well as forgetting other basics.

But today, I'm back on it and I created a demo which shows how I prefer to address duplicating a record, empowering Users to select their own fields to duplicate, freeing Developer time and still maintaining the integrity of the UniqueID. It is set for multi-user, fully cross-platform and works with all versions of FM (well, except very old ones, I think). And, I hope the demo is clear enough that anyone (regardless of their current FM abilities) will be able to use it easily.

I've placed it on Andy Gaunt's FusionDZine website in the Downloads section here http://www.fusiondzign.com/.

The name is not original - it's just called Duplication and it is in the External Demos section.

LaRetta

[Vaughan]

"The duplication problem was, I'm fairly certain, caused by importing the records into an empty file and not resetting the serial number..."

Oh, I *hate* it when that happens! Done it too many times, too.

Now before I attempt an import I write myself a note in big letters "Reset Serial Numbers" and pin in out the wall behind the screen. I make a point of going through the field definitions to look for auto-enter serial numbers, and note their next values before starting.

[BobWeaver]

Yep. Been there, done that.

[DanBrill]

Good to know I'm not alone. Thanks for the demo, LaRetta. I'll have a look under the hood later today.

(BTW, LaRetta, saw your post on rounding. I think what you were thinking of is not that FM always rounds up, but that FM always rounds up on a .5. To be more accurate with rounding there's a math rule that says if the number to the left of the 5 is odd, round up. If its even, round down. This washes out the slight upward bias you get by always rounding up on .5. FM doesn't do this.)

Thanks to everyone for their help.

Dan

[Ugo DI LUCA]

Thanks for sharing LaRetta,

The least duplicate functions are used, the best I feel. I had terrible mistakes made in my old files, and I sweared to never use a duplicate script anymore.

But this could be another way to go when you have too, and less involved than conditional lookups.

[LaRetta]

Ugo said...The least duplicate functions are used, the best I feel.

Well, maybe the better YOU feel but the worse your Users will feel. Having an option, when creating a new record of having the system duplicate data chosen by a User and which would have to be retyped anyway is a real time-saver for data-entry.

It is no difference (and in fact much better) than the silly option of Auto-Enter of 'Value from Previous Record' which, IMO, is only good for inserting the current date. If Users are given a checkbox in which to select fields to duplicate, and Management feels they should have the option, I think it's a great thing to assist Users.

Any time my Users are presented with record creation and data-entry, I want their lives as pleasant as possible. Duplicating certain fields from a record they are currently viewing is quite safe - because they won't be duplicating fields they don't see and they won't be duplicating fields that FM (or a script) decides should be duplicated - only what they need. But that's just my opinion, of course.

LaRetta

[ESpringer]

LaRetta,

Why is the Enter-Previous-Record's-Value shortcut silly and only good for current date (which of course has its own key-combo)?

In the absence of an omniscient developer -- and perhaps this is precisely the issue for me -- this shortcut seems to be the best generic solution to the problem of how to:

(1) avoid unanticipated consequences of full duplicate; yet

(2) save data-entry time.

If I suddenly have several books from the same publisher, or several new members with the same address, or several transactions with the same party, isn't it useful to create a new record and zip through data entry on the relevant overlapping fields?

(I'm not asking rhetorically -- I'm constantly finding in this forum that things I regularly use are disparaged for good reasons.)

[Ugo DI LUCA]

LaRetta,

There's no duplication at all here. That was my point.

It's safe to do it your way.

I personnally use global fields for any new record creation, a globalID in case I need to get data from a given record, other global fields and related fields.

It's safe too but surely more involved.

Now, it's pretty rare I'd use it. But this surely depends upon the kind of solutions you're developing.

[LaRetta]

Hi ESpringer, My point on Auto-Enter, which probably wasn't as clear as I would have liked is that, with Auto-Enter pre-determined on a specific field, it may be useful but it may (just as likely) be a pain. And, if it's pre-filled, the User may not notice that the data should in fact be different until several records later! Asking a User to select the fields to duplicate frees them from unncessary work while still being sure that the data inserted is, in fact, what the User wants in the field. Because if a field is filled in by FM (via auto-enter) a User will tend to 'not see' it. I've used Auto-Enter for data-entry with over 200 heavy data-entry (8-hrs day) Users (using Access, Approach and now FM) for the past 15+ years and can say that it can be quite frustrating for staff. I prefer a compromise - make them choose the fields to duplicate (so they think a second) but let FM do it for them (so the data is consistent). There are exceptions of course - such as Current Date, etc.

Hi Ugo! "There's no duplication at all here." Not sure I follow you. Dan's original request was two-fold; problems associated with duplication of his UniqueIDs AND duplicating certain fields (on occassion) when they were (for instance) husband & wife, etc and he wanted to insert the same address. That's what I was trying to get back to for him. How he could handle duplicating certain fields 'when he wants to' but not duplicating them at other times. And, letting FM duplicate a field will give the exact same address instead of one with a period between S.W. and the other without ... or one with a zip code error, etc. It's a matter of consistency and ease for the User. Just a different perspective, I guess.

LaRetta

[ESpringer]

Oh, silly me. I was thinking of the enter-last-value hot-key, not the field definition option to Auto-Enter Previous... Indeed, *that* smells like trouble except when there are bound to be long runs of the same value, and it's a key field not about to be overlooked...

I do think that telling users that they can repeat their last entry in a field with cmd-'/Ctrl-' is useful; it takes away some of the temptation to duplicate records (if that's allowed at all), and helps solve the "Ave SW" vs. "Av S.W." (etc.) problem...

[LaRetta]

Hi ESpringer,

The keyboard command CTRL & ' will duplicate the field contents ONLY from the LAST record that was accessed by the user. Therefore if the user just navigates to a record, then creates a new record without entering that record, that is not the last record seen by the system and therefore the contents from that record are not duplicated but could be a different record. and if the User just signs on and is viewing a record, it won't at all because there was no 'last record.'

My solution (which, as Ugo points out, is actually not a duplication as such), is that it solves this so that the last record they looked at is the one where the information is duplicated - and only what they choose and several fields at once if they wish. Again note - the keyboard shortcut CTRL & ' will not return ANYTHING if the user just opens the database and does not navigate into a record by selecting a field.

Actually, my process could be attached to a NEW RECORD button just as well. It just allows Users an alternate approach - if they don't click any fields to duplicate in the checkbox, only a new 'blank' record is created but, if they are viewing an existing record and WANT to duplicate part of the contents, they have that option also. It doesn't depend upon there 'being' a last record. Users are used to 'new record' being a new blank record, whereas 'duplicate' record' means duplicate all (or parts) of the existing record. That's why I keep it as 'duplicate record' for clarity. It just seems to solve all of these problems at once and Users don't need to learn different shortcut keys or risk inserting data from a different record.

LaRetta

[Duane]

I know this is a very old thread, but I have the same problem. I am not following how the self join is being used with Max. How is the self join relationship setup so that Max will be calculated for more than one record.

I setup the obvious self join and all I get is max of the current record. That is max of one value which of course is the value. I guess I am missing something about how max and the self join work together.

Thanks for the education.

...Duane

[ESpringer]

Duane,

The word "self-join" might be used in a couple different ways: one to mean the current record always joined to itself (perhaps with some additional condition), and another to mean a Cartesian join of all records within the same table (FM7) or file (FM6), or some variation on that.

For the serial number trick, you need the Cartesian kind: a relation between each record and every other record of the same table/file. To do this in FM6, you need a constant (a field which has 1 in every record) to serve as the left and right key. In FM7, you can just use the X connection instead of the = match connection.

[Duane]

Thanks! That makes sense now. That's why Max() actually works.

Thanks for the quick education.

...Duane

[bruceR]

In FM7 you can use globals and lookups to deal with this problem. Globals can now be lookups! And a lookup next-lower is WAY faster than the max/min calc. For instance try this.

Add 2 fields, gBigNum and gGetNextSerial, both global number fields (this is all assuming your ContactID field is a number). Create a relation from gBigNum to ContactID. Set gGetNext Serial to be auto enter calc, replace existing value, lookupnext(Relation::ContactID; lower)

See attached.

GetRecID.zip

[Ugo DI LUCA]

Bruce, this is just awesome.

I was playing with the concept for a full week right now, until I saw a reference to this by Fenton.

I had the "Do not evaluate" checked Doh !

This surely opens the way for some major rethinking.