1 2 Previous Next 17 Replies Latest reply on Jun 29, 2014 2:36 PM by keywords

    Record Creation via Relationship - Oddity When Auto-Enter Involved

    kcunning

      This one took a a while to even figure out what was happening. It's a

      little tricky to explain, but it's entirely reproducible, and it has

      a side effect of regularly creating extra "blank" records that repeat

      existing id numbers. This is causing chaos in my solution.

       

      FileMaker 12 on Mac.

       

      Summary: I have three tables -- OUTER, MAIN, and DATE.

       

      OUTER is related to MAIN via an "id" number field.

       

      MAIN in turn is related to the DATE table by two separate relationships:

       

      "main_to_date2" joins calc2 (=2) in MAIN to "typenum" in DATE

      "main_to_date3" joins calc3 (=3) in MAIN to "typenum" in DATE

       

      (Basically, I want to set a type number at the time I set the date so

      I have two different kinds of dates and can manage them distinctly.)

       

      On the OUTER table's layout, I place two fields:

       

      main_to_date_2::date

      main_to_date_3::date

       

      If I create a new record in OUTER, then enter date values into the

      two fields I put on the layout, I expect the system to create one new

      MAIN record (the join), then two new DATE records (one with typenum 2

      and the other with typenum 3).

       

      This is in fact what happens in the usual case.

       

      HOWEVER...

       

      If I have set an auto-enter condition on the "typenum" field in the

      DATE table (e.g., Auto Enter data = "1", say), the system again DOES

      create the two DATE records as expected (typenum = 2 and 3

      respectively, NOT the auto-enter value of 1), BUT it creates *two*

      records in the MAIN table. The second record is not a duplicate of

      the first record, but a newly-created join record, with the same id

      number pulled in from the OUTER record, as if the system had to

      create a whole new path through MAIN to get to the DATE table when I

      create the second DATE record.

       

      Once the DATE record is created, I can edit it, etc., via the

      relationship and no additional middle record is created based on that

      relationship, so it only has to do with the creation step. And as

      soon as I try to create another DATE record via a *different*

      relationship, it adds a new MAIN join record.

       

      (In my case, I actually have *many* of these "main_to_date..."

      relationships -- a whole series of typed date fields on one layout --

      and a new MAIN join record is created every time a user newly enters

      a date in a particular typed entry field because the key field has

      the auto-enter condition on it.)

       

      (I should also point out that creating the DATE records from a layout

      based on MAIN does NOT have this issue. It has to do with threading

      the relationship across the two jumps.)

       

      I could see the behavior going either way. I just didn't expect it to

      be different based on whether an auto-enter is set. And note that the

      end result in the DATE table *is* the expected behavior -- it's just

      the extra superfluous MAIN join records that strike me as odd.

       

      IS THIS EXPECTED BEHAVIOR OR A BUG?

       

      [If you think to say "just turn off auto-enter, you simpleton!", let

      me explain that if a user goes directly to the DATE table (on a

      different layout based directly on DATE), I want a newly entered

      record to default to typenum=1, so naturally I hoped to retain

      auto-enter -- of course, the workaround appears to be to unset

      auto-enter and have a trigger script on that layout to set the

      typenum value to 1 if I want, or to run a trigger script in the cases

      I reported above and have that script remove any redundant join

      records. But my question is about whether this is expected behavior

      or a bug, not how to solve my problem per se. I have solutions now

      that I can see what's happening. My question remains: does this seem

      like the expected behavior, or is it off? Any explanation that would

      elucidate why it's expected?]

       

      Thanks,

      --Kevin

      --

      Kevin M. Cunningham, M.Ed.

      FileMaker 13/12/11/10/9/8/7 Certified Developer | FileMaker TechNet/FBA Member

      (617) 229-5081 | kcunning@alum.mit.edu | www.kcunning.com

        • 1. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
          EvanGoldstein

          Hi Kevin,

           

          Is the value of "1" in the Auto Enter tab specified by using the "Data" or the "Calculated Value" section?

          If it is using the "Calculated Value" section, then perhaps the context is using one of the relationships instead of just the TO of "DATE".

           

          Evan

          • 2. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
            kcunning

            Good question. I happen to set it by the "Data" auto-enter option,

            but "Auto-Enter calc" also produces the same result, no matter what

            the TO context or the calculation in that option.

            • 3. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
              Mike_Mitchell

              Kevin -

               

              When these "extra" records are created, what is the value in the "typenum" field?

               

              Mike

              • 4. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
                Mike_Mitchell

                Never mind. I see it in your post.

                 

                What I suspect is happening is that the auto-enter is colliding with FileMaker's attempt to insert the key field value in the related record. Try this:

                 

                Instead of auto-enter of "1", use a calculated value of:

                 

                     Case ( IsEmpty ( Self ) ; 1 )

                 

                See if that fixes the issue.

                 

                Mike

                • 5. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
                  kcunning

                  I think you're right that there is a collision.

                   

                  Turns out it doesn't matter what I have in the auto-enter calc field,

                  the fact that I have SOME auto-enter option forces the creation of

                  the intermediate record. Even auto-enter "Creation Date" or "Serial

                  Number", admittedly a stupid option in this case, causes the problem.

                   

                  Thanks,

                  --Kevin

                  • 6. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
                    kcunning

                    For those interested, a small file that demonstrates the issue is available online at:

                     

                    http://web.mit.edu/kcunning/www/relationship_issue_demo.fmp12.zip

                    • 7. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
                      keywords

                      I don't think the autoenter option is relevant; record creation via the relationships will override whatever autoenter is set. The issue lies with how the records are created. On your demo file ther are two records created, one for each relationship path. When you enter a date in the "main_to_date_2::date" field you are entering a value in a field in the date table, thus creating a date record, but this in fact also creates a record in the main table as well, presumably because of the way the relationship path is structured. When you enter a date in the "main_to_date_3::date" field another record is created in each table.

                      • 8. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
                        kcunning

                        Hi there,

                         

                        I would have thought autoenter was irrelevant as well, but if you

                        redefine the destination field to have no autoenter and change

                        nothing else, the behavior is different (no second middle record is

                        created in that case) while the relationship path is identical. That

                        suggests that the autoenter option is specifically relevant. Why, is

                        my question.

                         

                        Thanks,

                        --Kevin

                        • 9. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
                          keywords

                          So it does! Weird. Can't answer your Why question off the top of my head, but will certainly ponder it and post again if I think of anything.

                          • 10. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
                            alquimby

                            Kevin,

                             

                                 In your main to main_date_2 relationship, how can the value of c2 (2) ever match the typenum value (1)? Same with c3 in the main to main_date_3 relationship? I don't know why you want typenum to be 1, but it looks like you are giving FileMaker conflicting instructions that it can only resolve by having typenum equal c2 and c3.

                             

                            Al Quimby

                            • 11. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
                              kcunning

                              Not exactly. Naturally the sample file is streamlined to point out

                              the technical issue, but I think the intent is pretty standard.

                              Though it's not exactly germane to the issue I pointed out (the

                              creation of an extra "middle" record depending on the setup), I'll

                              explain my purpose here...

                               

                              There are three relationships from the MAIN table to the DATE table,

                              all based on an id match, and all allowing CREATE from the MAIN side.

                              Two of the relationships additionally have a match field that will

                              set the "typenum" foreign key when you create a record from the MAIN

                              side:

                               

                                 1) main -> date = match id

                               

                                 2) main -> main_to_date_2 = match id, also match c_2 to "typenum" field

                               

                                 3) main -> main_to_date_3 = match id, also match c_3 to "typenum" field

                               

                              On a layout based on MAIN, I could place:

                               

                                 a) a portal based on relationship 1) to show all the related dates

                               

                                 b) a field based on relationship 2) to show the first related record

                                    that has typenum = 2

                               

                                 c) a field based on relationship 3) to show the first related record

                                    that has typenum = 3

                               

                              Why might I want this?

                               

                              I label field b) "begin date", and I label field c) "end date", and I

                              label portal a) "All Dates".

                               

                              Now if I type a date into the displayed "begin date" field (b), it

                              creates a new DATE record, with typenum = 2. This date will also show

                              up in portal a) because portal a) shows all related DATE fields,

                              regardless of "typenum". That's fine.

                               

                              Now, I would like all dates to have a "typenum" so I can keep track

                              of them separately. If typenum = 2, it's a begin date, if typenum =

                              3, it's an end date, but what about the others? Yes, I could leave

                              their "typenum" value blank, but I would prefer to have an actual

                              typenum. That's why I put in the auto-enter value of 1 (for

                              miscellaneous or "other").

                               

                              What I want, in other words, is that if I enter a date via portal a),

                              it still has SOME typenum value. Or, if I go over to the DATE table

                              itself (i.e., work from a layout based on DATE), I want new records

                              to be given a typenum by default.

                               

                              That does not seem to me farfetched.

                               

                              And given this setup, my expectation was that, if I create a new

                              "begin" or "end" date, the new record ends up with the typenum

                              specified in the relationship (2 or 3) -- which it in fact does.

                               

                              The only issue -- the one I raised on this list -- is that, oddly,

                              specifying an auto-enter option in the field definition also forces

                              FileMaker to create a new intermediate record if called from one node

                              further along a relationship path -- a behavior it does NOT do if

                              there is no auto-enter logic specified. This strikes me as an

                              inconsistency in FileMaker's logic, and I sought/seek understanding

                              about it.

                               

                              In any case, I don't think I am giving FileMaker "conflicting

                              instructions", but  relying on precedence etc. to get to a final

                              result (as one might with CSS, for instance, or with the style stuff

                              in FileMaker) -- and I do in fact get the result I expect, except for

                              the bothersome extra middle record. You may be right that FileMaker

                              gets confused by this cascading logic.

                               

                              Thanks,

                              --Kevin

                              • 12. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
                                BruceRobertson

                                It is what it is. FileMaker is not confused.

                                 

                                The behavior is repeatable and expected and consistent.

                                • 13. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
                                  Mike_Mitchell

                                  Bruce -

                                   

                                  Can you elaborate a bit on the mechanism? I’d be curious to know what the logic for this is.

                                   

                                  Mike

                                  • 14. Re: Record Creation via Relationship - Oddity When Auto-Enter Involved
                                    alquimby

                                    Kevin,

                                     

                                    (I should also point out that creating the DATE records from a layout

                                    based on MAIN does NOT have this issue. It has to do with threading

                                    the relationship across the two jumps.)

                                     

                                        

                                         I've always found that if FileMaker doesn't follow "my" logic, it is my logic that needs to be changed. Without knowing the full scope of your databases that use these Main to Date relationships, it seems that the Outer table serves no purpose other than to create a primary key, which goes to the Main table. But that key becomes the actual primary key in Main (where you only have 1 key) that, in turn, relates to the foreign key in Dates. The Outer table is creating the 2 jumps––why have it?

                                     

                                    Al

                                    1 2 Previous Next