3 Replies Latest reply on Aug 1, 2011 5:27 PM by brianquillin

    Relational Quagmire

    brianquillin

      Title

      Relational Quagmire

      Post

      Filemaker Pro 11 Adv. on Mac (currently on my 8th day learning the software)

      I'm very new but learning more each day but I'm in a situation where I'm not even sure where to look for the answer.  I've been troubleshooting my dilemma for several hours without finding the solution.  I'll try to be as brief as possible in the description of my problem.

      Database for entering information for a Silent Auction - including Buyers, Donors, Items.  I will be creating Receipts (to give to Donors) and Invoices (to give to Buyers).  All is working perfectly EXCEPT the relationship between Items (at center of my relational diagram) and the Line Items for Donated Items TO (same as Line Items).  On the Invoice side, the relationship works perfectly.

      On the Receipt side, I cannot enter Items into the portal UNLESS I have the ALLOW & DELETE options selected on both sides of the relationship.  When I have both side of that relationship set to ALLOW & DELETE, then new records are created/deleted in my Items each time I add/delete something to the portal (as I would expect).  When I turn off ALLOW & DELETE on the Items (at center of diagram), then I get an error saying "This operation cannot be performed because one or more required related records are not available and cannot be created."

      I'm convinced that I know less about relationships than I previously thought.  Any advice is greatly appreciated.

      Screen_shot_2011-03-31_at_7.58.05_AM.png

        • 1. Re: Relational Quagmire
          philmodjunk

          This may be as much a layout design issue as it is a relationship issue. I don't think enabling a delete option on any of these relationships is a factor I can't conceive of any way that could be a factor. The allow creation option is a key clue here I think, but it comes down to how you've set up your layouts for this.

          I suspect that you are using portals for data entry--which is how I would do it, but the fields, the portals or the layout on which the portal is located refer to the wrong table occurrence and thus have to "force" a creation of a record in a related table to permit the creation of a new record in the portal when you enter data.

          Describe the layout where you are encountering this problem. In Layout Setup..., there's a drop down called "Show Records From". What table occurrence is listed in it? Describe each portal on this layout and what Table occurrence is listed in "Show Records From" in portal setup... for each of these portals. Check and see if the fields in which you are trying to enter data refer to the same table occurrence as the portal. If they do not, indicate what table occurrence they refer to.

          • 2. Re: Relational Quagmire
            brianquillin

            Thank you very much for the "AH HA" moment!  You were absolutely correct.

            The portal was still referencing records from "Line Items" TO - Not the "Line Items for Donated Items" that it was linked to on the table chart.  (I should be more careful when duplicating Layouts)

            Very simple fix once I set the portal to pull from the appropriately linked Table.

            Thanks again.

            • 3. Re: Relational Quagmire
              brianquillin

              It's been a long time, but going back into this database for a little modification.  

              Can I have a combined CONTACTS Table that would consist of both Buyers and Donors (see original post in this thread)?

              Should I replace both the BUYER Table and DONOR Table and replace with one CONTACTS Table?

              What would be the easiest way to accomplish this?

              My reality is that for this solution, it is very common for me to have someone donate money or an item but that same person also will purchase another item donated by another person.  It would be great to have ALL MY PEOPLE in one place but still be able to assign them both a Donor Receipt for money or items AND a Buyer Invoice for something they purchase.

              Any thoughts?  Thanks in advance.