AnsweredAssumed Answered

Relationship and showing data problems

Question asked by TC on Mar 14, 2012
Latest reply on Mar 14, 2012 by karendweaver

I developed a database in Access, but now the customer wants to use the system on a MAC. I chose FileMaker after researching what product to do. Here is the background

 

The system tracks customers and the filters needed to support them by month. I have three tables and have extracted the key fields below

 

tblCustomer

<CustomerID>

 

tblCustomerFilterAmounts - one to many relationship with tblCustomer

<CustomerFilterAmountsID>

<CustomerID>

 

tblCustomerMonths - one to many relationship with tblCustomer

<MonthsID>

<CustomerID>

 

I created a form layout and data displays correctly as I move from my two test customer records with each record having two filters records and two month records. Views of each tbl also show correct data

 

I want/need to do a rollup that shows what filters need for each customer by month so if I had one customer with two filter records and two month records, I want displayed in a list view (for now):

Filter1 Data Month1

Filter1 Data Month2

Filter2 Data Month1

Filter2 Data Month2

 

I tried creating using the relationships above, but only got Filter1 Data Month1

I creating another relationship using tblMonths2 with tblCustomerFilterAmounts, but that was a many to many relationship. I split the relationship into two one to many relationships by adding a tblGoBetween (for now) with the following

tblGoBetween

<goBtwnID>

<CustomerFilterAmountsID> linked to <CustomerFilterAmounts> in tblCustomerFilterAmounts

<MonthsID> linked to <MonthsID> in tblCustomerMonths

Now I get Filter1 data to show only. I created a view for tblGoBetween and it shows no data

 

Can anyone show me where I am going wrong? I would greatly appreciate it

 

TC

Outcomes