This is similar to a question I posed in the General FM 7 Forum about two weeks ago. It's so disturbing I have to ask again.

I have attached an FM7 file that uses a simple schema for creating related records via globals.

You set a global and then created related records via portals. So you can create a record in Table 2 based on the global in Table 1 (which is itself set from a record's key value). Created record in Table 3 is based on global in Table 2 (corresponds to a record). In this example there is no creation of Table 1 records via its own portal.

I'm using auto-generated primary keys for my three tables. And here's where things get ugly.

If I set the key to auto-enter (all tables) by serial value, things work fine. Records are created properly.

However, if I set Table 2's key to auto-generate by calculation (via a relationship: max of all key values + 1), a bizarre thing happens. When I create a record in Table 3 (based on Table 2 record/global), it auto-creates a related record in Table 3 AND in Table 2. It doesnt' matter in Table 3 if I auto-enter the key from a serial or a calculation.

When I reported this before, I was told it might be a bug. This would be very bad; I'd rather be shown to be doing something wrong.

It may just be that I'm not thinking clearly about what's happening in the background.

If it is a bug, how can one let the Filemaker people know about it? This seems like a particularly nasty surprise.

I would very much appreciate any thoughts on this. The attached file, while not pretty, should demonstrate this oddity fairly clearly.

Thanks.

strangetest2.fp7.zip

Cellmaker,

You've already heard my opinion of this in the other thread.

Bugs can be reported at:

http://www.filemaker.com/company/product_problems.html

To get there again, from FileMaker's site choose:

"Support" -> "Speak with an expert" -> "contact us" -> "Report product issues"

("you are in a maze of twisty passages...")

I don't know what is happening here, but I recommend that you use the auto-entered serial numbers. No telling what kind of problems you might have if you try this in a multi-user setup.

Well, the thing is that my "real" solution (which also exhibits this behavior) will probably be deployed in multiple (unwired) locations, with eventual aggregation of the data across locations. So I'll probably need to set keys via some sort of calculation and not just have auto-entered numbers that will doubtless be duplicated among the various locations. I don't want to ask users to hard-code the serial number starting points.

It's a bizarre situation, in any event. I have reported it to Filemaker. (Thanks for the link, Shadow.) I'm hoping they will point out that it's not a bug and give some indication as to what is happening to cause this phenomenon so I can avoid it and similar problems in the future.

Using a text serial number key you could add a prefix to serial number for each location. Another thing I would recomend is that you do not allow the creation of related records. Use a button and a script to do this. Your setup is a little unusal.

Here's the calc I use instead of the auto enter serial number, just replace "LineItem#" with a locationID code. Also, don't use a calc field, instead use a Text field that Auto Enters Calculated Value, you may want to not check "Replace Exisiting Value" so if you import, the ID will not be over written:

"LineItem#"&

Right( "000000" & Get(RecordID); 6) &

Middle("0123456789ABCDEFGHIJKLMNOPQRSTUVXYZ"; Round(Random*35;1); 1) &

Right( "00000" &GetAsNumber(Get(CurrentTime)); 5) &

Middle( "0123456789ABCDEFGHIJKLMNOPQRSTUVXYZ"; Round(Random*35;1); 1) &

Right( "000000" & GetAsNumber(Get(CurrentDate)); 6) &

Middle( "0123456789ABCDEFGHIJKLMNOPQRSTUVXYZ"; Round(Random*35;1); 1)

Ok... I figured this thing out. It tooka good 20 minutes of head scratching and a two full beers, but I got it.

First off, that mysterious record that's created in the Level 2 portal isn't actually a new record. You'll notice that it looks like a blank record with the exception of the Global Key (gl_PKey_Level2).

This isn't actually another record, it's an optical illusion. Because you've got the relationship to allow creation of new records, you end up with the look of a blank record in the last row of the portal. The only reason, that the Global Key contains a value is because it's a Global Key - it's not a new record yet. You can fix this illusion by removing the Global Key from the portal layout and all will look and act fine.

If you are hell bent on viewing this value in the portal, create another field (not global) that simply returns the value of "gl_PKey_Level2".

You can demonstrate this by deleting all the records in all levels and starting over. You'll notice that as soon as you create a new record in Level 1, it appears as if there's a record in Level 2. But when you actually go to that Table, you'll notice that there are 0 records in table Level 2.

Hilarious. Unless I'm still confused, the update seems to have fixed the problem. Thanks, FM.

Actually, I was getting real records in level 2, not the illusion of them, but that's neither here nor there now.

Thanks for taking a hard look at it.