lookup problem

[Devin]

I need a database that will pickup old records when we enter a new job that is a rerun.

So after enter in the previous job #. I would like it to scan thru the database and pickup that Job# and What CD its on. After it finds that information I would like it to see if that recoord has a previous job and repeat untill the last record has no previous job#

Say job # 1 comes in. Job is done a saved to cd 1. Now job# 25 comes in and is a rerun of job # 1. So job# 25 needs to pull up the old data from job#1 and list job# and what Cd it's saved on.

But here is what I'm having problems with!

If a new job# 100 comes in and is a rerun of job# 25 than I want to see the saved location of job #25 and #1.

I'm very new to scripting in Filemaker Pro so any help would be great!

Devin

[Chuck]

quote:

---

Originally posted by Devin:

I need a database that will pickup old records when we enter a new job that is a rerun.

So after enter in the previous job #. I would like it to scan thru the database and pickup that Job# and What CD its on. After it finds that information I would like it to see if that recoord has a previous job and repeat untill the last record has no previous job#

Say job # 1 comes in. Job is done a saved to cd 1. Now job# 25 comes in and is a rerun of job # 1. So job# 25 needs to pull up the old data from job#1 and list job# and what Cd it's saved on.

But here is what I'm having problems with!

If a new job# 100 comes in and is a rerun of job# 25 than I want to see the saved location of job #25 and #1.

I'm very new to scripting in Filemaker Pro so any help would be great!

Devin

---

Let's say your database has two fields: Job_Num and Prev_Job_Nums. Build a self-join relationship such that Prev_Job_Nums = :: Job_Num. Also have a field for Prev_Job which keeps only the most recent previous job in the link.

So long as you always keep track whenever you link a job to a previous job, all you need to do is append the most recent previous job to the Prev_Job_Nums in that record and place the result in the Prev_Job_Nums in the new record.

For instance, You have Job_Num 1. Job_Num 25 has Job_Num 1 as a previous job, so Job_Num 25 has a 1 in the field for Prev_Job_Nums and a 1 in Prev_Job.

Now you have Job_Num 100, which has Job_Num 25 as a previous job, so Job_Num 100 has 25 in the field for Prev_Job. Use a script with the Set Field script step to set the Prev_Job_Nums of Job_Num 100 to:

Prev_Job & "

[Devin]

Chuck,

Wow I think I'm over my head!

Could you make a filemaker test file with the script in it for me. Just so I can look at it.

Thanks for your help..

Devin

[email protected]

[Devin]

Chuck,

I did it, thanks you.

But I would like it still lookup if they forget to run the script! On an older record! It looks like it stops getting the old job if no script was ran.

Devin

[email protected]

[Terrell]

Hi, I use a simular system in my job database, but use the job title as the relationship key, my job names are a calculation of client code + job name (lookup list), I then have a portal on the job details layout. If you have to many old records to add a logical job name this might not be useful but it works well for me. T

[Chuck]

quote:

---

Originally posted by Devin:

Chuck,

I did it, thanks you.

But I would like it still lookup if they forget to run the script! On an older record! It looks like it stops getting the old job if no script was ran.

Devin

[email protected]

---

I can think of one way to do this without a script, but I'm not even sure it would work. I would have to test it, but I don't have the time right now.

Basically, there's a technique out there which can keep a record of the changes to fields. It works by a feature of FileMaker that evaluates field calculations and lookups in the order that they are created. A calculation checks to see if there's been a change in the field data (in this case, Prev_Job_Num) and if there has, relooks up the data. This might work like an automatic script.

Take a look at www.databasepros.com and do a search for "field tracking". You might be able to find this technique and alter it to suit your needs.

But that technique is even more complicated than the one I offered.

My suggestion for you would be to have a button near where they enter the Prev_Job that runs the script.

If this isn't acceptable, another solution, not as easy but still better in my opinion than the automatic lookup technique is to use a plug-in like ScriptScheduler, which can run a script when the data in a field changes.

Chuck