Getting a serial number from a related database

[Chuck]

Can you give us some more information? While I can think of a number of ways to do what you need, my first thought is to wonder why you would need to do that. It may be that there is an easier way to get your job done. Why does Tutee need this information?

Chuck

[OASES]

Hi everyone,

I'm designing a database to keep track of tutee and volunteer records for an afterschool program. Right now, I have PEOPLE, TUTEE, and VOLUNTEER databases that are related. However, the serial number (PEOPLE ID) is generated from the PEOPLE database. I want to do data entry from the TUTEE database, but I need to get the "next value" that should be generated from the PEOPLE database. Can someone help me? Thanks.

[OASES]

Ok, here's some brief background. It's hard to do this without diagrams. Everyone in our database is assigned a PEOPLEID (serial number that increments by 1, ie. John is #540, Sarah is #541). There is also a field TYPE that indicates whether John is a tutee, volunteer, donor, etc. Based on what John is (a tutee), there are different fields that would pertain to John and not to Sarah (a volunteer). Therefore, we have related TUTEE and VOLUNTEER databases that store those relevant fields. Now, when I want to enter a new TUTEE record, I don't know how to do it in the TUTEE database so that it takes the next incrementing value for the PEOPLEID (which is stored in the PEOPLEID as an auto-entered field) and updates what that next value should be. If it doesn't update what PEOPLEID should be, then if I entered a new VOLUNTEER record, I would get the same PEOPLEID as the TUTEE I just entered.

My tentative solution was to have data entry just occur in the PEOPLE database, and view information in their respective databases. Is this a good solution, or is there a better way? Thanks ahead of time. If this still isn't clear, please tell me what isn't.

[Chuck]

First, here's how I would probably solve the problem from the beginning. I tend to shy away from one-to-one relationships, which is what you have between people and tutees and between people and volunteers. While some might consider it bad relational design, I've found that the problems that pop up in such a design outweigh such considerations as the disadvanges of having null fields (fields that do not contain data for a large percentage of the records).

So I would have only a People file with a field that specifies whether the person is a tutor or a volunteer. Then based on the value in that field, I would use scripts to take the user to a layout that is specific to the type of person.

However, if you're set on having three files for this, you can do this.

First, create fields in both People and Tutees called Constant which is a calculation field set to equal 1. Relate Tutees to People using this field as the match field on both sides. This will allow you to transfer data in global fields, avoiding the copy and paste commands.

Create a field in People called gPerson_ID which is a global field. It should be a global text field if you Person_ID field is text, and a global number field if the Person_ID field is number.

Then write a script in People called New Record:

New Record/Request

Set Field [ gPerson_ID, Person_ID ]

Write a script in Tutee:

New Record/Request

Perform Script [ External: People (New Record) ]

Set Field [ Person_ID, People~Constant::gPerson_ID ]

There are other ways to do this, but this one, I think, is the most straight forward.

Chuck

[Chuck]

yafreax,

Actually you're close to what my other method would be, which is to use what I call a relationship pipe. Basically, I create a relationship such that it is never valid. In my local file, I use a constant field set to 1, and in the foreign file I use a number field (usually called Temp_Relationship) set to auto enter a 0. The relationship is set to allow the creation of related records.

Then you can use a statement like:

Set Field [ Relationship_Pipe: :Dummy, "X" ]

Set Field [ People_ID, Relationship_Pipe::People_ID ]

Set Field [ Relationship_Pipe::Temp_Relationship, 0 ]

The first set field creates a record in the foreign file. This happens because there is never a valid related file and because I've allowed the creation of related records.

The second set field gets the People_ID value from the new record using the temporarily valid relationship and sets that value to the local People_ID, creating a valid relationship using that field.

The last set field breaks the pipe so that there is again no valid related record in the Relationship_Pipe and the next time I use it I can do so to create a new record.

Chuck

[This message has been edited by Chuck (edited December 19, 2000).]

[OASES]

Thanks yafreax and Chuck. I'm opting to use yafreax's portal method, since it's so simple and it works. Thanks again. This forum is really helpful for a beginning FMPer like me. =)

[yafreax]

okay, well, if i'm misunderstanding, one of you more advanced guys jump in ( ya know, chuck, liveoak, vaughan, etc)

If you have a portal in the Tutee (that's a funny word to me, anyway) database and set it to "allow creation of related records." Put all the neccessary data entry fields in the portal and then when you create a new record within the portal, it should automatically create the next available PeopleID. I would have the PeopleID Field in the portal as well, and as soon as you left the first field in the portal, it would asign an id . . .

crap, sorry rambling about nothing, since the People ID is what relates the two, it won't work will it? oh well, it still throws some stuff out there.

yafreax

[yafreax]

well, chuck, that was going to be my next post, ya know, you just got in there about one minute before me. . .

yafreax