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.
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.
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.
still can't get it to work. I will put it in more detail maybe it will pop out.
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
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.
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?
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.
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?
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.
Thanks in advance
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.
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.