12 Replies Latest reply on Aug 17, 2009 8:38 AM by rsmcomputer

    Displaying a found record multiple times

    rsmcomputer

      Title

      Displaying a found record multiple times

      Post

      I have a database that consists of job records, many with a job that spans several days.  In these multiple day jobs, the records list each day involved (i.e. 8/11/2009, 8/14/2009, 8/24/2009).  I would like to do a find based on a range of dates (8/11/2009...8/24/2009) that returns a list of job records for each day.  But, my finds only return the first occurance of each job, not each occurance as I need.  I've tried setting up the job day field as a repeating field (this works for finding all jobs on a single day find) or creating separate fields (day1, day2...), with neither seeming to work.  How can I display a list of jobs by a range of dates and include each instance in a list?

        • 1. Re: Displaying a found record multiple times
          comment_1
             I'd suggest a table for Jobs (one record per job) and a related table for JobDates. Then do the find in the Jobs table, using the related date field.
          • 2. Re: Displaying a found record multiple times
            rsmcomputer
               OK.  I understand conceptually what you're suggesting, but am unclear how the relationship is setup, and how to populate the new table. Please guide this newbie.
            • 3. Re: Displaying a found record multiple times
              comment_1
                

              In the Jobs table, make sure you have a JobID field, defined as Number, Auto-enter serial number. All the fields that describe a job should be also in this table. In the JobDates table have a JobID field, defined as Number, the date field and any fields that describe a particular "occurrence" of a job.

               

              Define a relationship between the two tables as:

               

              Jobs::JobID = JobDates::JobID

               

              If you like, allow automatic creation of JobDates records (this will allow you to enter new JobDates directly in a portal). Place a portal to JobDates on a layout of Jobs.

               

               

              As for populating, I am not sure what you have already. I think your current table could become the JobDates table and you just need to add the Jobs table. What do you have now that uniquely identifies a job (i.e. has the same value for multiple occurrences)?

              • 4. Re: Displaying a found record multiple times
                rsmcomputer
                  

                To answer your question, in the "Jobs" table (I'm assuming this to be the existing, primary table that displays all fields?), I have a unique, serial number auto-entry "Contract #" field, one contract # per job.  The date field I am currently using is "Date of 1st Task", is a repeating field with 6 repetitions.  I can (and have thru my trials) populate six individual task1, task2... fields with a "Get Repetion" calc.  So, I can go both ways.

                 

                • 5. Re: Displaying a found record multiple times
                  comment_1
                    

                  Ok, if you now have one record per job, with a unique serial, then you can import your records into a new table in the same file. Import the unique serial and the repeating field, and in the import dialog choose to split the repeating fields into individual records. This will then be your JobDates table - almost ready to use.

                   

                  Make sure you have a backup before you start, as it's really easy to mess it up.

                  • 6. Re: Displaying a found record multiple times
                    rsmcomputer
                      

                    The import went well, but didn't see the option to split the repeating record -- so I chose to bring over the six date field data from the Jobs table individual Task1, Task2...Task6 fields.  All looks OK when viewing the table after the import.

                    So, for performing a Find of a range of dates, which field do I target?  Is this a single, or a multiple request over all six date fields from within the portal?

                    • 7. Re: Displaying a found record multiple times
                      comment_1
                        

                      You would have seen the option, if you had selected the repeating field as the source field to import.

                      The entire idea here is to create a separate record for each date - NOT six date fields in the same record (you already have that).

                       

                      I suggest you try it again and pay attention to the Import Options dialog you'll see after mapping the fields and clicking Import.

                      • 8. Re: Displaying a found record multiple times
                        rsmcomputer
                          

                        It seems so close, yet...   I did as you suggested and DID see the option for splitting into separate records, which I chose.

                        Reimporting did create individual task records for the one Job, and I see them all in Browse mode - Show all records.  

                        However, when I go to the Layout (in Jobs) that has the Portal (from JobsDates) and perform my date range find to cover at least three occurances of different task dates, I only get back the first dated task.   Stumped!

                        • 9. Re: Displaying a found record multiple times
                          rsmcomputer
                             Found another fly in the ointment -- when I make a change in the Jobs data, it doesn't update the corrosponding records in the JobsDates table data.  Will this mean that everytime I query a JobsDates, I will need to re-import or somehow otherwise intentionally update the data? 
                          • 10. Re: Displaying a found record multiple times
                            comment_1
                              

                            Once you have made the conversion, you should keep the dates only in the JobDates table (please refer again to my second post in this thread).

                             

                             


                            rsmcomputer wrote:
                            when I go to the Layout (in Jobs) that has the Portal (from JobsDates) and perform my date range find to cover at least three occurances of different task dates, I only get back the first dated task.

                            I am not entirely sure what you mean here. Searching the related date field (in a portal, if you are doing this manually) for a range should find all jobs that have at least one occurrence in that range.

                            You will only find one record for each such job - because (hopefully) there is only one job record for each job in the Jobs table. But each found job "carries" with it all its related dates in the portal.

                            • 11. Re: Displaying a found record multiple times
                              rsmcomputer
                                 Sorry, I've been away from the project for a few days due to a death in the family and other work committments.  I'll go back and review all the notes and try to see where I went wrong.
                              • 12. Re: Displaying a found record multiple times
                                rsmcomputer
                                   After playing around a bit with tables and relationships and re-reading your suggestions, I did get the dates to display as I originally requested.  Through the process, I've discovered I need to refresh my training/understanding of tables and relationships so I can better follow the logic and get this entire database project working as it needs to.  Thanks for your patience in working with this novice.