Add related record?

[agtjazz]

We have found a flaw in the design we have been working on for almost 3 months or so.

Currently I have a people table , a contract table and a job assignment table. (along with various referential tables). There is one person that can have multiple job assignments and multiple contracts. So I have relationships from people to contracts and people to job assignments.

We just encountered that we need a relationship between jobs and contracts. For example,

Person John Smith

Active jobs: Lecturer for English dept

Lecturer for Philosophy dept

A contract should be for English and another one for Philosophy.

How would I do that? I could make a relationship job: Job_pk to contract: job_fk… but how would I add a record to the contract table that picks up the job_fk? I actually have a portal showing the active job assignments (thanks to this forum’s help). I would like to create another button on the portal row to add a contract to that job – but don’t know how to tell the script to go to the contract table and add a Related Record.

Your assistance is very very appreciated…. As I am in a bit of a panic since this was completely unforeseen by all involved.

Thanks in advance

[Kent Searight]

Set a global field to the Job_pk

Go to layout based on Contracts table occurrence

New record (you're not linking to an existing contract, right?)

Set foreign Job_fk field in Contracts to value in global field

Go to layout (original)

That's pretty much it...hope this helps!

[agtjazz]

Thank you for the quick response... but I am not familar with global fields. I tried to follow the steps you advised, but it won't work.

In the contract table, I made a global field called xjbpk as a number and in the calculated value I said xjbpk = job: jb_pk

When I go to the contract table, this field is blank and even when I add a contract (with the job and contract relationship established) this field is blank.

Should I have made a global field in the contract table? I apologize for the noob questions and appreciate all the assistance.

[agtjazz]

I am having a hard time understanding how to implement GTRR for new records. I have attached a watered down play database that I am trying to use to understand.

I have 3 tables

People, job assignments, contracts

If I add a new person, and want to add a new job for them……. How do I go to the job layout and add a related record to that person? I don’t want to use a drop down since there will be over 500 people in the database.

I am trying to set a global to empid and go to job layout and add a new record… but it’s not working.

Your help will be very very appreciated as I am having a very difficult time trying to understand this.

Thanks in advance

GTTR-global.zip

[Kent Searight]

Does this example help you to understand a little better?

Also, make sure that ALL records in ALL tables have an auto-entered serial number that CANNOT be modified. If you don't do this you'll find yourself in trouble later.

multijob_rev1.fp7.zip

[Lee Smith]

Hi Kent,

I did the same thing, only I moved the button into the portal.

Hi agtjazz,

A little different approach.

multijob.fp7.zip

[agtjazz]

Thank you for your assistance & patience... what happens if I don't have a portal?

How do I add a record that is related to the job or contract layout?

Thanks again

[agtjazz]

Let me explain a bit further.... We recently found out that jobs and contracts need to be related. People can have multiple active jobs and a contract for each job (sometimes more than 1 contract per active job).

I have a people layout with a tab for job assignment that has a portal for jobs... but Contracts have at least 70 fields or so. So I was NOT thinking of creating a portal to add a contract (that is related to job and ultimately person).

What is the best advice in this circumstance? I would like to create a new record without a portal...

Thanks bunches

[Kent Searight]

Try this.

I didn't see your latest post since we posted at about the same time.

multijob_rev2.fp7.zip

Edited June 7, 2006 by Guest
didn't see most recent post

[Kent Searight]

Maybe someone (Lee?) else can chime in? I have to get back to doing the stuff that pays the bills. :

Best of luck!

[agtjazz]

Kent: Thank you for all your help and patience! I will try the 2nd revision and see if I could get it to fit on my project. That's just what I was missing- the method in setting the global as you mentioned earlier this week.

Lee: Thank you for providing a different approach.

I am very grateful for this forum. It is so helpful and full of great people.

Thank you!!! Thank you!!!

[Kent Searight]

Hey agtjazz,

No problem, I hope it helps!

To answer a question you had earlier about globals, they can be in ANY table in your database, as they don't require a relationship to get values from them. In other words, the global field could have been in the Jobs or Contracts table and it would have worked just the same.

[Kent Searight]

Hey agtjazz,

I wanted to mention one more thing that I wanted to earlier but I was pressed for time. If you're setting up job assignments that may be shared among people (a many-to-many relationship) you should consider using a join table to relate people to jobs. This will keep you from creating the same job record every time someone is assigned that job.

I couldn't tell from your posts if that was your situation but I thought it might be helpful to at least make you aware of this.

[agtjazz]

Thanks Kent again for everything

The job assignment table is actually the join table between people & job code tables (thanks though)

For the globals:

I set the job id as a global for contracts to add a related record and it works.... I have a form that prints out for the contract (another layout)... do I have to pass a contract global to the form? I am going to try that- cuz right now it prints the incorrect record.

Thanks again for all the suggestions and your time and expertise to help me so much!

[Kent Searight]

It seems to me that if Contracts relates to People and if Contracts relates to Jobs, you might want to establish 2 separate relationships. In other words, peopleID in People is keyed to peopleID in Contracts AND jobID in Jobs is keyed to jobID in Contracts.

[agtjazz]

The relationship has changed- so now it's people to job and job to contracts. (before it was people to jobs and people to contracts)... but as I mentioned in the first post that was incorrect and we just discovered that. So now it's people who have an active job assignment(s) and each contract is based on a job assignment.

Still working on getting the contract form to print per contract... but thanks to the advice and suggestions- I think I am on the right track.

Have a nice weekend!!!