6 Replies Latest reply on Sep 19, 2011 3:39 PM by philmodjunk

    Help Filtering Portal

    brianquillin

      Title

      Help Filtering Portal

      Post

      I am recording Purchases (on the Purchases Table/Layout).  Each purchase will be assigned to a SHOW and also a BUDGETLINE.  

      SHOWS are being classified as "current, previous, or future".  

      I have a Purchases 2 portal on my Purchase Layout where I want to show ONLY Purchases from the SHOW marked as "current".

      I have tried MANY combinations of filters and calculations but haven't found a working method.  It appears that my Purchases::showname is not related to Show::ShowID or Show::showname.  

      Current relationships (in my frustration/desparation, I've messed with these also - they may be setup incorrectly)

      Show::showID --- < ShowBudgets::showID
      ShowBudgets::budgetlineID ---< Purchases::budgetlineID

      PLEASE CONFIRM:
      ONE Show may have MANY BudgetLines
      ONE BudgetLine may have MANY Purchases

      Do I have the path setup correctly? 

        • 1. Re: Help Filtering Portal
          philmodjunk

          I have a Purchases 2 portal on my Purchase Layout where I want to show ONLY Purchases from the SHOW marked as "current".

          "Purchase 2" implies that you have a table occurrence named "Purchases 2". Do you? (I don't see one in the two different relationships listed in your post.)

          On what table occurrence is this portal based? On what table occurrence is the "purchases layout" based?

          (Table occurrences are what we call the boxes found in Manage | Database | Relationships. We can't just call them "tables" as one table can have many table occurrences in this relationship window that refer to it, each with a different name.)

          • 2. Re: Help Filtering Portal
            brianquillin

            I do have a Purchases 2 TO (it is related to Purchases via Purchases::purchaseID -x- Purchases::purchaseID)

            The Portal is based on the Purchases 2 TO.  The Purchases Layout is based on Purchases.

            I tried the portal filter of Shows::show_status = "current" but that doesn't work.

            (What is the proper way to assign a Purchase to a Show and BudgetLine?  Should I have those fields as Purchase::show_name or Shows::showID? - Purchase::BudgetLine or ShowBudgets::budgetlineID)

            Does the Purchase table need to be related directly to Shows rather than ShowBudgets?

             

            • 3. Re: Help Filtering Portal
              philmodjunk

              I do have a Purchases 2 TO (it is related to Purchases via Purchases::purchaseID -x- Purchases::purchaseID)

              In which case you have: Purchases::PurchaseID X Purchases 2::PurchaseID as the relationship for your portal. The other relationships do not apply here.

              To filter for the value of a field in Shows, you need an occurrence of shows that is linked to Purchases 2 not purchases. That requires something like:

              Purchases 2::ShowID = Shows 2::ShowID

              However you set it up, the key detail is you need to link an occurrence from Shows to Purchases 2--that could require additional table occurrences (such as an occurence of ShowBudgets) between Shows 2 and purchases 2, depending on the structure of your relationships.

              Then your filter expression becomes:

              Shows 2::show_status = "current"

              (What is the proper way to assign a Purchase to a Show and BudgetLine?  Should I have those fields as Purchase::show_name or Shows::showID? - Purchase::BudgetLine or ShowBudgets::budgetlineID)

              Except for certain relationships used to help find or display data, all relationships should be based on ID numbers, not names--this protects you from problems should you ever need to edit the value in a "name" field.


              • 4. Re: Help Filtering Portal
                brianquillin

                Thanks very much (as always).  Your ability to explain/inform is truly a gift.  I will work through your suggested solution this evening and reply back with updates (hopefully positive).

                • 5. Re: Help Filtering Portal
                  brianquillin

                  Thanks for your input.  As usual, right on target.  I have a few other issues that I'm working on.  I'll post in a new thread if I don't find the answers already posted.

                  Quick question (broad topic) - Can you recommend a good reference or resource on good schema practices (how to structure/organize data more efficiently)?

                  I'm working on my transition to the A/B system but still looking for a comprehensive resource there as well.