agtjazz Posted June 5, 2006 Share Posted June 5, 2006 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 More sharing options...
Kent Searight Posted June 6, 2006 Share Posted June 6, 2006 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 More sharing options...
agtjazz Posted June 6, 2006 Author Share Posted June 6, 2006 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 More sharing options...
agtjazz Posted June 7, 2006 Author Share Posted June 7, 2006 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 More sharing options...
Kent Searight Posted June 7, 2006 Share Posted June 7, 2006 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 More sharing options...
Lee Smith Posted June 7, 2006 Share Posted June 7, 2006 Hi Kent, I did the same thing, only I moved the button into the portal. Hi agtjazz, A little different approach. multijob.fp7.zip Link to comment Share on other sites More sharing options...
agtjazz Posted June 7, 2006 Author Share Posted June 7, 2006 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 Link to comment Share on other sites More sharing options...
agtjazz Posted June 7, 2006 Author Share Posted June 7, 2006 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 More sharing options...
Kent Searight Posted June 7, 2006 Share Posted June 7, 2006 (edited) 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 Link to comment Share on other sites More sharing options...
Kent Searight Posted June 7, 2006 Share Posted June 7, 2006 Maybe someone (Lee?) else can chime in? I have to get back to doing the stuff that pays the bills. : Best of luck! Link to comment Share on other sites More sharing options...
agtjazz Posted June 7, 2006 Author Share Posted June 7, 2006 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 More sharing options...
Kent Searight Posted June 7, 2006 Share Posted June 7, 2006 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 More sharing options...
Kent Searight Posted June 8, 2006 Share Posted June 8, 2006 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 More sharing options...
agtjazz Posted June 8, 2006 Author Share Posted June 8, 2006 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 More sharing options...
Kent Searight Posted June 8, 2006 Share Posted June 8, 2006 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 More sharing options...
agtjazz Posted June 9, 2006 Author Share Posted June 9, 2006 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 More sharing options...
Recommended Posts
This topic is 6529 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 accountSign in
Already have an account? Sign in here.
Sign In Now