Need a relationship primer

[Tundraboy]

Apparently I don't quite grasp how a relationship between two tables works. I have been trying to get a calculation to work but the results are always wrong.

If two tables are related by some identifier in a 1:M relationship, and a layout (based on the table with unique identifiers) calls for information from the related table (with multiple instances of the same identifier), why does the following calculation only seem to act on the first record it finds in the related table?

Case (table2::field5 = 1; table2::field4;

table2::field5 = 0; "N/A")

So if table1 has 1 record, table2 has 5 records and record 3 of table2 has field 5 populated with a 1 it would seem I should be able to pull table2::field4 because it has found that record. That does not seem to be the case with my database I only ever seem to have table2::field4 record 1 returned.

I realize that there are probably a number of other ways of returning the data from table2::field4 but the problem is my grasp of the theory is apparently wrong in the first place. Can someone tune me in?

[Vaughan]

Relationships work like baggage tickets: each traveller is given a number, and each of their pieces of luggage are given a ticket with the same number.

The traveller's tickets need to be unique. The baggage tickets need to match the travellers tickets.

In FMP, the master table (traveller records) has a primary key field, usually an auto-generated serial number. The related table (baggage) has a remote key where the traveller's primary id is entered.

Your case calc above is working correctly: looking through a relationship sees only the first related record. Display the related records in a portal to see them all. Or use the List() function to aggregate all the related data into a paragraph delimited string.

[Tundraboy]

Hmmm. So I am only half correct in my understanding. To user your analogy the problem I have is with the "baggage tickets". I need to return information about one of the baggage tickets not all of the baggage tickets related to the passenger. A portal does not do the trick in this case. What I was hoping to achieve with the Case statement was something similar to this SQL statement:

Select field4 from table2 where field5 = 1;

If I placed a foreign key in table1 (Passenger ticket) from table2 (Baggage tickets) could the extra identifier be used to single out only those baggage tickets that were flagged (field5)?

Bob

[Fenton]

There's more than one way to do this, depending on what you want returned; a list, or a single value? We don't really know the business logic; you say "one of the baggage tickets"; but why only one? Structurally it could have more than one with 1 in field 5.

If you have a "Constant" field in table 1, a calculation field = 1, then add that to your relationship, pointed at field5 of table2, then you will only see those records (maybe only one record, but we don't know) where field5 = 1 for that person.

table1 -> table2

person id = person id

AND

_c1 = field5

You could then calculate, from table1: relationship::field4

But it would be only the 1st one it hit.

[Tundraboy]

Fenton,

The intent is to return one result from the related record that is flagged. Using Vaughan's example of Passengers and Baggage tickets, I would be looking for each passenger that has missing baggage. Assume for the moment that only 1 piece per passenger can go missing (insert airline humour here) and that a flag has been set in the record of the missing piece of baggage.

The real business logic surounds students and their attendance. and the flag is set on students that have transferred or withdrawn. The layout I am trying to populate is a summary of the attendance. The summary needs to report the status change code when a student has withdrawn.

Both tables are currently related using a student identifier number, which is serialized. Table2 "Field5" is a flag which is calculated field (unstored) and Table2 "Field4" is the status code. Because the summary layout is based on Table1, there is a calculated field that is intended to catch these flagged records and return the status code to the field. The Case statement at the beginning of this thread is the calculation for this field.

Passengers and baggage or students and attendance, the problem is that I thought the current table relationship was enough to allow the Case statement to identify the flagged record and act only on it when returning a result. What actually appears to be happening is that the Case statement only acts on the first record in the related table. Vaughan has suggested in his reply that another relationship may be needed.

If necessary I can post a sample of the database ... could take a bit of cleaning though to prevent privacy issues.

Bob

[Fenton]

Table2 "Field5" is a flag which is calculated field (unstored) and Table2 "Field4" is the status code.

Why is Field5 unstored? That makes everything not only more difficult, but much slower. It's a 'flag' of what?

[Tundraboy]

Why is Field5 unstored? That makes everything not only more difficult, but much slower. It's a 'flag' of what?

Field5 is a flag identifying when/if a students enrollment status changes which is directly related to attendance. It is my attempt to identify which records have status changes. Each record in Table2 indicates 1 day of attendance information for the student. So the only quick way I could see to indicate which records had changed status was to create a flag.

Bob

[Tundraboy]

Fenton,

I have a sample of the Attendance Utility attached (I think, haven't done that before here) that should help this dialogue a bit.

The "Table1" and "Table2" references in this message thread are the Attendance Utility table and the Attendance_Calc table respectively. The layout for which the information is destined is the Attendance File.

This utility is acting as a reporting tool for data from another database (database set actually). Any field names in caps are populated via ODBC from that dataset.

There is only one sample student and their attendance data included. Normally there would be from 100 to 500 students and their attendance data.

Bob

Attendance_Utility_Alpha007_Copy.zip

Edited May 15, 2007 by Guest
forgot info about contents of db