Cartesian Join causing cross linking?

[ron G]

I am using fm12 04 advanced.

 

I have  Membership application.

There is a Membership table.

On the main layout, (based on Membership) I put a portal on the left with a MembershipTOC called MembershipPortal.  MebershipPortal is related to Membership by a X between PK_MemberID and PK_MemberID.

 

 

The problem:

When I run this little 'add new record script' it adds a new record BUT, it also assigns (somehow) that new record number to an existing record; thereby cross linking them.  Huh?

 

 

I can't see anything wrong.  Can you?

Thanks for your help.

 

Perplexed Ron

[eos]

What has the relationship or the portal to do with anything? You're not using related values or going to a related record in that script snippet, you're clearing some $$s and selecting a window  – which may or may not exist at that moment, with all that ensues.

 

Did you run the script with the Debugger?

[ron G]

What has the relationship or the portal to do with anything? You're not using related values or going to a related record in that script snippet, you're clearing some $$s and selecting a window  – which may or may not exist at that moment, with all that ensues.

 

Did you run the script with the Debugger?

Yes, I ran the script with Debug.  When it gets to the 'Add New Record' it adds a new, blank record (I can see it in the MembershipPortal) and if I stop the script at that point and scroll down the MembershipPortal, I can see that an existing record has had it's PK "reassinged" the same 'new' PK given to the new record.  This is so weird....

[eos]

This is so weird....

 

It usually isn't. Why don't you post the complete script? btw, how do you trigger the script? And is there a difference between Member and Membership?

[ron G]

It usually isn't. Why don't you post the complete script? btw, how do you trigger the script? And is there a difference between Member and Membership?

The script you see IS the entire script.

It is triggered by a "Add Member" button.  Pretty straight forward.

 

I don't understand the nature of your query about the difference between Member and Membership.  What is your reference?

 

Thanks for trying to grock this...  Ron

[Fenton]

I don't really understand what is going on either. But the first thing I'd think of is whether the field itself is set to Lookup a value on creation?

[ron G]

I don't really understand what is going on either. But the first thing I'd think of is whether the field itself is set to Lookup a value on creation?

The PK_Member field is, as is typical, a Numeric field with a Serial on creation.

[Fenton]

OK. Yes, you said that the new one got the correct ID earlier, so that all makes sense. But, as you say, another record getting the same ID, overriding an existing ID, is quite odd. 

 

I think, as "oes" says, it would be best at this point to look carefully at the basics. Go to the actual base layout of the table, then create the record, and see if the same thing happens. (And return to the layout you want afterwards, if desired.) 

 

