1 2 3 Previous Next 68 Replies Latest reply on May 8, 2012 10:02 AM by JessicaFiorini

    Attaching multiple files to multiple records?



      Attaching multiple files to multiple records?



      I have a database that is tracking the writing of math lesson manuscripts. I have two related tables. One table is comprised of the immutable lesson information. One table is the task record. They are related through a Lesson ID serial number.

      When I receive a manuscript from a writer, I save a reference into a container field on the appropriate task record. And change that status to review for our math experts. This works well for now, as we aren't recieving huge deliveries of manuscripts yet. It will be very tedious, at some point, to attach these manuscripts manually.

      Some further information is that the file paths of these manuscripts will almost always be the same except for a couple of unique digits that exist in the file name and the lesson name.

      Is there a way to do a search in the lesson table for the appropriate collection of lessons and then attach files to individual task tickets based on a combination of the file path and the unique digits? So can I look for geometry grade 7, which results in 8 records, and then through a looping script use the file path to determine which file needs to be attached to which record? resulting in that lesson 070301 gets the file 070301 and so on?

      Does this make sense?

        • 1. Re: Attaching multiple files to multiple records?

          In your example, the value 070301 exactly matches to the same digits in the file name. Is this always the case?

          If so, you can right a script that allows you to select a file for inserting, then a script inserts the file and captures the file name. It can then use a relationship based on these digits to link the record with the inserted file to the matching task record or records.

          How consistent is the format of these file names? That can affect the calculation needed to extract these digits from the file name.

          You could have this relationship:

          Files::Digits = Tasks::Digits  

          You would insert the file with the "store a reference" option enabled. This stores the file path to the file in the container field. Since the file path includes the file name, Files:::Digits can be defined as a calculation field that extracts the digits  inorder to establish a relationship to a task record.

          If you put these files in the documents folder, you can even set up a script that loops through the files in the documents folder and imports each in turn. (There's a get function that lists all files in Documents.)

          • 2. Re: Attaching multiple files to multiple records?

            The format is hard-wired so it is always the same. I will try out what you suggest. Be prepared for the torrent of questions or praises dependent on the result.

            • 3. Re: Attaching multiple files to multiple records?

              Oh Phil,

              I believe I am out of my depths a little. How do I extract the digits, if the format is 07_01_01_01? I've set up the relationships but I'm not clear on how to write the correct calculation. And agian, the naming conventions are hardwired so the lesson and the file have the same numbering system.I've checked out some other posts that extract numbers and places them into date fields but I can't seem to figure out the correlation.




              • 4. Re: Attaching multiple files to multiple records?

                What is the desired result? Do you need to remove the text as is from the file name or do you need to reformat it to something such as:


                A filter function could be set u that strips out all characters except 9876543210 if that's the issue here.

                Not sure where date fields have a role here as this seems to have one more pair of digits than I'd expect for a date. (I could read that as July 1st, 2001, but then I have an "01" left over.)

                • 5. Re: Attaching multiple files to multiple records?

                  Oh it's got nothing to do with dates. I just saw some posts that extricated digits from a string and transformed them into the appropriate format. I was thinking I had to do something similar. What I need to do is if the task record lesson name is the same as the last part of the file path to upload that file into a container.

                  • 6. Re: Attaching multiple files to multiple records?

                    And that leads us back to this question:

                    What is the desired result? Do you need to remove the text as is from the file name or do you need to reformat it to something such as:


                    I can anticipate two potential issues for you:

                    1) extracting the file name from the container field

                    2) formating the digits from the file name so that they perfectly match the value in the related task record.

                    • 7. Re: Attaching multiple files to multiple records?

                      I may be confused here. I would like to auto-attach files to the task record by grabbing the lesson name and plugging it in the correct file path and then uploading that file to the task record. I'd like to be able to batch this task so that FM checks to see if there are any matching file names/lesson names and auto-uploading.

                      Have I made a mess of things?

                      • 8. Re: Attaching multiple files to multiple records?

                        Not that I can see, but you gave an example value of 07_01_01_01. I'm asking for a more detailed description of what problem you are having with that. Keep in mind that I only know what you tell me and what I deduce from those descriptions.

                        I'm assuming that you have file names such as: SomeText07_01_01_01.doc or SomeText07_01_01_01.PDF

                        and the "SomeText" might not be part of the file name.

                        Then you are trying to match this file to a record with either 07_01_01_01 or 07010101 or some other format in your tasks record.

                        So where exactly do you encounter trouble? Extracting the data from the original file name? Reformatting the digits extracted to match the value in the task record? or Both?

                        • 9. Re: Attaching multiple files to multiple records?

                          Thanks for your patience. The file names and the lesson names are identical in that 07_01_01_01 (domain, level, week, lesson day) format. I place all the files in a single directory. I would like to do a search on the lesson record::lesson name for 07_01_01, which will result in 5 records. I thien want to run a script that has FM search the set file patch for a matching file name and auto upload the file to a pre-assigned task. I guess my problem is, how do I get FM to compare the lesson name in the database and the file name on the server?

                          So, really, no reformatting is necessary. More a get, compare and load if match. My main problem is in the compare phase.


                          Is that better? Maybe I have friday brain?

                          • 10. Re: Attaching multiple files to multiple records?

                            That help.

                            Wouldn't it be simpler to import all the files into a table in a way that the files are automatically linked to the task record with the same identifier? That way, when you find the task record, you find the file or files already linked to it.

                            • 11. Re: Attaching multiple files to multiple records?

                              I'm not sure if that would work. These files are created after the lesson records and come in at different times from different places. Also, a certain amount of editorial tasks need to be done to them. It kinda works like this:

                              1. I assign a writer a unit of lessons to write by creating a task linked to the lesson record.
                              2. We receive the lesson.
                              3. I move the lesson to the appropriate folder on the server.
                              4. I open FM and go to the appropriate task.
                              5. I attach the file and mark it ready for review.
                              6. The reviewer opens the file directly from the container by double clicking.
                              7. Either the file is accepted and the task is closed or they mark it for resubmit.
                              8. I assign it back to the writer and the whole process begins again.

                              So we may get a bunch of lessons at once or we may not. Also, the files change content but the task remains the same as a manuscript cannot be accepted until the review process is complete. I was thinking that when we recieve all of Algebra Level 1 (writers hand in entire units) that I can auto upload all the files to the apporpriate tasks. From there, things will have to go more by hand. But that intial group attach at this point is very tedious as it is done singularily by hand. Also, these files are more components of the lesson rather than the entire lesson itself. The end lesson manuscript is comprised of all these disparate pieces, so it's not like these files are indicative of the final product.

                              Do you think I should still import files like you say above?

                              • 12. Re: Attaching multiple files to multiple records?

                                Seems like we are describing the same thing. What I have in mind is a "batch upload" to a table and since the file name includes the needed data to match to a task record, the process of inserting the files into containers automatically links them to the relevant task record via a calculation field and a relationship to the task record table.

                                If we start by dropping the files into the documents folder, I think we can automate the whole process--including moving the new files from the documents folder to a designated location set up for their storage--and not need to acquire a plug in or write a system script in order to maniplate the files.

                                I haven't actually done this as I only recently noticed a get function that seems to fill in the missing part of the puzzle that I previously thought would require a plug in or system script--the ability to get a list of the files stored in a given location.

                                For my own ongoing education and enlightenment, I'm going to gin up a small test file. If it works like I hope, I'll post a download link from a share site so that you can take a look at it for yourself.

                                • 13. Re: Attaching multiple files to multiple records?

                                  OK, that was fun. What I thought would work DOES work.

                                  This demo file moves all files found in a folder named "InsertFileFolder" located in your documents folder to a folder named "FileArchive" located on your desktop, inserts references from their new location in FileArchive into new records in the database, with a calculation field that extracts a portion of the file name to use as an ID to link the files to records in the TaskRecords table. The task Records table is set up with a portal to the related table of container fields so that more than one document can be linked to a given Task record.

                                  You can create the two required folders with the specified names and in the specified locations, drop some files into the InsertFilesFolder and click the button to see this happen.

                                  If I were to set this up for a real database, I'd probably add a shortcut to the InsertFileFolder on my desktop and set up the FileArchive folder in a different location, but these make for convenient locations for demo purposes as I can use get functions to compute file paths to each.


                                  The main limitation to this method is that it only works from the documents folder as it relies on a get function that lists all items inside this one folder.

                                  • 14. Re: Attaching multiple files to multiple records?

                                    Oooh. Fancy. Awesome!

                                    Is there a way to point to folders within FileArchive? For example, can I point all the 07 files to live in a 07 file within FileArchive?

                                    1 2 3 Previous Next