9 Replies Latest reply on Mar 19, 2014 6:35 AM by philmodjunk

    Starter Database - Loading Tasks

    WilliamLivingston

      Title

      Starter Database - Loading Tasks

      Post

           I have been setting up a basic project database using the starter solution - projects.  I have loaded all my "projects", but now need to load all the underlying tasks and make sure they are associated with their respective projects.  I can load tasks without issue, the issue is i cant figure out how to load them so they are associated with a given project.  Any ideas would be appreciated.  

           wcliv

           note - this could very well be user error, but I have tried a few times with no luck and do have a basic understanding of filemaker and relational databases.  

        • 1. Re: Starter Database - Loading Tasks
          philmodjunk

               How are you "loading" the tasks? Are you importing data from a file?

               Is there any data in that file that identifies which task is to be associated with a given project? If so, what data is that?

               What version of FileMaker do you have? (The starter solutions differ with each version of FileMaker.)

          • 2. Re: Starter Database - Loading Tasks
            WilliamLivingston

                 Thanks.  FMPro 12.  I am loading via excel spreadsheets.  The issue I am having is when I import, there does not appear to be an available field to create the association.   The closest target field avaialble is "PROJECT ID MATCH FIELD", but i could not find the "PROJECT ID MATCH FIELD" associated with each project in the project database.  

                  

                 https://www.evernote.com/shard/s192/sh/7e1af84c-3b8a-48d0-ba50-2aee7760fca6/81650ee1e01589a7d48255ee3e725b20

            • 3. Re: Starter Database - Loading Tasks
              philmodjunk

                   When I asked this question:

                   "Is there any data in that file that identifies which task is to be associated with a given project? If so, what data is that?"

                   I'm not asking about the fields in your tables. I am asking about the data in your file that you are importing.

                   The match field is an auto-entered serial number in projects and a number field in the Tasks. It won't be a value that you import in most cases. Instead, you have to match values from the imported data and use one of several methods to then update the match fields involved in order to correctly link the imported data to other records in related tables.

                   To rephrase the question, If you just open up the file of data that you are importing, can you tell just by looking at the data in this file which task goes to which project? What data in that file enables you to do that?

                   And you also need to make sure that your imported table of Projects data has a value in the Project ID match field. This will only have happened if you enabled auto-enter options during the import. Otherwise, you have to discard and import all over again, or use Replace FIeld Contents to add in the missing serial number after the fact.

              • 4. Re: Starter Database - Loading Tasks
                WilliamLivingston

                     Understood.  Yes,  the name of the project identifies would be how you would associate each task with a given project. Regarding the ID, i undertand the concept and use it when I create other databses.  I can't seem to find a way to find out what ID was associated with each project I loaded.  I beleive if I could find that out, I would expect that I could manually enter the ID FM created with each project to the task upload and create the association that way. 

                     File:  https://www.evernote.com/shard/s192/sh/fea67a3d-5d90-4cb8-ac07-a942c7f1c3ea/cc8778c908eb58d2e5eb8412a59707ac

                     In the screenshot, I highlighted the data that identifies the project.  The import does not allow that data to be imported  to the "project" field

                     Hope this clarifies and thank you for your help.  

                      

                • 5. Re: Starter Database - Loading Tasks
                  philmodjunk

                       If you have unique, data-entry error free, project names and you imported them into projects, we can work with them to get the needed ID numbers copied over.

                       But I will repeat something from my last post:

                       

                            you also need to make sure that your imported table of Projects data has a value in the Project ID match field.

                       If this is not the case, you'll need to correct that deficiency before the following method will work.

                       Importing the tasks data must be imported on a layout based on Tasks if you do the import manually (without a script). What we need is a relationship link that allows us to match to records in the Projects table by project names so that we can copy over the match field ID's from projects. You can't use the Project field in tasks as it is a calculation field and you can't import data into a calculation field. So open Manage | Database | Fields, and add a new text field to tasks for the project name. I'll name it ProjectName in this example.

                       Then click over to the relationships graph in Manage | Database and select the Projects table occurrence by clicking it. Then click the duplicate button (Two green plus signs) to make a copy of this table occurrence. Drag from Projects 2::Projects to Tasks::ProjectName to link these two tables by ProjectName. Now click OK to save your changes and exit Manage | Database. Go to the Tasks layout where you will import your tasks data. Use the field tool while in layout mode to add the Project ID Match field to this layout. You can remove the field when you are done. You could also duplicate this layout so that you can make changes in the copy without modifying the original and do your import on the copy of this layout.

                       Now import your records and be careful to map the project name column to the new ProjectName field when you do so.

                       Once you have imported your records, click to put the cursor in the project ID Match field and use Replace Field Contents with the calculation option to enter the value of Projects 2::Project ID Match Field into this field for every record that you just imported.

                       If this is a one time operation, you can now delete the ProjectName field and the Projects 2 table occurrence as they should no longer be needed.

                  • 6. Re: Starter Database - Loading Tasks
                    WilliamLivingston

                         Thanks.  I am having trouble getting this to work.  Rather than detail my issues, would this work?  I found the project match ID that is assoicated with each project .  can I add that to the import of tasks so that the tasks are associated with the appropriate project? 

                    • 7. Re: Starter Database - Loading Tasks
                      philmodjunk

                           Nope. The data for this match is not in the data you are importing. That's why you have to use the project name to find and look up the value of the Project ID Match Field.

                      • 8. Re: Starter Database - Loading Tasks
                        WilliamLivingston

                        Yes, in my original post I did not have it. Through some tips you provided i can now add the project match id to all tasks (I found out how to get the project match id via the layout screen).  The task table allows for importing project match id, so my idea is to append the relevant project match id to each task so when tasks are imported they will associate with the appropriate project. 

                             Would that work?
                        • 9. Re: Starter Database - Loading Tasks
                          philmodjunk

                               It will work, but sounds vulnerable to data-entry errors. It's your database, but what I described seems easier and safer.