Define field problem (related tables)

[Frank E]

I have a database (in FM Pro 7) made up of three tables: a Client table, a Source table, and an Event table. The database is a life-history database which produces a life-history timeline for each client. Each individual record in the Event table describes one event in the client’s life.

Each event is derived from a single source (a record in the Source table). Each event has only a single source, but a single record in the Source table can be the source for many records in the Event table. (A source is typically an interview or a record, for example a medical record.)

I want to define a field in the Source table which I call Source Code. This field will act like a serial number field for the records in the Source table associated with a particular record in the Client table. In other words, the Source table may contain 100 records, but only a subset of those records (let’s say 12) will be associated with a particular Client. I want the Source Code field of all records in the Source table associated with this client to be sequentially numbered from 1 to 12, in the order in which I create them. (There will be other records in the Source table associated with other clients; so the Source Code field will not have the same values as the Source ID field, which is an auto-entered serial number field.) Thus, each record in the Source file will have a unique Source ID field, but there will be several records with the same Source Code field. (There will be many records with Source Code 1, one for each record in the Client table.)

I want to define Source Code as a field that will auto-enter a sequential value for each record in the Source table associated with a particular Client. When I create the first record in the Source table for Client A, I want the Source Code field to generate 1. When I create the second record in the Source table for Client A, I want the Source Code field to generate 2. (I may have created several records in the Source table for other clients between creating the two source records for Client A.)

Any suggestions on how to define the Source Code field so that it will automatically generate sequential numbers for the records associated with each client as I create the new Source record?

Thanks in advance for any advice.

Frank

[IdealData]

You must already be storing the CLIENT_ID in the SOURCE table, otherwise you can't make the relationship of CLIENT -> SOURCE work.

Use the CLIENT_ID in the SOURCE table as a self joining relationship to itself, then you can use the COUNT function to determine the number of currently associated SOURCE records for the Client. Just add 1 to the COUNT for the SOURCE_CODE field. You'll need this is an auto-enter by calculation or you can script it.

[comment]

Why do you need this field at all? If you have a portal to Events on the Client layout, you can enter and display client's events directly.

Filemaker's default sort order is by record creation, so if you create your records in sequence, that is how they will be shown in a portal. If you want, type @@ into the first portal row (in layout mode) to number the shown records sequentially.

[Frank E]

Thank you for your help. Here’s what I did:

I set up the self joining relationship by establishing a second Source Table and joining the CLIENT_IDs.

I then defined SOURCE_CODE as = COUNT(CLIENT_ID).

When I look at the records there is a 1 in my new field SOURCE_CODE in every record.

I also defined a field SOURCE_CODE_2 as a number field, auto-entry by calculation, using the same formula. This also returns a 1 in each new record.

What am I doing wrong?

Thanks again.

[Frank E]

Postscript to my previous post: I see what I did wrong, and I think I have fixed it -- partially. I was defining the calculation in SOURCE_CODE 2 without specifying that the Count should be made from the new table (Sources_template 2). Once I made this change, it seems to work with all new records in the Source table except for the first record for each client. The formula returns a blank in the SOURCE_CODE field for the first source record for a new client, but then returns the correct value (2, 3, 4, etc.) for subsequent records in the Source table for the same client.

Here is the formula I am using for the SOURCE_CODE field:

= Count ( Sources_template 2::ClientID ) + 1

In other words, when there is no previous source record for a new client, the database is apparently not seeing the result of the Count function as zero.

Any suggestions on how to fix this?

Thanks again.