Understanding relationships & new records.

[DBNewbie]

I have a Agent database and another database called "Projects". Each Agent can have multiple projects. I figure this is a one to many relationship.

The Agent fields are: agent_id, name, emal, phone, notes, status and some global fields.

The Project fields are: agent_id, project_id, name, project_state, cost, date_created.

Do I create the relationship in the Project DB or Agent DB. To my understanding, I create the relationship in the Project DB, relate the agent_id of the Project DB to the agent_id of the Agent DB.

I wrote a script that creates a new Agent record and calls an external script in the Project DB to create a new record as well. My problem is in

the script in the Project DB to create a new record

New Record/Request

Set Field[agent_id, "Agent::agent_id"]

I can create a new record but the agent_id in the Project DB is left blank. It leaves it blank but the agent_id in the Agent DB is filled in (agent_id in the Agent DB is set to auto-entry by serial number.

If there is a relationship that I created, how come the agent_id in the Project DB won't be set with the agent_id found in the Agent DB.

I even clicked on create related records in the relationship edit window.

Did I define my relationship in the wrong place. Where are relationships supposed to be created anyways....

I'm a newbie to this whole thing and trying to learn it on my own....

[danjacoby]

The relationship is created within the Agent file. Use a portal to create new Projects.

The "key field" (the field in the Project file that is related to a field in the Agent file -- probably agent_id) need not be in the portal -- it will be filled in automatically. The other data -- project_id, name, project_state, cost, and date_created -- can be added manually ... although you've probably set date_created to auto-enter the current date.

P.S. For a moment I thought you had reverse engineered a custom solution I've created, which I call CastPro, and which several agents and casting directors here in New York City are using. Just goes to show you should never jump to conclusions.

[DBNewbie]

Why would I need a portal to create a new Project record?

Also if I create the relationship in the Agent DB where the agent_id is related to the agent_id in the Project DB then the script in the Project DB, I cannot access the the agent_id.

SCript below:

Set Field [agent_id, #what do I put here?]

I don't know what to put there since the relationship doesn't exist in the Project DB but rather in the Agent DB as you say.

I was reading the FileMakerPro and they have an example with Customer and Video Invoice, they have the relationship set in the Video Invoice...which is the opposite of what you are suggesting...

I am still confused....

Thank....

[Kurt Knippel]

You do not actually set the agent_id field from the AgentDB through the relationship, you need to go to the ProjectDB and then set the field. Because as you say the record (and thus the relationship) does not yet exist.

Basically:

Click a button on the AgentDB screen and store the agent_id in a global field.

Goto the ProjectDB and create a new record, storing the agent_id global into the new record's agent_id field.

Continue with data entry on the ProjectDB record

[danjacoby]

The agent_id field in the AgentDB should auto-create a unique alphanumeric string when you create a new record in the AgentDB (there are several ways to do this). That way, you can use it to create the relationship (in the AgentDB) with the agent_id field in the ProjectsDB.

Once that relationship is set up, you don't need the script. Just create a portal on a layout in the AgentDB that accesses the other fields in the ProjectDB. Now, by typing on the next line in the portal, you are automatically creating related records in the ProjectDB.