Cross Linked Primary Keys

[ron G]

My free Membership app is on the web and presumably being used by thousands of users.

 

I have one user who periodically gets two members to cross link their records by having the same PRIMARY KEY.  (I put the PK on the layout so I can watch it as I go from record to record.  Yep, the same key (2777) for each member.

 

Of course this means the 'One' table (Members) holds unique information but that each 'many' table is the same for both cross linked members.

 

I have checked all the relationships and made sure that the Member table is not able to be updated.

 

I have checked the Members table and made sure it can not be modified.

 

 

I do have a cartesian join table that looks like this:

 

 

But if this relationship was the problem, wouldn't the expression of the problem be more prevalent than just two records?

 

I can not recreate this situation.  The user can not remember what he was doing to recreate it. 

 

Has anyone had this experience and solved it?

 

Thanks for reading...

 

Ron

 

[David Nelson]

Sounds like somewhere the ID is on a layout and they can enter the field so they changed it.  Do you have 'prohibit modification' in field definition and also set it to unique?  Just an idea.  I know what it's like to get stuck on these things and good luck to you on solving it!

[ron G]

Sounds like somewhere the ID is on a layout and they can enter the field so they changed it.  Do you have 'prohibit modification' in field definition and also set it to unique?  Just an idea.  I know what it's like to get stuck on these things and good luck to you on solving it!

 

Nope.  The PK is never on any layout so the possibility of a user changing it is zero.  Yes, I have prohibit modification turned on.  I figure it is a script or a relationship.  I checked all the relationships.  So, I conclude it is a script where I somehow specified PK instead of FK perhaps.  But, putting the lie to that is the fact that I can make hundreds of entries to the program and never occurs......  I am baffled and thought that someone might have had a similar experience in which case they can show me some 'light'.

 

Thanks for the comment.

[Vaughan]

When weird things like this happen to me (typically users report that new records seem to be displaying old data in portals) it's because I've done an import or something and not updated the next serial numbers in primary key fields.

 

Eg: in main table, next serial number is 101. Import 100 records process the data, create new primary keys, etc which now go up to 200. Forget to update the next serial number. User creates new record (pk=101) and it matches old data.

 

So I'd start by checking all serial number key fields and making sure that they are not creating duplicate ehtries, or doing anything else unexpected.

[ron G]

I *do* have a scripted import function but it a) goes to each layout/table and deletes all records then imports the source tables.  Plus, the .fmpur file that the user sent me only has 2 records cross linked even thought he added numerous records since the last import.

 

He can not reproduce the problem and neither can I.  Yep, these 'inconsistent' , irregular bugs are the worst!

 

Thanks for the thoughts.

 

Ron

[Wim Decorte]

 

 

He can not reproduce the problem and neither can I.  Yep, these 'inconsistent' , irregular bugs are the worst!

 

 

Not sure how to say this in the nicest way possible, because I was exactly like you when I got started; but don't cry wolf too early.  These almost never turn out to be bugs but the result of a misunderstanding of the features.

 

So prepare to raise your game before you're prepared to call it a bug.

[ron G]

Not sure how to say this in the nicest way possible, because I was exactly like you when I got started; but don't cry wolf too early.  These almost never turn out to be bugs but the result of a misunderstanding of the features.

 

So prepare to raise your game before you're prepared to call it a bug.

Easy there Red Rider.  I not referring to a bug in FM.  I was referring to bug in my code; my understanding; my logic....

[ron G]

Update:  A 2nd user has sent me their .fmpur file with 2 cross linked records. 

I suspect the problem is in the Import script.

 

First it opens a Members layout and then deletes all the records.

Then, (after it deletes all the dependent tables) it goes to the same layout and Import Records [No Dialog]; "$$path"; Add; Dos OEM]

All the fields are specified to Arrange By Matching Names

... and Perform Auto-Enter options... is NOT checked.

 

I tried checking "Perform Auto-Enter" options on the 'one' Members table but PK didn't seem to flow to the 'many' tables FK and seemed to get screwed up.

 

So, the question is:  "Is there a way to set import script parameters to regenerate the Primary Key for the 'One' table AND regenerate the dependent FK in the 'many' tables?

 

Thanks

Ron