4 Replies Latest reply on Jun 2, 2014 7:45 PM by obospieler

    Products NOT on an invoice

    obospieler

      Title

      Products NOT on an invoice

      Post

           Hello!

           I know that a portal can be used to show me all the products on an invoice (via a portal to the invoicelineitmes table on the invoice layout), but can I create a portal which would also show me which products are NOT on an invoice?

           I am looking to see what products are NOT on an invoice (perhaps in a separate tab control).  I cannot figure it out.

        • 1. Re: Products NOT on an invoice
          davidanders

               Another Table Occurrence with a "Not Equal" relationship?

          • 2. Re: Products NOT on an invoice
            obospieler

                 When i tried to do that with a second TO of both the products and the product line times tied to the invoice table I can get the new portal to show every line item that is not on that particular invoice.

                 But I'm looking for a list of products that are not on an invoice, not a list of the line items no on an invoice.

                 Here is a rephrasing of the question to maybe spark another method:

                 If I place a portal to the Products table on the layout based on the employees table (obviously there is that line items join table in the middle), I get a list of every product that customer has bought.  But can I place a portal on the layout based on the Customer table to show all products that customer has NOT bought?

                 Weather I show the products no purchased on either the invoice layout or the customer layout doesn't matter to me in particular.  Truth be told.  I think whatever method I use for one will be transferable to the other.

            • 3. Re: Products NOT on an invoice
              philmodjunk

                   I am assuming these tables, fields and relationships:

                   Invoices---<LineItems>----Products

                   Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
                   Products::__pkProductID = LineItems::_fkProductID

                   In Invoices, you can define this calculation field, cProductList with Text as the result type

                   List ( LineItems::_fkProductID )

                   Then you can add a new Tutorial: What are Table Occurrences? of Products linked to Invoices like this:

                   Invoices---<Products|Notselected

                   Invoices::cProductList ≠ Products|NotSelected::__pkProductID

                   A Portal to Products|NotSelected placed on the Invoices layout will then list all products not selected for the current invoice. This relationship can also be used as the basis for a conditional value list called a dwindling value list.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              • 4. Re: Products NOT on an invoice
                obospieler

                     Of course your solution worked perfectly!  Thanks so much PhilModJunk!