12 Replies Latest reply on Jul 2, 2013 3:49 PM by AnitaWoods

    Portal relationship problem?

    AnitaWoods

      Title

      Portal relationship problem?

      Post

      I have a Pregnancy table, and an Accounting table, related Pregnancy::PregnancyID = Accounting2::PregnancyID.  The original Accounting table is related Contacts::ContactID=Accounting::ContactID.  A Contact can have multiple Pregnancy records, and each Pregnancy can have multiple Accounting records.  

      I have a layout based on Pregnancy with an Accounting portal, but it shows all accounting records for that Contact, not just the ones related to that PregnancyID.  I tried basing it on Accounting2 and it shows two of only the Contact's first accounting record, but not related to that Pregnancy.  I've tried filtering it Pregnancy::PregnancyID = Accounting::PregnancyID as well as =Accounting2::PregnancyID.  Help?

        • 1. Re: Portal relationship problem?
          philmodjunk

               I am assuming that Accounting and Accounting2 are two table occurrences with the same data source table.

               Pregnancy-----<Accounting2

               Contacts----<Accounting

               

                    I have a layout based on Pregnancy with an Accounting portal

               What other relationships do you have? From what you have posted so far, a portal to Accounting on the Pregnancy layout will be empty. There must be additional links in your relationship graph. I would guess that you have a link from Contacts to Pregnancy, Contacts----<Pregnancy, but prefer not to guess...

               

                    I tried basing it on Accounting2 and it shows two of only the Contact's first accounting record, but not related to that Pregnancy.

               Hmmm, that makes no sense at all. An unfiltered portal to Accounting2 on a Pregnancy layout should show all accounting records for that pregnancy and only that pregnancy. Since that is not what you have, something else is wrong here--could be the wrong data in a match field or issues with your layout or portal setup or your relationship is different from what you have reported here.

          • 2. Re: Portal relationship problem?
            AnitaWoods
            /files/35abd38eeb/Treebaby_relationships.jpg 842x1024
            • 3. Re: Portal relationship problem?
              philmodjunk

                   Looking at the lines, the relationship line from Accounting 2 vanishes into a "tangle" a the Pregnancy end, but presumably it matches fields by PregnancyID. And it looks like Accounting 2 and Accounting have the same data source table, though you'd have to hover the mouse over the arrow in the top left corner or check things on the Tables tab to be sure.

                   So that appears to rule out the possibility that the relationship is "different", but leaves the other options I listed as possible reasons why you are not seeing the correct data in your portal. You need to check layout setup, portal setup and the table occurrence references of each field in the portal to make sure that none of them refer to Accounting when they should refer to accounting 2. Then it comes down to checking the data in your match fields.

              • 4. Re: Portal relationship problem?
                AnitaWoods

                     Pregnancy::PregnancyID = Accounting::PregnancyID, and yes, Accounting and Accounting2 are two occurrences of the same table.

                     Layout is set up based on Pregnancy. Portal is currently set up based on Accounting2, and each field in the portal is from Accounting 2.  BUT, Accounting::PregnancyID isn't populated at all, that field is blank.  Isn't it supposed to autopopulate with the matching Pregnancy::PregnancyID when the new Accounting record is created?  I'm sure that's why it isn't working, how do I make them match?

                • 5. Re: Portal relationship problem?
                  philmodjunk

                       Yes, that's why it isn't working.

                       How do  you create new records in the accounting table? PregnancyID, the foreign key field won't automatically receive a matching value except when the new record is created under very specific circumstances such as by creating a new record by entering data in the "add" row of a portal to Accounting or Accounting 2 from a layout based on Pregnancy.

                  • 6. Re: Portal relationship problem?
                    AnitaWoods

                         I use the Accounting table two ways: I have a separate Accounting layout where I can create records just by clicking "new record."  It is related to Contacts::ContactID = Accounting::ContactID.  That works perfect.  I then also use it by just tapping in the bottom empty row of the Accounting2 portal in the Pregnancy layout.  I tried to make the layout show everything for that contact and just filtering it based on the PregnancyID, but that isn't working either.  You're so helpful, will you ever get tired of me?  

                    • 7. Re: Portal relationship problem?
                      philmodjunk

                           Using the portal to accounting2 should auto-enter the pregnancyID, but creating accounting records on the accounting layout will not automatically enter a value in this field. FileMaker has no idea, when you do that, what value should be entered into that field. You'd need to either make this a required field and give it a value list for selecting a pregnancy record to which to link it, or your new record button will need to perform a script that enters this value.

                      • 8. Re: Portal relationship problem?
                        AnitaWoods

                             No, I can't do that, because some records in Accounting are expenses I pay out to vendors, not pregnant clients, so there wouldn't be a PregnancyID in the accounting record in that instance, and uses a separate layout.  What would work is to require the Accounting2::PregnancyID only when it is a new record via the portal in the Pregnancy layout.  A Set Field script?  "Set Field [Accounting2::PregnancyID; Pregnancy::PregnancyID]"? How do I get it to run only when the new record is created in the Pregnancy layout portal?  

                        • 9. Re: Portal relationship problem?
                          philmodjunk

                               To repeat: When you use the portal to Accounting2 on the pregnancy layout to create a new accounting record, the pregnancyID field should get the needed value automatically from the current pregnancy record in your layout. The only time this will not work is if you use the accounting layout to create a record that should be linked to a pregancy record.

                          • 10. Re: Portal relationship problem?
                            AnitaWoods

                                 OK, my idiot status has been restored, because I originally had the portal set up based on Accounting not Accounting2, every record created did not automatically populate that PregnancyID.  Now that it is set up based on Accounting2 and no records show up, it is because the PregnancyID field was never populated.  I have some data entry to do in that field in each Accounting2 record, but at least it works now. As always, a virtual plate of cookies to you.

                            • 11. Re: Portal relationship problem?
                              philmodjunk

                                   Hmm, let's see...

                                   at different times I've been given--as a result of answering questions in the forum:

                                   Virtual Beer
                                   Virtual Pizza
                                   Virutal Cookies
                                   Gourmet popcorn--oh wait, that one wasn't virtual!

                                   Quite a virtual party! wink

                              • 12. Re: Portal relationship problem?
                                AnitaWoods

                                     Well, I mill my own wheatberries for fresh bread and cook mostly organic.  So how about whole wheat organic cookies made from organic vegan dark chocolate chips?  I try to eat gluten-free, so I can make them organic & gluten-free instead of whole wheat if you like.  :)