10 Replies Latest reply on Sep 22, 2011 6:24 PM by PeterDowns

    Conditional formatting drop down list Dilemna

    PeterDowns

      Title

      Conditional formatting drop down list Dilemna

      Post

      Hi 

      I have an issue with a pair of conditional formatting drop down lists. (this is a different issue to my other drop down list posting)

      The idea is in a portal where users enter costs about an activity they are undertaking, they select a drop down list with a fund called 'Fund Area' listed and this should automatically filter the next drop down list to show only the accounts in that fund to pick from, called 'Fund Account'. The portal is tied to a table called 'ProjectedCosts' inside a parent layout - 'Projects'. Fund Area is populated by table called 'Funding', Fund Account is populated by table called 'Chart of Accounts'. The are related by a field called 'CostCentreCode'.

      The problem is when you pick Fund Area all Accounts are shown in Fund Account, not the ones related to their fund areas.

      If I set up a blank layout with the 2 drop down lists displayed pointing to the same value lists they work perfectly filtering correctly. This blank layout is pointing to the table - Chart of Accounts and referencing the 2 value lists used in the above portal. So I know after reading every conditional formatting advice listed by PhilModJunk the FM-deity I am close. 
      I can't change where the portal or layout are pointing as it is capturing other data as well.

      Not sure how to proceed
      Any help would be appreciated

      Cheers

      Peter 

        • 1. Re: Conditional formatting drop down list Dilemna
          philmodjunk

          You have these relationships:

          Projects::__pk_ProjectID = ProjectCost::_fk_ProjectID

          ChartOfAccounts::CostCentreCode = Funding::CostCentreCode

          You need another relationship for your conditional value list:

          ProjectCost::CostCentreCode = FundingByCostCentreCode::CostCentreCode

          FundingByCostCentreCode is a second occurrence of Funding created by selecting Funding in Manage | Database | Relationships, then clicking the button with two green plus signs.

          ProjectCost::CostCentreCode is your first value list formatted field where you select the account. Your value list should specify fields from FundingByCostCentreCode, Include only related values starting from ProjectCost.

          • 2. Re: Conditional formatting drop down list Dilemna
            PeterDowns

            Ok still same issues.

            Made the 2nd occurrence of Funding - 'Funding_CostCentreCode'

            drop down  box of FundArea - ProjectedCosts::CostCentreCode
                 Values from table Funding field - CostCentreCode

            drop down  box of FundAccount - ProjectedCosts::FundAccount
                 Values from table ChartOFAccounts field - Account 
                 (tried adding 2nd field CostCentreCode in dropdown with related values from ProjectedCosts) didn't work

            Am still getting to see all accounts. Not seeing it. 

            Cheers

            Peter

            • 3. Re: Conditional formatting drop down list Dilemna
              philmodjunk

              This appears to be the problem:

              "Values from table Funding field - CostCentreCode"

              Values need to be from Funding_CostCentreCode, not from CostCentreCode.

              Even though both refer to the same datasource table. Funding_CostCentreCode is the occurrence needed to set up the relationship needed for the Includ only related values... option of your value list.

              • 4. Re: Conditional formatting drop down list Dilemna
                PeterDowns

                Hi 

                still can't get it to work. I will put it in more detail maybe it will pop out.

                Overall:

                Overall Layout linked to table projects
                Portal containing 2 drop down boxes related table ProjectedCosts

                Drop down Lists: 

                Have funding table (FundingCostCentreCode2) contains list of funding areas generates 1st drop down list. FundingCostCentreCode2 is a duplicate copy of tblFunding

                Fields: FundingCostCentreCode2::CostCentreCode / 2nd Field: FundingCostCentreCode2::ActivityArea
                Include all values.

                This displays correctly the list of funding areas. Selected results are stored in and displayed in ProjectedCosts field - ActivityArea

                2nd hopefully filtered dropdownlist. From table (ChartAccounts2) 
                Fields: ChartAccounts2::GeneralLedgerCode / 2nd Field: ChartAccounts2::AccountName
                Include related values from ProjectedCosts3 (automatically generated duplicate copy of ProjectedCosts - join relationship between ProjectedCosts3 and ChartAccounts2 field - costCentreCode)

                 

                RESULTS I have been getting displayed in the 2nd drop down list to select from include

                1 account only
                All accounts
                1st Account in list of all Fund Areas
                wih the above structure no values 

                I am out of ideas, As I have said before run it outside the portal will work perfectly. Inside the portal - useless.  

                Cheers

                 

                • 5. Re: Conditional formatting drop down list Dilemna
                  philmodjunk

                  I need to understand the actual relationships that you have set up here. I've reread this thread and don't see the rationale for additional occurrences of tables to get: ChartAccounts2 and ProjectedCosts3.

                  Can you list all of the relationships, listing both the table occurrence names and the match fields?

                  • 6. Re: Conditional formatting drop down list Dilemna
                    PeterDowns

                    Hi

                    the reason why there is multiple copies of above tables is due to the rule about not having multiple relationships between multiple tables. So thaey are all self generated. Because the database is getting quite big there is a lot of interactions between tables and therefore relationships.

                    Rather than try and explain it here is screen shot of relationships.

                    Cheers

                    PEter

                     

                    • 7. Re: Conditional formatting drop down list Dilemna
                      philmodjunk

                      I strongly recommend reading this article about a way to better organize your relationships so that they are easier to work with: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

                      Each of those "duplicate tables" are called table occurrences.

                      Which of the above occurrences is specified in Show Records From in Layout setup for this layout?

                      Which occurrence is specified in Show Records From in the portal set up for this portal?

                      • 8. Re: Conditional formatting drop down list Dilemna
                        PeterDowns

                        Hi Phil,

                        the overall layout is linked to table  'Projects'. The portal is linked to table 'ProjectedCosts'.
                        Two dropdown lists in portal.
                        First one is for field called 'FundArea' linked to : 'CostCentreCode' It's drop down list is linked to a value list. It's value list is tied to the following field 'CostCentreCode' from table 'Funding'. It displays the correct data.
                        Then the related next drop down list - linked to field 'FundAccount' It's value list has 2 field values. Value from first field is 'CostCentreCode' from table ChartAccounts' and 2nd value field is 'AccountName' from occurrence 'ChartAccounts 2'. (Show only values from 2nd field)
                        I have also selected to 'Include only related values starting from 'ChartAccounts 2' ' 

                        Obviously the 2nd field does not display correct data. I have tried different combinations as you suggested thats' why I have more random occurrences popping up. Above is what the latest combination is, not neccessarily my best effort.

                        Put up screen shot as well.

                        Screen shot of portal

                        Thanks in advance

                        Cheers

                        Peter

                         

                        • 9. Re: Conditional formatting drop down list Dilemna
                          philmodjunk

                          I'm looking for a key matchup of fields on which to base the needed relationship, but am not sure I see the fields needed for this. You need to beable to match the value in ProjectedCosts::FundArea to a field in ChartAccounts.

                          I can't tell which field that might be.

                          We need :

                          ProjectedCosts::FundArea = ChartAccountsByFundArea::UnknownField

                           

                          Then you set your value list to list values from ChartAccountsByFundArea, Include Only related values starting from ProjectedCosts.

                          • 10. Re: Conditional formatting drop down list Dilemna
                            PeterDowns

                            OK 

                            all sorted.

                            I related ProjectedCosts::FundArea to ChartAccounts::CostCentreCode. This generated another table occurrence of ProjectedCosts. 

                            The 2nd dropdownList for Chart Accounts I used Generalledgercode in the value list instead of costcentrecode as it was more specific to the account and it worked.Thanks for your help.

                            Cheers

                            PEter