Jump to content

Add related record?


This topic is 5661 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!!!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!!!

Link to comment
Share on other sites

This topic is 5661 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.