Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Find 2nd record with same field value and return a related field value


cpeyser
 Share

Recommended Posts

Hi,

 

I am trying to write a script that will identify a record by a parameter and then find a second matching record by a second parameter and return the value from that matching record to be used in a calculation field in the first record. Also, I want to mark or label the matching record as the second record and mark the original record as the first record in the pair (there are only at most 2 matching records for the particular second parameter mentioned). I assume this requires a loop. I'm not terribly experienced in scripting or programming in general and have made numerous attemps but to no avail.

 

More specifically, the script is to find all the teachers in my school (each one has a record in the database) who team teach with another teacher (I use the field 'CTT' - for collaborative team teaching - with the values 'CTT' or no value and I give each record a CTT ID that is a calculation field combination of the grade they teach and their 'CTT' value - ex. 3CTT for the 2 3rd grade CTT teachers or 2 for a 2nd grade non-CTT teacher), label the first of the pair encountered as the primary teacher (#1 as mentioned above) and the second teacher encountered as secondary (#2), and finally return the name of the second teacher to be combined with the first teacher in a calculation field called 'Teachers' for each record. If there is no second teacher, the 'Teachers' field would consist of the name of only the independent teacher ("John Doe"), otherwise it would consist of both teachers names that team teach that class ("John Smith / Mary Jane").

 

I would then use a second script in a 'teacher lookup' layout to show only the records labeled teacher #1 - all the independent teachers and all the primary CTT teachers (so as to avoid repeating visible records in terms of the 'Teachers' field since I imagine that, for each record, the 'Teachers' field has a value and in the case of the 2 team teachers, it has the same value so why show it twice)

 

I would post the scripts I've tried, but I have a feeling I'm totally on the wrong track. As it is, I have an initial script that sorts the records by teacher last name, resets their primary and secondary teacher values to #1 (ie. their number in their teaching pair) and which calls a second script. That script does a find on all CTT teachers and then runs a loop on them. In this loop it calls a 3rd script which runs another loop on the found group looking for the match for each record, labeling the first record found #1 and the second record found #2 and returning the name of the #2 teacher to the second script which then combines the two teachers names in the 'Teachers' field and continues its loop for the next CTT teacher to find its match.

 

I'm guessing the loop within a loop might be the initial problem. Either way, it doesn't seem to work. Does anyone know of any sample scripts I might look at that might help me figure this out? It's probably not close to as complicated as I imagine it to be.

 

It's not a big faculty so I could do this by hand, but I'm trying to set it up so that if teachers change positions within the school it will be automatically adjusted and so that the following year when teachers leave and join the faculty it will as well be automated. I'm running filemaker pro 6 and this is database is my first foray into scripting.

 

Many thanks,

Chris

Link to comment
Share on other sites

Basically, I'm trying to write a script to

 

1. find all the teachers in my school (each one has a record in the database) who team teach with another teacher (I use the field 'CTT' to signify a team teacher - for Collaborative Team Teaching - with the values 'CTT' or no value, and I give each record a CTT ID that is a calculation field combination of the grade they teach and their 'CTT' value - ex. 3CTT for the 2 3rd grade CTT teachers or 2 for a 2nd grade non-CTT teacher)

 

2. label the first of the pair encountered as the primary teacher (#1 as mentioned above) and the second teacher encountered as secondary (#2). I was looking for pairs by matching their CTT ID

 

3. return the name of the second teacher to be combined with the first teacher in a calculation field called 'Teachers' for each record. If there is no second teacher, the 'Teachers' field would consist of the name of only the independent teacher ("John Doe"), otherwise it would consist of both teachers names that team teach that class ("John Smith / Mary Jane").

 

 

I've tried with numerous loops and failed numerous times. I'm using Filemaker 6.0.

 

thanks

Link to comment
Share on other sites

I think the problem is in your basic approach. You should have a join table between teachers and whatever they teach (grades? courses?). The join table records that teacher X tought grade Y in year Z.

Link to comment
Share on other sites

Thanks for the suggestion. As a followup, since I am a relative newbie trying to take the leap forward into more sophisticated features of filemaker, how do i create a join table in fm 6? Is it documented in the Help resources?

 

Thanks

Link to comment
Share on other sites

Okay, so I figured out how to define a relationship between records in the same file, which is great, but when fm 6 does a lookup based on the relationship, how can I have it return the information from a second record and not the same record?

Link to comment
Share on other sites

Eureka! Your suggestion was the answer. Figured it out without scripts. Just needed new calculation fields using the joined tables via the newly defined relationship of the database to itself using the CTT Teacher ID field to match up the two team teachers.

 

Thank you so much.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use