There is something odd about this. I've never seen it happen myself, so can't really guess what. (I have seen FileMaker create an extra record, when a Relationship required 2 fields to need value at once, and "allow creation of records" was turned on in a Relationship; but that's different.)

[ron G]

Yes the situation is odd. 

Here is some more background.  I release the app with 'dummy' records so that new users can play with it and get an idea of how it works and what the reports are like.  Eventually, the users delete the dummy data and add their own. 

 

When I release a 'new' version, they import their previous version.

 

The 'odd' situation occurs when one particular user sends me his fmpur file.  Sure enough, after I import it, and when I add a new record (see script above), when the Add New Record line is executed, it adds a new record with the next serial but also attaches that serial number to an existing record; thereby cross linking 2 records.

 

If I add another new record, it may or may not, cross link.  The cross linking is, I think, totally random and unpredictable.  Also, if I import my dummy data back into the app, it NEVER happens.

 

So, one question on my mind is "Is there some way that existing data can be corrupted to make itself susceptable to cross linking like this?  If so, what would cause the corruption?  (Sorry to be so general I just don't have a clue)

 

thanks

 

RON

[ron G]

Sep 1.

 

In the above .jpg you see on the left the Members_Portal  (A TO of Members) with Members on the right.

(Members_Portal is a navigation system using GTRR to bring up the full Members record)

 

There is a Cartesian Join between Members_Portal and Members (PK_MemberID x PK_MemberID)

 

It seems to be working (for the last 6 hours).  All I did was slightly repair the portal filter: (below)

 

I get how having incorrect Portal Filter code can screw up the 'display' of records.  But, the question is:

 

"If the Portal Filter code can cause an existing record to have it's Primary Key overwritten when a 'New Record' is produced, is that a 'fault' in Filemaker portal filter functionality?"

 

=========FILTER CODE===============

//===>>> All - (Active and Inactive) (Candidate and Member)
Case ((Members_portal::gBtnActiveOrInactive="Active¶Inactive" or  Members_portal::gBtnActiveOrInactive="Inactive¶Active" ) and (Members_portal::gBtnMemOrCan="Candidate¶Member"  or Members_portal::gBtnMemOrCan="Member¶Candidate");Members_portal::Mem_Can="Candidate" or Members_portal::Mem_Can="Member";
//=====================================================

//Both - (Active and Inactive) Member
(Members_portal::gBtnActiveOrInactive="Active¶Inactive" or   Members_portal::gBtnActiveOrInactive="Inactive¶Active" )  and Members_portal::gBtnMemOrCan= "Member"; Members_portal::Mem_Can="Member";

//Both - (Active and Inactive) Candidate
(Members_portal::gBtnActiveOrInactive= "Active¶Inactive"  or   Members_portal::gBtnActiveOrInactive="Inactive¶Active" ) and Members_portal::gBtnMemOrCan="Candidate"; Members_portal::Mem_Can="Candidate";

// Inactive (Member and Candidate)
(Members_portal::gBtnActiveOrInactive="Inactive") and (Members_portal::gBtnMemOrCan="Candidate¶Member" or Members_portal::gBtnMemOrCan="Member¶Candidate" ) ;Members_portal::Status<>"" ;


//Active  (Member and Candidate)
(Members_portal::gBtnActiveOrInactive="Active") and (Members_portal::gBtnMemOrCan="Candidate¶Member" or Members_portal::gBtnMemOrCan="Member¶Candidate" ) ;Members_portal::Status="";

//---------------------------------

// Active Candidate
Members_portal::gBtnActiveOrInactive="Active" and Members_portal::gBtnMemOrCan="Candidate" ; Members_portal::Status="" and Members_portal::Mem_Can="Candidate";

// Inactive Candidate
Members_portal::gBtnActiveOrInactive="Inactive" and Members_portal::gBtnMemOrCan="Candidate" ;Members_portal::Status<>"" and Members_portal::Mem_Can="Candidate";

// Active Member
Members_portal::gBtnActiveOrInactive="Active" and Members_portal::gBtnMemOrCan="Member" ; Members_portal::Status="" and Members_portal::Mem_Can="Member";

// Inactive Member
Members_portal::gBtnActiveOrInactive="Inactive" and Members_portal::gBtnMemOrCan="Member" ; Members_portal::Status<>"" and Members_portal::Mem_Can="Member"

[eos]

"If the Portal Filter code can cause an existing record to have it's Primary Key overwritten when a 'New Record' is produced, is that a 'fault' in Filemaker portal filter functionality?"

 

No; it is called flawed code, which usually is the fault of the person behind the keyboard.

 

On a related note, how about using something more elegant than a brute force approach? (But if you must, use at least Let() to simplify the whole thing a bit.)

 

Less code = fewer opportunities to make mistakes.

MemberFilter_eos.fmp12.zip

[ron G]

I think it is fixed.

 

It turns out that the IMPORT code needed to look at the highest primary key in the imported data then Set Serial to the next highest value.  This prevents FM from being 'confused' and using what it thinks is the next highest serial; which sometimes turns out to be a value that is all ready assigned to a record.  Hence the cross linking....  Here is the import code that seems to work:

 

 

I still don't toally 'get' what affects "Perform Auto Enter..." has on Parent and Child records... but for now, this seems to make everything work as expected.  (fingers crossed)

 

Thanks for all your insights and suggestions.

[ron G]

No; it is called flawed code, which usually is the fault of the person behind the keyboard.

 

On a related note, how about using something more elegant than a brute force approach? (But if you must, use at least Let() to simplify the whole thing a bit.)

 

Less code = fewer opportunities to make mistakes.

I forgot to shout out a special "Thanks!" to eos for supplying me with an example that shows how to use the Let statement instead the much less efficient Case statement in my Filter.  I am not well versed in Let but think with the supplied example, I will finally 'get' how to use it.  Thank you eos!!!

[eos]

Ron –

 

glad to help, but there seems to be a misunderstanding:

 

Case () isn't “much less efficient” than Let () – they're totally different beasts, and one doesn't replace the other; they work great in tandem. Actually, Let () works with any function; that's its raison d'être.

 

For a fun afternoon read: http://fmhelp.filemaker.com/fmphelp_12/en/html/func_ref3.33.16.html#1028226

[ron G]

eos,

Thanks for the link. 

Yes, I am going to make myself suffer through learning the syntax of Let.... And your filter Let examination is a good way to do that.  I appreciate your help.