Link fields

[devinh]

OK, I'm starting to redisgn our company database. But thur my research I'm finding one road block and I hope the forum could lead me to an answer.

Here is the problem

I have 3 fileds for this problem.

Jobs_current_number

Jobs_ previous_number

Jobs_previous_list

We have certain jobs that need to see all the previous jobs that relate to it.

Example.

job 100 has no previous number

Job 200 has a previous number of 100

job 300 has a previous number of 200.

job 400 has a prevoius number of 300

Now with Job 400 I would like the filed "Jobs_previous_list" to show me all the jobs that have a link to 400. So it should show 300, 200 and 100. Of course Job 300 would show 200 and 100.

Does this make any since.

Thanks

Devin

[SlimJim]

Make a second copy of the Jobs table, Jobs2, and relate Jobs to Jobs2 by jobs_previous_number = jobs_current_number. Now define jobs_previous_list as a calculation:

jobs_previous_list = jobs_previous_number & " " & Jobs2::jobs_previous_list

In the " " insert whatever delimiter you want to have between the numbers in the list. If the job numbers are set up as numbers then it may make sencse to cinvert them to text before putting them into jobs_prvious_list.

I have no setup to test this on but it should work with maybe a bit of tweaking to start off as the empty list with the first job number in a chain

[Ender]

I think SlimJim is suggesting a self-join relationship, which is how I would do this. For a separator, I'd use a

[comment]

Poster indicates version 7, so a self-join relationship

Jobs::Jobs_current_number > Jobs 2::Jobs_current_number

should be enough to show all previous jobs in a portal.

[Ender]

Good point. And if there are sets of jobs, identified by a common foreign key, then the relationship would then be something like:

Jobs::EmpID = Jobs 2::EmpID

AND Jobs::Jobs_current_number > Jobs 2::Jobs_current_number

With this, you only see the previous Jobs for the same Employee (or whatever you wish to group by.)

[devinh]

Ok....I think I understand, Now I need to try and make a test database to see if I do understand, so I might be back for more help on what goes where.

You guy are great! I'm so glad that there is a forum like this. Now to pay my mebership dues.

Thanks

Devin

[devinh]

Well I thought I knew what I was doing! But if it's a self join relationship then why would I need to have a seperate job2 table. I'm not quite sure how I do the relate to jobs2!

can anybody make up a quick example.

Thanks

Devin

[Ender]

You would not use a second Job table, but a second table occurence of the existing Job table, called Job 2.

[devinh]

ah..Ok I was just watching the Jerry Robins Filemake 7 video on that. This is so cool, but very hard my little mind to rap around.

Thanks for the insight.

Devin

[devinh]

Well I tried it and all I get is ? in the prev_list?

Any ideas?

I guess it would be hard to guess what I did wrong so I could post my test file if you need me to.

Devin

[SlimJim]

Apologies. My suggestion regarding the Previous_job_list should be an auto-enter calculation, not a calculation.

See the attached which just deals with the three fields as an indication of how this process works. In the real situation you will have to take account of the possibility of deletion of records in the chain of jobs and, as mentioned above the possibility of grouping jobs by another criterion.

ChainLink.zip

[comment]

You don't need a previous list - see attached.

PrevJobs.fp7.zip

[Ender]

Are we going around in circles?

[devinh]

Well that is a start...Thanks, but I see two pit falls.

One being it's and auto-enter calc.

If a change is made to a field or deleted then there would need to be a way to recalculate this. I'm sure there is a workaround for this.

The other is it's not a portal. We may like to see other info along with the pervs job, like a date filed and where it's saved. Then they would also have a go to curent record button for one of those prevs jobs if they need to see more info.

Also not all jobs have a prevs job so there is no link.

Thanks

Devin

[comment]

Not anymore - twice is my limit.

[devinh]

Well circles is nice simple object! Thanks for your help anyway.

I'm thinking that the list will need to be a calc field that does some type of recursive look up. Or at least this will be my next circle to try. But I still need to treat each of the job # in the prevlist to point back to that record of the job.

Thanks

Devin