6 Replies Latest reply on Jun 29, 2010 7:22 PM by eibcga

    portal records disappear

    eibcga

      Title

      portal records disappear

      Post

      I have the following tables and relationships (irrelevant fields are not mentioned to save time):

       

      NAMES

      pk_name_id link to JOURNAL::fk_name_id

      name

       

      JOURNAL

      pk_trans_id link to LEDGER::fk_trans_id

      fk_name_id link to NAMES::pk_name_id

      date

      memo

       

      LEDGER

      pk_trans_lineitem

      fk_acct_id link to ACCOUNTS::pk_acct_id

      fk_trans_id link to JOURNAL::pk_trans_id

      cleared

      amount

      total

      balance

       

      ACCOUNTS

      pk_acct_id link to LEDGER::fk_acct_id

      acct_name

      acct_type

       

      Link to ERD: http://www.4shared.com/photo/IzOlqfzp/Screen_shot_2010-06-29.html?

       

      I have 'Allow creation of records in this table via this relationship' for both the LEDGER and JOURNAL table.  Primary keys of all tables are auto-enter serials on commit (not on creation).

       

      In Form View in the JOURNAL table of transactions, I have a portal to show related records from the child table, LEDGER of transaction line items.  When I click the Go To Next Records arrows in the toolbar, the records of transactions and related records of transaction line items in the portal show as they should.  The problem starts when I create new records in the portal.

       

      I add a new transaction record by adding a new record in the JOURNAL table in Form View, and fill in the necessary fields.  I then go into the first line of the portal to create new related records of transaction items and fill in the necessary fields.

       

      After I have finished entering all fields and records in the portal for the transaction (usually no more than two records of transaction line items for any given transaction) in the portal and click outside the field to commit the related records in the portal, the new related records I created in the portal disappear and the portal is blank (which previously showed two new records BEFORE I committed them).  Why did they disappear?

       

      I believe the new records that were in the portal AFTER I committed them are disappearing because the transaction line items did not save the related transaction ID number.  Without this transaction number, the 'related' records in the portal after creating them do not know which Transaction they are related to and thus, disappear.

       

      When I look in Table View of each of the JOURNAL and LEDGER tables, I see the new transaction and transaction line item records I created when I was in Form View as mentioned above.  The new JOURNAL record shows the new Transaction ID and the fields I filled in earlier (while in Form View).  The new LEDGER records show the new Transaction Line Item ID and other fields I filled in earlier while in Form View, but the JOURNAL::pk_trans_id related field in the LEDGER table shows as blank.  This is consistent with what I saw in Form View.

       

      I can't figure out why the new LEDGER records of transaction line items will not save the related Transaction ID.

       

      Any ideas?  I hope I painted a good enough picture.

       

      Thanks. 

      A beginner in FMP11 on Mac OS 10.6.4.

       

        • 1. Re: portal records disappear
          philmodjunk

          Here's your problem: Primary keys of all tables are auto-enter serials on commit (not on creation).

           

          You create a new Journal record and the value for pk_trans_id is blank because you have not yet committed the record. With auto-create enabled as you describe, creating new records in the Ledger portal will auto-enter the value of pk_trans_id into the matching fk_trans_id, but since this field is still empty, this feature copies the "empty" value of pk_trans_id into fk_trans_id.

           

          You have two options: 1) change the serial number setting for pk_trans_id to On Creation instead of On Commit 2) Make sure you commit the new Journal record before entering data into a new row of the Ledger based portal. You can do this with a script trigger or by simply clicking a blank area of your layout.

          • 2. Re: portal records disappear
            eibcga

            I'm having the same problem no matter what option I choose, but thank you for this.  The records in each table are being created, but the records in the child table become 'orphans'.  Perhaps I am misunderstanding what the foreign key field in the LEDGER child table should be.

             

            To clarify, the fk_trans_id foreign key field in the LEDGER table is a related field (i.e., JOURNAL::pk_trans_id) that links to the primary key in JOURNAL table pk_trans_id.  Shouldn't this foreign key not be a related field, but rather, should be a regular field that just happens to have the transaction ID in it?  I mean, the transaction ID in the foreign key field is not dependent on the transaction ID in the parent key field, but is a static number of the transaction ID.  At the end of the day, the transaction ID in the parent table is related to any records in the child table that match the transaction ID.  It's almost as if I should be entering the transaction ID number in the foreign key field manually, but that seems to defeat the purpose of a database to me.

             

            I think I'm missing something here.  Thanks for your help. 

            • 3. Re: portal records disappear
              philmodjunk

              I think you are missing something in your layout or relationship design. I tried replicating what I thought was happening and found that filemaker popped up an error message to keep me from entering data in the portal until I committed the record to generate a valid serial number in the Parent record--so what I thought was happening is not the case.

               

              Check your portal and the fields in the portal to make sure they both refer to the same table occurrence in your relationship graph and that the relationship you've defined linking it to the table occurrence specified in your layout is correct.

               

              Also, if you are using filemaker 11, make sure you don't have any filter expression defined that might be filtering out your newly entered portal records.

               

              You may need to post a copy of your file up on a share site so we can take a look at it in order to spot the issue that causing this.

              • 4. Re: portal records disappear
                eibcga

                Checked, but seems to me like no issues… but not sure.  

                 

                File is eibcga Clone.fp7 at 

                 

                 

                 

                 

                • 5. Re: portal records disappear
                  philmodjunk

                  _kf_trans_id should not be set up as an auto-entered serial number. As it is currently defined, when you enter data in a blank row of the ledger portal, filemaker assigns the current value of __kp_trans_id to _kf_trans_id so that the value match, but then the auto-enter serial setting for _kf_trans_id kicks in and assigns a completely different serial ID--breaking the link.

                   

                  Simply remove the auto-enter serial option for _kf_trans_id and it will work for you.

                  • 6. Re: portal records disappear
                    eibcga

                    Thank you very much.  I should have realized that one myself.