Still need help: Conditional Relationships?

[wirledpeas]

Hi -

I'm still trying to figure out a way to create a conditional relationship between two databases. Basically, I have a db with contact info for a variety of people (call it ContactDB), and I want another db that has other information for a subset of the contacts (call it StudentDB). I want to find a way for the StudentDB to pull those records, and ONLY those records, for people who have a "Yes" under the "Student" Field in the ContactDB, and then have additional information associated with each student record like "Graduation Date." How do I do that?

Sorry I'm a newbie and thanks.

-Kate

Edited November 2, 2007 by Guest

[Søren Dyhr]

Use this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000079

--sd

[wirledpeas]

Ok, this shows me how to hide fields. But I want to hide *records*... I'm not seeing how it can do that. Let's say I have the following people in ContactsDB:

1 Joe Schmoe Student 555-1212

2 Mary Moop Student 555-2345

3 Philip Grok Faculty 555-6789

4 Ingrid Honk Student,Faculty 555-7654

What I'd like to have happen is the following in StudentsDB:

1 Joe Schmoe 555-1212 Graduated 05/02

2 Mary Moop 555-2345 Graduated 12/06

4 Ingrid Honk 555-7654 Graduated 05/04

Edited November 2, 2007 by Guest

[Søren Dyhr]

Why not introduce an extra file for these extra's ....a portal isn't nessersarily required. Use the boolean field as keyfield!

--sd

[comment]

Huh? Surely the relationship should be based on ContactID?

@Kate:

Is this really for version 6?

[Søren Dyhr]

Jikes! Indeed...

--sd

[wirledpeas]

Actually, it's version 7. We got a (minor) upgrade, but when I try to show version 7 on here, it resets back to 6 every time... Don't know what that's about. But we've got 7.

[Fenton]

First, the obligatory question; why 2 tables? Why not just put the students in with the other people? Since you're marking them as students you can always tell them apart when needed.

Because you say you have 2 tables, and both have the student's info. But you don't mention anything about an ID to tie the 2 together. Which just does not make sense. Do people just enter the name and info twice? Why? Do they come from separate sources?

Without an ID tie, all you have are names in each table. So the relationship would be on the name. But this is very unreliable. It can be used temporarily however, to populate a real Student ID foreign key in the Contacts table (or, better, a Contacts ID into the Student table). It will require some work to check for non-matches, not to mention possible duplicates, "John Jones," "Bill Smith", etc..

Hopefully you can see how it should look when you're done; ask if you don't. But just clicking "Yes" next to a student in the Contacts table will only give you some idea of who should have a Student match; it does create one.

On the positive side, if names were entered correctly you should be able to get almost all of them quickly just using the name. But the few percent with typos, misspellings, etc., will take (much) more time. This is one of the reasons they invented relational databases in the first place, to avoid these problems.

[wirledpeas]

I am trying to create a tie, but I'm trying specifically to create a conditional or selective tie. Right now, if I base the tie on unique ID numbers, I can get records from Contacts to show up in Students, but it's not selective so ALL records show up, not just students' records. I'm trying to figure out how to structure the relationship so that it only draws in the records that fulfill certain criteria. I want to say to FM: "Show me the records from the Contacts DB in the Students DB that fulfill the criteria 'yes' in the field 'Contacts:Student.'"

The reason I don't want to store it all in the same database is that there are many different kinds of people in the Contacts database: students, faculty, coaches, donors, volunteers, board members, allied organizations, and so on. Each of those categories has information that is only relevant for them: students have things like graduation date and project title, faculty have teaching topics, donors have grant application due dates, allied organizations have joint projects, and so on. Rather than store all that information in literally several hundred fields in the same database (which would mean that any given field would have hundreds of blanks for all of the people for whom the field isn't relevant), I wanted to have a central place for contact information to be used as a phone book and source for mailings, and then other tables or databases that only hold the records and fields that are relevant to a given group of people. Is that clearer?

Edited November 4, 2007 by Guest

[Fenton]

That's a pretty good reason, a "subset" table. It has a 1-to-1 relationship with Contacts. And you say have unique ID numbers, but you don't say which. Do you have ContactID in Students? Or a StudentID in Contacts? (Either method would work, but it would help us to know.) Or both?

It would also help to know what exactly the problem is that you're trying to solve. Because if you have an ID between the tables, it seems to me that the problem is already solved. The "Yes" field seems a bit redundant, as the relationship would already show you the matching record in the other table, from either side.

Which makes me question whether you do have the relationship. There is not much reason why a "unique" relationship would show All the Contacts in Student.

[wirledpeas]

Right now, as the data stands, there are no ID numbers. I am trying to build the new database from scratch, and then migrate the data into the new setup to make it much more useful. So, as of right now, there aren't ID numbers. BUT in creating a new set of databases, I can add ID numbers to individual people, and I was going to do it in contacts with an auto-inputting numerical field. (However, if later, someone adds a contact via the Students' database, that might mess up the numbering in Contacts...) What I can't do is give an ID number based on what category someone is in (like S1, S2, etc for students) because many people fall into multiple categories.

