I suggest you review your method for linking records and consider switching over to pure serial numbers for your links between tables. You can keep the existing fields with their complex format as simple text fields for "human" use if they are needed, but straight serial numbers are much simpler to work with and that simplicity carries over into fewer design headaches for you.
This isn't something you can do quickly, but if you can map out a gameplan for systematically overhauling your DB to make such a change one table's primary key at a time, it will improve the design of your database by quite a bit.
In the mean time...
I need to review your current tables and relationships. Is this what you have?
Lessons::LessonID = Tasks::LessonID
In what table are you creating new records?
what does 01_01_ identify here?
They are purely linked by an auto-generated serial number. The container name was more to let an editor know that the task they are working on refers to the correct lesson. Yes, my relationship is Lessons::LessonID = Tasks::Lesson ID. I am creating new records in the task table as the data contained in lesson table is fixed and unchangeable. 01_01 signifies that the Lesson is from level 1 and domain 1. The last two numbers are the week and day. So, I would like to assign a task that says create content for all lessons 01_01_01_01 to 01_01_06_05. Currently, I assign one "create content" task to one lesson. In the future, I will need the ability to both assign a task to a group of lessons and to an individual lesson. I was figuring that I could do a search for all the lessons matching the 01_01 signature and assign the whole group to the task by a task id auto-generated serial number.
Can a lesson record be linked to more than one task record? If so, you have a many to many relationship here and we need a join table. If not, you should reverse the relationship so that you use a primary key in tasks instead of a primary key in lessions:
Lessons::TaskID = Tasks::TaskID
But only if you don't have cases where there is more than one task record linked to a given Lesson record.
If you DO have a many to many relationship, you will need this relationship:
Lessons::LessonID = Lesson_Task::LessonID
Tasks::TaskID = Lesson_Task::TaskID
Yes, a lesson can have more than one task record. So, a join table would enable the one task to many lessons. Can you tell me a little why that works? Also, how should I handle the assigning of multiple lessons to a task?
Every time you need to link a given lesson record to a given task record, you create a record in the join table with the ID values of the two records you want to link. Assigning multiple lessons to a task would require generating a record for each lesson in the join table--each with the same task ID, but with different lesson ID's. This can be done with a looping script.
The most straight forward way to work with a many to many relationship is to set up a portal to the join table on a layout based on one of the other two tables. You can put a portal to Lesson_Task on your Lessons layout and see a list of all related tasks. You can add fields from the Tasks table to show details about each linked task into the row of this portal. You can also reverse this and use a portal to the join table on your tasks table to list all the linked Lessons records.
Here's a demo file you may want to examine for how to set up layouts that work well with join tables: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html
Got it. This is similar to the task log I created. I'm still unsure in how to allow a editor to assign the task to only the matching lessons with the 01_01 identifier. I would think a dialog box asking if they'd like to assign this task to all the lessons in a level/domain unit that when they answer yes then starts the looping script that does a find for matching records and assigns them to the task would be effecitve. I would assume I'd have to parse out the 01_01, do a search for those lessons and attach the multiple lesson id's to the task. How do I parse out the 01_01?
Set Variable [$Identifier ; Left ( Lessons::Identifierfield ; 5 ) ]
Enter find mode 
Set field [Lessons::IdentifierField ; $Identifier ]
Perform Find 
will find all Lessons records that start with the same first 5 letters as the value in the IdentifierField of the current lessons record.
Thanks Phil. That makes sense.
I put together a test file and everything worked. My script, the relationship, etc. When I placed it into the pre-existing database everything works except the Task table does not seem to relate back to a lesson. The Lesson ID is present but when I use the go to a related record script, nothing happens. Also, it seems I am unable to display any info on the Task record of the Lesson record, either through a portal or a field. This is not the case in the lesson_task table/layout. I have uploaded my relationship graph, if that helps.
but when I use the go to a related record script, nothing happens
This is typical of what you'd see if there are no related records to "go to"... Better check the values in the three tables to see if they match Id's like you expected them to.
I am unable to display any info on the Task record of the Lesson record
How did you design your layout? Remember that there could be multiple lesson records linked to your task. Did you use a portal to the join table with fields added from the Lessons table? If so and they are empty, this suggests that there is a problem with the relationship linking the join table to the lessons table or between tasks and the join table...
Ain't that the truth. Had an extra Lesson ID holding up the works. Thanks!