Defining filter lationships on a related fields

[John Heffernan]

In a workshop database I designed, I need to show all the courses students in a special program have taken.

 

There is a People table with all the students.

 

There is an Events table with all the specific instances of courses. (A course could be given mulitple times/places). The course type is stored in Courses.

 

There is a join table peopleEvents which records who is enrolled in each course.

 

I created a special table for this specific program (called PLACE ) which has a foreign key to People and contains PLACE specific info.

 

I created calc fields in PLACE and in peopleEvents such that:

 

NumToText(fkPeopleID) & "." & If(cCourseType="Professional Licensure (PLACE) ", "PLACE", "FALSE")

 

The calc field works fine and mark the records I want.

 

However, I can not define a relationship (and hence a portal to show the courses) since courseType is stored in Courses. Events has a calc field that pulls it from Courses via a relationship. peopleEvents also has a calc field that pulls it from Events via a relationship. Since the value is pulled from a relationship, FMK says the relationship won't work since the value can not be indexed.

 

Besides using Lookup fields instead, is there a way to define courseType in peopleEvents so that it can be used in a relationship?

[Taff]

Hi John

 

The problem is that you cannot use a calc field in a relationship.

 

Once created do the elements of the calculation remain constant or do they change?

 

If they remain constant it may be possible to create a button that uses a script to create a new record you could then place your calculation in the set field option of Scriptmaker. This would work on all new records, if you wanted to change previous records you would need to write a script that uses a loop to go to the fist record sets the field then moves to the next record sets that field and so on until it reached the last record then exits the script.

 

If my thinking is correct this field could then be used in a relationship, as the field itself is not a calc field and could therefore be indexed.

 

I hope this has been of help.

 

Taff

[Robert Schaub]

[ QUOTE ]

Hi John

 

The problem is that you cannot use a calc field in a relationship.

Taff

 

[/ QUOTE ]

 

Not True, Maybe in this case it is , But generally Not True.

 

I usce calc in relationships all the time. Problem is calcs must be able to be indexed to work in a relationship.

 

Heres a common calc I use

CustID& Year(InvoiceDate) Indexed

 

So a cust num is 234 , year is 2005 Calc = 2342005

next years invoices = 2342006

Here I can grab yearly totals with ease.

 

Getting back to John's Problem

Why don't you keep trak of the courses taken by the Person

in the peopleEvents Table. Here you already have all the courses and people on assciated records.

 

Why not make a self relationship

 

courseCode :: CourseCode

 

In the Portal Sort by PersonID

Now you will see every for that courseCode in the portal

 

Hope this helps

 

P.S. What is a "lationships"