Jump to content
MJH

Related records in same table

Recommended Posts

Hello

I am trying to relate multiply records in the same table.

I have a table called Jobs with each record having a unique serial id as well as a job number.   Often a new jobs comes in that is a revision of a previous job (or jobs) that has been billed and closed.  My database users would like to link the new jobs to the old ones.   I have created a second instance called Jobs_related and also tried putting a join table in between, but nothing I do to join them works.  

Thanks

Share this post


Link to post
Share on other sites

Don't do that - create a Master_Jobs table which then links to the Jobs that are relevant.

Share this post


Link to post
Share on other sites

Create a new table to store all related jobs for a job.

That table will have 2 main field the Job id and related job id.

ex. if job with 4 is related to jobs with id 1 & 3 respectively. then this new table will have 2 records:

Job ID           Related Job ID

4

Create a new table to store all related jobs for a job.

That table will have 2 main field the Job id and related job id.

ex. if job with 4 is related to jobs with id 1 & 3 respectively. then this new table will have 2 records:

Job ID           Related Job ID

4                      1

4                      3

 

Then create a relationship of Job table with this new table based on Job ID. On jobs layout you can place a portal of this new table. 

Hope this helps.

Share this post


Link to post
Share on other sites

You do not need a new table for related jobs. You just need to store the ID of the related job in the same table. Read about normalization. Whatever you can store in one place you do not store in a different place.


Sent from my iPhone using Tapatalk

Share this post


Link to post
Share on other sites

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

  • Similar Content

    • By sal88
      Hi all
      I'm trying to find the Client that has the most Job time over the last 30 days, divided by the number of seats that they have. The tables are:
      Clients
      Client_ID, Seats
      Jobs
      Client_IDF, Job_time, Job_Date
      The best I've managed so far is to find the distinct Client_IDF from all Jobs:
      ExecuteSQL ( "SELECT DISTINCT j.log_client_IDF FROM Jobs J WHERE j.Log_date >= ?"; ""; "";  $date_30_days_ago) And then loop through the list and perform the 'divide by seats' calculation for each line
      Or to do something similar but from the Clients table:
      ExecuteSQL ( "SELECT SUM (j.labour_units), c.Name FROM Clients C JOIN Jobs J ON j.log_client_IDF = c.Client_ID WHERE l.Log_date >= ?"; ""; ""; $date_30_days_ago) But no joy in either case when it comes to actually performing calculations within SQL.
      Is this possible in FM SQL?
      Many thanks
    • By Joost Miltenburg
      LS,
      I find charting confusing. Anyway...
      My goal is to do a product year over year comparison on how many times it has been sold given a date. 
      Two products can be picked and a reference date will be entered. A product has a date in it so it will be unique. That's why I am comparing two products
      I found the related table example in the training series and I got it to work. However, the 2016 product comes in the first column and the 2015 in the second. I would like to change that so that 2015 comes first. I've tried sorting the relationship, that didn't work.
       
    • By Ana606
      Hi,
      I have a DB I'm working on and I'm stuck on something that I think will be pretty simple but I cant seem to get my head around it.
      Its for a school, so you have courses,locations,students etc.
      Students can take many courses at many locations.
      So I have a course layout, with a portal to the participants. This works well apart from I cannot get to open the related record from the portal without it showing me all of the records relating to the student in question. I need to create and reprint a certificate for each student \ course. I have created a button on the portal row to open another related layout up (the cert) to allow them to be printed or emailed, but it always shows me all of the course certificates for the student in question. I have another button on the form for printing all of the certificates for that particular course and that works fine.
      I had setup some scripts (shown below) but have gone back to a simple related record button to see if I can figure out whats wrong, I have tried all sorts of changes for hours! and I'm getting no where, any help would be greatly appreciated
       
      edit: Also I realise it shows in the script "records being browsed" but I also did try current record only. Being playing around
       
       
       
       






    • By mapsgal
      Hello,
       
      I am trying to think through a relational database involving medieval maps as linked to manuscripts.
       
      I have set up the manuscript end of the database. What I am trying to figure out is whether or not I should make the maps in each manuscript relational or not. Each manuscripts comes with on average 21 maps but these can sometimes be missing, so the numbers can fall. Some contain only 1-2 maps and others no maps at all. On the other end there are a few manuscripts that contain 100+ maps and images. There is in other words, no steady amount. If I had to pick a fixed number I would opt for 21 since those are the map manuscripts that I work with the most.
       
      I could set this up as a flat database with container options for 25 maps and info for each one repeated and then fill in those as needed according to each manuscript.
       
      But my better sense tells me that I need to make this relational and set up the maps in a separate database and link them. Then the question is do I need one separate database for all the maps or one for each type of map. So, for instance, there are world maps, maps showing the Mediterranean, maps showing North Africa and Spain, etc. It would be useful to be able to search for all maps of the Mediterranean or the world across all manuscripts but this is not essential because I am also a Lightroom user and I can tag my images to create collections of maps.
       
      My own experience with FM databases varies with the decades. (My skill level should read Beginner not Intermediate) Back in the early days of FM (FM4) when it was owned by Claris (yes, I go back that far) I designed a super complicated manuscript-map database. Then I stumbled over the lack of a big container field for notes so I ended up used printed copies and hand-filling out the data. Life, research, teaching, deprived me of time to work on this again. I bought FM12 ran out of time and now we have FM13 and I cannot afford to buy the upgrade. So I have to do this on FM12 and limp along until FM14 comes out. At which point I will spring for the upgrade. 
       
      It sounds odd to say this in 2015 but I really love those delicious container fields that can now even take a pdf. Wow!
       
      Thanks for any advice on this crazy complicated database for a poverty-struck academic who cannot afford to hire a professional to help her out.
       

    • By mikedr
      Background.  A record in a table is related by a field to a field in another table.  To select the related record, I use a dropdown box that shows all the relevant field values that can be selected from.  The issue is, if the desired value is not there, you want the user to be able to enter a new value, and then automatically create a record for that new value in the related table.
       
      I was having trouble with this issue previously, and decided to take another look at it.  I came across the following solution, which is worth reposting here.
       
      http://forums.filemaker.com/posts/d11c45f9f8
       
      Here's the relevant section.
       

       
      Now, for whatever reason, this doesn't work for me unless I add a "Commit Record" at the very top of the suggested script, and then it works like a charm! 
       
      My previous solution was to force the user to press a "plus" button next to the dropdown to add a new record (via a popover).  I think this approach is cleaner.
       
       
  • Who Viewed the Topic

    5 members have viewed this topic:
    Will Xu  Ron Cates  Punith baskar  oscar23445  jddel 
×

Important Information

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