2 Replies Latest reply on May 19, 2012 12:35 PM by StevenWexler

    Complex Portal/Relationship

    StevenWexler

      Title

      Complex Portal/Relationship

      Post

      I would apprecaite anyone's input on this. 

      I am developing a database for my girlfriend's business. She will be selling private lessons and workshops.

      In her invoice table, I have a 'class code', 'Description', 'Quantity', 'Unit Price' and 'Total' fields. The 'Description' and 'Unit Price' fields come from another table called "workshops." I set up a relationship between the Invoice Table and the Workshop Table, so that when she enters the class code, it automatically shows the description and unit price. I did this so that she can easy look up and print out who is attending a particular workshop. The Quantity and Total fields are part of the invoice table. The 'Total' is a calculation where the 'Quantity' field is multipled by the 'Unit Price' field. All is working fine. A side note, there is only one item per invoice, so there is no portal on the invoice layout which shows multiple items from a line item table. 

      Here is where I'm stuck. In her customer table, I would like to be able to view all invoices made by that customer and show me these fields as they are displayed in the invoice table. But since those fields come from both the workshop table and the invoice table, I have no idea how to set up that type of complex relationship so it shows  as it would if it came from a single table. 

      Thanks in advance for any help. 

        • 1. Re: Complex Portal/Relationship
          GuyStevens

          I would guess that you already have your relationships set up. If you have a contacts table then you probably have a relationship between your invoice table and your contacts table like so:

          Contacts::ContactId------<Invoices::ContactIdFk

          And I assume you also have:

          Incoice::WorkshopIdFk>-----Workshops::WorkshopId

          If this is the case you could just put a portal on your layout based on the contacts table.

          The portal should be based on the invoices table and show:

          - The InvoiceId from the invoice table. (or an invoice number or date)
          - The Description or name of he workshop
          - and any other fields you want to see.

          If you need any further help or an example file, let me know.

          • 2. Re: Complex Portal/Relationship
            StevenWexler

            Thanks so much for your help. That is what I tried and it didn't work. Setting the portal relationship to a different table and back again. Then creating different records made it snap in. 

             

            Thanks again for your help. You rock!