10 Replies Latest reply on Feb 23, 2010 7:58 PM by srfeuer

    Problem with adding records in related files - again.



      Problem with adding records in related files - again.


      I thought I would try this one more time.  It seems a very simple problem to me, although the solution seems to be anything but.


      I have a table.  It is a Payments table.  You really don't need to know too much more about it except that it is related to another table.


      The other table I will call Group Transactions.  Again, I don't think you need to know to much about this table except that it is related to the Payments table.  I have it on a layout as a portal so that I can add records to it based on payments that are made.  One payment for each group.  If you need to know, payments are all made at the time of the transaction and cover everything in the Group Transaction.  This part works just fine by itself.   I have it set up in the Manage Database to allow records to be added only to the Group Transaction table based on records in the Payment table.  It is a one to many relationship.


      The problem is with the next table. 


      The next table is a Line Items table.  It is related directly only to the Group Transaction table.  For each transaction in a Group Transaction there can be many Line Items.  Therefore I want to be able to add records to the Line Items table based on entries made to the Group Transaction table.  This table is also a portal on the same layout as the Payments and Group Transactions.  In other words I have two (2) portals on the same layout.  If I turn on the allow records to be added to this table, and only this table, based on records added to the Group Transaction table, a strange thing happens.  Every time I add a record in the Line Items portal, a record is automatically entered in the Group Transaction portal, even though I have not turned on this possibility anywhere in the Manage Database.  It also gets a serialized number even though I have not told it to do that either.


      Having worked with other databases for many years, constructing this relationship and adding appropriate records and relationships is a simple task and works exactly as one would expect.  However, I have tried just about every combination I could think of and I cannot get this to work right unless I manually add records to the Line Items table and disallow the "Allow Records to be added" option in the relationship window, or whatever you call it.


      Can anyone tell me if Filemaker allows two (2) related files on the same layout in two (2) different portals to add records properly???  If so, how is this accomplished??


      I hope this explanation is simple enough to get a proper response.  It doesn't seem complex or difficult to understand the problem to me. 

        • 1. Re: Problem with adding records in related files - again.



          Having responded to your previous thread, I know that your table structure and relationships are far from simple. Could you post a copy of your current file so we can take a look at it? You can upload the file to a file sharing site and post the link here so that forum members can examine it before attempting to respond to your post.


          You should be able to do what you want the and presence of two or more portals on a layout should not be a factor, but the devil is in the details of how you've designed your database.

          • 2. Re: Problem with adding records in related files - again.
               I did post a link to the rapidshare site that I placed a picture of my database design.  I thought I sent you a reply with it.  That was in my other post.  If there is not a usable link there, I will post it again in this post.
            • 3. Re: Problem with adding records in related files - again.

              That picture would help with the context since you've started a new thread. However, I'm suggesting you upload a copy (You can leave out your records by saving a clone of your file) of the actual file.

              • 4. Re: Problem with adding records in related files - again.
                   There were actually five pictures that I uploaded.  Were you not able to see the other four??  If not, I will try to get you the URL's for the other four.  They are all in something called a collection.  When I read how it supposedly works I thought that anyone would be able to see the entire collection and not just one.  I had a great deal of difficulty trying to copy all five URL's, but I'll try again if that will helpl.  The other screen shots are of the tables and the screens that come up when you click on the box that shows up when you join two related files.  By the way, don't worry about all the other table and design structure.  I will fix that once I understand how to make this work.  Just concentrate on the three tables that I am trying to relate and add records to properly by using the "Allow records to be added" commands.  Thanks.  I can do screen shots of the structure of the three tables and upload that as a picture along with the other pictures.  I will try to do that later today when I have more time.  Thanks again for all your help.
                • 5. Re: Problem with adding records in related files - again.
                     I honestly don't remember what you had uploaded originally, but pictures can't always take the place of an actual copy of your file. I haven't had the time to search out your original thread. It would help to post your URL here. Uploading a copy of the file instead of screen shots would be even better.
                  • 6. Re: Problem with adding records in related files - again.
                       Okay.  As soon as I figure out how to do it and accomplish it I will let you know.  Thanks again.
                    • 7. Re: Problem with adding records in related files - again.

                      Hello again.  I just tried something new.  I created a brand new database.  I kept it very simple.  I created three tables.  The first table I called Payments.  It has only two fields.  The first field is GroupTransactionNumber as a number field.  The second field is TransactionNumber which is also a number field.


                      The second table I called GroupTransactions.  The fields are exactly the same.


                      The third table I called LineItems.  The fields are also exactly the same.


                      The system automatically created three layouts, one for each table.  Since the first table I created was the payments table, that was the first layout.


                      I went to the Manage Database window and went to the edit relationship window.  I stacked the icons with Payments at the top, Group Transactions underneath that, and LineIitems directly under that.


                      I then created a relationship from Payments to GroupTransactions based on the GroupTransactionNumber field only.  I then went to the "Edit Relationship" window where I only checked the box that says "Allow creation of records..." only for the GroupTransactionsTable.


                      I then did the same thing for the LineItems table.  I created a relationship from GroupTransactions to LineItems based on the GroupTransactionNumber.  In the "Edit Relationship" window I only checked the box that says "Allow creation of records..." only for the LineItems table.


                      I then proceeded to put a portal for the two related tables, GroupTransactions and Lineitems, on the Payments layout.


                      I then added one record for the Payment table and put in numbers for each field.  I used '1111' for the GroupTransactionNumber and '2222' for the TransactionNumber, but obviously you can use any numbers you want.


                      I then went to the first portal, the GroupTransactions portal and added three records.  That worked fine, exactly as expected.


                      I then went to the LineItems portal.  It allowed me to enter records also.  However, when I entered a record in the LineItems portal, it automatically entered another record into the GroupTransactions portal.  This is exactly wrong behavior.


                      I kept this very simple to see if I was a FileMaker problem or something in the complexity of my design.  I believe it to be a Filemaker problem.  


                      You should be able to duplicate this experiment in about ten minutes.  See if you get the same result.  Also, I am using Filemaker 10.0, not advanced.


                      I was able to sort of solve this problem by changing the relationships.  If I take away the relationship between GroupTransactions and LineItems and instead make the relationship between Payments and LineItems, I can then add records into the LineItems portal without records being automatically entered into the GroupTransactions portal.  However, this is not the behavior I want and should not be necessary if FileMaker was working properly.  This may be a bug.


                      The reason I say this is not the behavior I want is because the relationship of payments is one to many between payments and GroupTransactions.  The relationship between GroupTransactions and LineItems is a many to many relationship.  I don't need to worry about numerous payments at different times because all GroupTransactions are paid all at once as are Single Transactions (that is a different story, but don't worry about that here).


                      Maybe a little background will help you understand why I want this to work this way.  I am an instructor.  Each session students have to register again.  Some students will be new and some will be returning (already in my computer system).  Here is the situation I seek to handle.  Sometimes multiple registrations will be paid for by one person.  Example, a family with three children.  Three registrations and one payment.  Each student may or may not buy equipment.  I seek to hand each registration as an individual transaction and, in this case, also as part of a group transaction for purposes of recording the payment.  So let's say that the first registration is given a GroupTransaction number of '1111' and a TransactionNumber of '2222'.  The second registration would get a GroupTransactionNumber of '1111', but a TransactionNumber of '2223'.  The third student would get a GroupTransactionNumber of '1111' and a TransactionNumber of '2224'.  The same would apply to equipment purchases but let's not complicate the scenario any more than it is.


                      Why do this??  Because all future lookups would be based on a single student.  I would like to be able to see that they were part of a GroupTransaction (or single payment) which would also show me which other students (and or transactions) were all paid for with the same payment.  Since I would only be looking something up based on only any one student, I would have no way of knowing about the others unless I could see them through the GroupTransaction.   You might be thinking why not just record them all as single transactions.  I could, but as I said, there might be a single payment that could apply to any number of transactions.  But I also need to record single transactions as single transactions (another problem not related to this particular problem).  I have a way of handling this very easily.


                      Anyway, I am not really asking you to help me design my database.  I hope this is not too much information.  I am just wondering why FileMaker automatically enters these records that I don't want automatically entered when it would appear that it should not based on the simple design and setup.  See if you can duplicate this set up and if it adds additional unwanted records like I am getting.  That is really the only problem I am looking for help on.


                      Thanks again. 

                      • 8. Re: Problem with adding records in related files - again.

                        I know this isn't what you want to hear, but there's no filemaker bug here. Filemaker is doing exactly what you designed it to do--it's just not what you want it to do.


                        Take a look at your relationship graph:


                        Payments::GT# = GroupTrans::GT# = LineItems::GT#


                        You have a portal to LineItems on your payments layout, but there is no relationship directly from Payments to LineItems. Instead, you've specified a route from Payments through Group Transactions to Line Items. Using this design, the only way filemaker can create a record when you enter data in the LineItems portal is to first create a matching record in the GroupTransactions table. It may not be what you want, but it's what you set filemaker up to do for you.


                        To eliminate this, you need a second Table Occurrence for LineItems. Select this Table Occurrence in the graph and click the button on lower left with the two green plus signs. link this Table occurrence directly to Payments.


                        Payments::GT# = LineItems 2::GT# and enable the allow create option for LineItems 2.


                        Use LineItems 2 instead of line items for your portal and it should now work for you.


                        I may have posted this link before for you. If so, please ignore. If not, you may find it helpful:


                        Many folks find the terms Table and Table Occurrence confusing. To learn more, click the following link:

                        Table vs. Table Occurrence (Tutorial)

                        • 9. Re: Problem with adding records in related files - again.

                          I understand your response.  But it still seems like a design error on the part of FileMaker.  I now understand that that is how it works and was meant to work, but I already had created the matching records in the GroupTransactions portal, that should have been good enough without creating more matching records.  Anyway, I will try your suggestion.  It is close to what I have already surmised.  As I told you it works almost correctly if I draw the relationship between Payments and LineItems instead of GroupTransactions and LineItems.  Your suggestion will probably complete the solution to my problem.  I guess I just wish the way my logic said it should have worked.  I think you did post that link before.  I will check it out.


                          Thanks a million for all your help and putting up with me.  If this works the way I want, I will redesign my original database to take this into account. 

                          • 10. Re: Problem with adding records in related files - again.

                            I checked your link.  Thanks.  I think I already pretty much understood that.  In FoxPro, which is what I am used to, it works somewhat the same.  The difference is that I can define which tables to use, what indexes to set for each table, and the relationships between them "on the fly" through the use of commands (scripts).  I do not need to lay it out in a graphical design first before I can use it.  In Filemaker you need to lay it out in a graphical design first (correctly drawn) and then go use the appropriate TO's to establish the relationship and use it on a layout.  It took me a while to realize the similarities and differences between the way the two systems work.  I prefer the "on the fly" method, but hopefully will get used to the "design it correctly first, then use it"  method.


                            Thanks.  By the way, you must be a really busy guy.  It seem like you are the only one that responds to people's problems here in the Forum.  Keep it up.  Use newbies need you.