Unexpectd Lookup and othe wow/odd behaviour

[Ugo DI LUCA]

Hello,

Proceeding with some tests on the Relational Architecture (specially multi-joins and auto-creation of related records discussed elsewhere these days), I discovered something I haven't seen reported elsewhere.

With the multi-joins capabilities with 7, you can fill a few globals in the Main file and have them populate fields in a related record on creation when the relationship is set to allow creation of related records.

This is a big change as it reduces the needs of defining fields as lookups or auto-entered calcs, when you want them to be dynamically filled with values from this Table *where* they were generated (multiple Tables as opposed to a fixed Table "lookup")

Though, this requires a bit of attention, as some expected and odd behaviors might happen...

Say I want to create a related record in RelatedTable and fill a numField at the fly from a global (num) in MainTable.

The relationship, set to "allow creation of related records" use a double equijoin MainTable::mainKey = RelatedTable::parentKey AND MainTable::gNum=RelatedTable::numField, and a script is used to create the related record.

SetField[RelatedTable::parentKey;MainTable::mainKey]

Provided a value was entered into the numeric global field, a new record is created. If not, you will get a message that no "modification" can take place before you give a correct value to the global, which is to be expected.

If you try this same script again, with the same value in the global field, you will notice that no new record will be created and that your earlier record is still there.

You may think at first that creation of related record is *locked* because of an existing match, but in fact this record *is modified* as would attest :

- the introduction of a ModTime field in the related Table.

- a portal from this relationship, entering directly the parentkey value into the new blank line will effectively create this duplicate record actually.

- displaying the parentkey as a related value in the MainTable layout, and entering the currentID into this new field will produce another modification to the first related record.

There may be situation where you'd want to create duplicate entries though, could it be that you want a pure duplicate or that you want some "same" keys to be entered into multiple entries, which was my case when I tested.

The first solution I found was to force FM to mismatch any old entries by adding a random key in the related record and using a gRandomKey as another key for the relationship. I wasn't very happy with this as 2 additional fields were needed as well as a script step to set this gRandomKey.

The solution I found today is rather "odd" enough to be reported, and may be had it been already here or elsewhere.

If you enter a text string in the numeric global field, you'd be able to create any duplicate instances of records.

There's low chance that you'd ever enter a text value in a numeric value for those of you who would like to prevent these duplications, but I felt like to mention either the "tip" and the "warning".

Another "oddly" behaviour now is the following.

Instead of displaying the parentkey from our relationship, change it to be the numfield from the RelatedTable to be displayed and create a totally new record in MainTable. Fill the global field with say 10.

Entering "10" in the related numfield does create a new "first" record in the Related Table, which is expected behavior, but here comes the "wow" part (at least for me)...

Create another record in MainTable, and delete the global value for gNumField. Enter 15 into the related field and you'll notice that the global field is now filled with "15" and has been looked up, without any need to define it as such.

I have no idea at the moment what this could be useful for, but again, I felt like to report this unexpected lookup behavior.

I did try with other settings, and it seems that this is a new feature in 7 :

When a relationship is set to allow creation of related records with a multiple equijoin including a global field, and when the global field is left empty, entering directly the match key field, either in the portal or from a related field will cause the global key to be looked up from the first value entered in the portal or the related field. The same behavior happens with standard indexable field as FM7 relationships are bi-directional, but I wouldn't have expected this to happen with globals.

Proceeding to the settings for related auto-created records, I also noticed another thing.

Say you add a gTextValue in the MainTable and a textField in the RelatedTable and add these keys as a new equijoin in our previous relationship.

We therefore now have 3 equijoins.

Drag the related field RelatedTable::textField on the layout.

In order to see what happens, create an equijoin relationship from mainKey to parentKey into a new occurrence (CheckingTable) of the related Table, and draw a portal with this relationship.

Fill the new global gTextValue with say "Hello" and gNumValue with any number (Note that you can still have the global looked up as with my first comment, even with a triple equijoin)

Create a new record in MainTable.

Use the previous script to create a new related record that will auto-fill with "Hello" and your number value.

Now, enter "Change" into the related field RelatedTable::textField. The record disappears from our portal from RelatedTable TO and appears as a record in the portal from our new TO. This is expected behaviour, as we changed the value of a matching key.

Enter "Other" into the now empty related field RelatedTable::textField. As there was no matching record from our 3 equijoin keys, a new record gets created using the value from the global field, "Hello", ignoring the input into the related field.

There's now a match for these keys.

Change "Hello" back to "Other" and you'd get a new record created with "Other" and visible in the portal from CheckingTable. Again, no record visible in the RelatedTable portal.

Enter "New One" into the empty related field RelatedTable::textField. Again, FM ignores your entry and proceed to a record creation from our equijoin with "Hello" and your global number value.

Change" Hello" back to "New One" and the new record appears in the CheckingTable portal.

All this tend to demonstrate that when using multiple equijoins set to allow creation of related records, you can create related records either by a change to the globals or by a change to one of the related record match key.

I hope this wasn't redundant. I confess I haven't read much posts these last months as I was pretty much busy.