I guess I'm not explaining my problem clearly enough. Generally, I want to have certain records, with certain criteria, from one database displayed in a second database where I can add additional fields. Imagine I'm starting from scratch. As I add records to the first database, I want some way that the second database *automatically* displays those records that have necessary criteria and doesn't display those records that don't. With a relationship based only on unique, automatically-inputted ID numbers from the first database, all of the records I input in the first database show up in the second database (relationship ID=ID). Clearly, I need some way for that second database to look for the criteria (student:yes/no) in order to only display the right records (student:yes).

I'm not sure why this is so hard to describe. It seems like it should be a really straightforward thing that any relational database should do, but I've been beating my head against it for literally weeks.

P.S. Can someone explain the thing about the boolean field as a keyfield? Because that sounded promising, but I don't understand it.

Edited November 4, 2007 by Guest

[comment]

I think there are two issues here that need to be considered separately: one is data structure, the other is navigation (and user interface in general).

In terms of structure, the problem is very simple. If you want student-specific fields to be in a separate Students table, just do exactly that. The relationship between Contacts and Students should be based on ContactID. You can set it up so that filling any of the student-specific fields (in Contacts) automatically creates a related record in Students, and vice-versa (see attached file).

However you must be very careful with the design of your user interface, so that users cannot end up with orphan students (students with no record in Contacts) or ghost students (contacts that have a related student record with no data in it), etc.

You should also consider carefully whether the savings in file size are worth the extra effort. It would be very easy to have scripts to find just students, or just donors, etc. or all contacts, and display them in a suitable layout - all within a single Contacts table.

Students.fp7.zip

[Fenton]

If you have a proper ID relationship, you do not need to tell the Contact table explicitly that someone is a student. The relationship to Students will either have a match or it wont. That is why I keep downplaying the marking of "students" in the Contact table.

The relationship would be ConctactID=Students::ContactID.

ContactID would be an auto-enter serial number field in the Contacts table, the primary key of the table.

The Student table would also have its own auto-entered primary key, StudentID. But it's tie to the Contacts table would be based on a relationship from a "foreign key" of a local ContactID field (not auto-entered).

ContactID=Contacts::ContactID.

There is no absolute need to mark a contact as "student", because a relationship to the Student table based on ContactID would show the matching Student record. Though you might want to mark a contact as a student, simply for speed of processing; though it is redundant. It would generally make more sense, and be more reliable, to just go the Students table.

As far as adding a record to the Student table, that would not mess up the IDs, because they are not the same field. But you MUST create a new Contact record for every Student record. Hence this creation should be scripted; create the Student record, go to Contacts, create a new record, grab the new ContactID, take it back to the Student, set it into its ContactID foreign key.

It may seem too soon, because you do not yet have the data (name, etc.) for the Student. You will need some other mechanism to populate and maintain redundant data in the Contacts table (a free "events" plug-in?). But this is the responsibility you take on when you use this kind of "subset" table structure. In other words, keep the redundant data to the absolute minimum. Use the related data instead.

The attached example file shows a way to do this, will almost all the data within the Contacts table. If you look at the structure you'll see that with a few modifications the same script can be used to create the records for each of the subset tables, using a different Script Parameter (attached to the button itself; "Student", "Faculty").

The same script can take you from Contacts to the subset record. Because ONLY 1 of the relationships will be valid (not empty). A calculation field produces the different text for the button ("Student", "Faculty").

I added a "reverse" script, to create the "subset" record from a Contact record. It requires a Type field ("Student", "Faculty") so that it know which subset table to create the record in.

It has a fancy "invisible" button to do that, as you would not want the button if the related record was already there.

ContactsStudents.fp7.zip

[Fenton]

Yeah, comment's file is simpler. Mine is perhaps overkill for a 1-to-1 situation.

[wirledpeas]

Thank you, thank you, thank you!! Three cheers for both of you! I *finally* see what to do. I'm going to mess around with the different possibilities when I'm back at work on Wednesday, and may end up going with the scripts-for-one-table thing, but at least I now understand how to make relationships like this. You guys rock!

-Kate

[Fenton]

Well, I don't know that you need the scripts. As you can see from comment's file, a 1-to-1 relationship can have "[x] Allow creation of related records in both directions. So you can create in either way.

But it would be a little tricky to create all the different "types" of subsets from a Contacts layout.

[Søren Dyhr]

Fenton I have a question here, why do you in your templates "Go to Subset Record" ... use [color:red]Else If?? You stay put, if no match exists with GTRR ... the following script seems to give the same functionality:

Set Variable [ $originate; Value:Get ( LayoutNumber ) ] 

Go to Related Record [ From table: “con_Students”; Using layout: “Students” (con_Students) ] 

If [ Get ( LayoutNumber)  ≠ $originate ] 

     Exit Script [  ] 

End If 

Go to Related Record [ From table: “con_Faculty”; Using layout: “Faculty” (con_Faculty) ] 

Where the first line more practically should be the scriptparamter, since its done via a button?

--sd