11 Replies Latest reply on Apr 13, 2012 11:38 AM by JessicaFiorini

    Converting my one task to one file setup to a one task multi-file setup



      Converting my one task to one file setup to a one task multi-file setup



      I have figured out that my current one task to one file setup in FM is not going to work in the long run. I would like to revamp it a little to reflect how our project workflow seems to be headed. Right now, I have a system in which a writer is assigned to create one day's worth of lessons. I will need the ability to assign up to 6 weeks of lesson creation at a time.

      I have two tables; lessons and tasks. I currently create a task from a lesson and share a lesson id number and create a new task id number (both serial, auto-created). Lessons have a set naming convention called the container name that follows this pattern: 01_01_01_01, 01_01_01_02, 01_01_01_03, etc. I perform a calculation on the task table to produce this container name on the task record.

      I would like for the editor to go to the first record in the desired lesson, create a task from that lesson record and have that task refer to all lessons that match the first two positions in the container name. So, can I create a new task for 01_01_01_01 and then choose to assign the same task for all 01_01_ lessons?

      My thoughts are to create a search that finds all the 01_01 records and assigns them to the task. I am unsure how to grab the inital 01_01 from the container name fields. And I am unsure the best way to display my data. I've been trying some parsing techniques but I don't quite understand the structure. Thanks in advance.




        • 1. Re: NoFields

          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?

          • 2. Re: NoFields

            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.

            • 3. Re: NoFields

              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

              • 4. Re: NoFields

                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?

                • 5. Re: NoFields

                  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

                  • 6. Re: NoFields

                    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?

                    • 7. Re: NoFields

                      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.

                      • 8. Re: NoFields

                        Thanks Phil. That makes sense. 

                        • 9. Re: NoFields

                          Hi again,

                          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.

                          • 10. Re: NoFields

                            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...

                            • 11. Re: NoFields

                              Ain't that the truth. Had an extra Lesson ID holding up the works. Thanks!