12 Replies Latest reply on Apr 16, 2014 11:17 AM by mgores

    Find across tables

    mgores

      Title

      Find across tables

      Post


           I have a solution I built a while ago and it was working until this morning.

           It is a tracking solution  for our company that tracks "jobs" that  are received.  Each department has their own tracking file that is related to the Master.  In my departments file I had a script to list current jobs that performed a find on OMIT master:dateshipped >=1/1/1999. In the department file layout the dateshipped field and other common fields displayed are from the Master file.

           It was working fine until I started playing in the master file this morning and added some relation ships in the Master file back to the individual files.  I haven't gotten in this deep for a while, could the other relationship from the Master back to the Dept file have messed up that find?

        • 1. Re: Find across tables
          mgores

               Another possible cause?  Our network administrator did me a favor and saved a backup of the Master file so I have an archive and be able to delete some records that are several years old.  The problems is that it is an exact copy and is running on the server just like the real Master file.  It has the same relationships and everything still intact.  Could that be what is causing issues? The timing seems right.

          • 2. Re: Find across tables
            philmodjunk

                 Am I correct that you have relationships linking tables in different files to each other? You may want to review the external data source references carefully. External data source references use file names and one of several types of file paths to find and open the specified file when a reference to that file is needed. It's possible that one or more external data source references in one or more of your files is connecting to the wrong copy of your master file.

            • 3. Re: Find across tables
              mgores

                   BTW -  this is in FM11 for what it's worth.

                   The Dept files are all linked to the Master table in a separate central file.  When users create a new record in their Dept file it creates a record in the master table based on the  MasterID.  They currently have their own fields for things like job number, customer, part number, etc and through a script copy all of that info into the master file when they click the "done" button.  I know, not the best way.  So I've been trying to change it so that instead of entering the data into field based on the Dept table, they actually enter data directly into the fields of the Master table by changing the fields on the layouts that they are using.  I've experimented with my departments file and it has worked fine for quite a while.  This morning I tried doing the same for another department and the find script for my department's file stopped working. 

                   Perform Find [restore] - Omit(Master::status = shipped)

                   Perform Find [restore] - Omit(Master::shipdate>=1/1/1999)

                   Sort Records [Restore, no dialog]

                    

                    

              • 4. Re: Find across tables
                mgores

                     another strange symptom...

                     I tried doing finds for specific dates and would get results, however, searching for 3/25/2011 finds two records - one that actually has a date shipped of 3/25/2011 and one that has a date shipped of 2/17/2010.

                     (scratching my head)

                • 5. Re: Find across tables
                  philmodjunk

                       I don't really follow all the details of your design here, but it appears that you are performing a find on a layout based on your main table while specifying criteria in a related table. (whether that table is from the same file or a different file makes no difference here.)

                       If so, you are telling FileMaker to find all records in the main table that have at least one related record matching the specified criteria. But once the find is performed and you return to Browse mode, all related records for he records in your new found set from the main table will appear in portals and and fields from related tables not in a portal will show data from the first such related record even if that related record was not one matching the specified criteria.

                  • 6. Re: Find across tables
                    mgores

                         Design-wise there is a Master table on a central file and 4 separate department files.  The Master table contains the common fields like customer, job number, date rec, date shipped, etc.  The department files contain other department specific fields.  Previously there were only 4 department files that each department had customized pretty extensively.  I wanted to tie them all into a central file with as little disturbance to the users as possible.  So my first thought was to set up a relationship in each Dept file to the Master so that each record created in the Dept file would create a record in the Master file.  I then had scripts that would copy all of the common data from the Dept fields to the fields in the Master.  The scripts would not always work because some users would do things differently and not trigger the "copy" scripts, so there was a lot of maintenance going back and fixing records that didn't get the data copied.

                         Then I had the idea of replacing the fields on the Dept layout with the related field in the Master table, i.e. on the layout for the Dept 1 file, based on the Dept1 table -replace Dept1::dateshipped with Master::dateshipped

                         That way, a when the users modify the fields on their layouts they are actually modifying the Master field and no scripts are required.

                         I had this implemented on my Dept table for a year and a half or so and it seemed to be working as I expected.  I was able to perform finds from the Dept layout like Omit Master::dateshipped >=1/1/1990 and show the list of jobs that had no shipped dates entered yet (i.e. current jobs).  Then yesterday I was going though the master file, fixing the other Dept records and getting ready to go into their files and switch the local fields to the equivalent Master fields when the "current job find" in my file stopped working.

                         One other monkey wrench in the mix is that last week I integrated the SeedCode free calendar into the Master file and was able to tweak it to display jobs on the calendar by their promise date.  Though every thing was still working after I did that, the SeedCode did want me to set up a few files to be indexed that were not indexed before.  In searching the forum I found a few cases where you had mentioned indexes getting corrupted, so I tried rebuilding  them as you described with no luck.

                    • 7. Re: Find across tables
                      philmodjunk
                           

                                I was able to perform finds from the Dept layout like Omit Master::dateshipped >=1/1/1990 and show the list of jobs that had no shipped dates entered yet (i.e. current jobs).

                           So you are finding records in Dept1 by specifying find criteria in a field defined in Master....

                           But what relationship matches the dep1 record to a record in the master table? Are you sure that you have a one to one relationship between the two?

                           If your dept1 record matches to more than one record in Master, your find maybe performing perfectly but your layout may not be displaying the data from the master record that matched the specified search criteria.

                      • 8. Re: Find across tables
                        mgores

                             The relationship is Dept1::MasterID = Master::MasterID, this is the same relationship that creates the record in the master table when a record is created in the Dept1 (or other Dept) table.  I was thinking that performing a find on the Master::dateshipped field should yield the related records that match the criteria, but what you just said is making me think it wouldn't necessarily do that. It should find all records in Master that match the criteria.

                             However, it keeps coming up with 0 records found, not too many.frown

                        • 9. Re: Find across tables
                          philmodjunk

                               If you are on a layout for dept1, your find will find all records in dept1 that have at least one related record in master. It will not find records in master.

                               Is the field in master of type date or type text? A text field will appear to show dates correctly but this type of find criteria that uses an inequality operator will not work if it's text.

                               And I would recommend that you use the following method to find records in dept1 that have some sort of data in at least one related record in Master::dateshipped:

                               Specify a lone asterisk (*) in Master::DateShipped

                               and you will find all dept1 records that have a related master record with a date in DateShipped.

                          • 10. Re: Find across tables
                            mgores

                                 The field in Master is date and I have it set to validate on entry.

                                 I tried the * in the Master::DateShipped field and found all 1363 records that are related to Dept1.  is there a right way to constrain that farther to show only those with the Master::DateShipped empty?

                            • 11. Re: Find across tables
                              philmodjunk

                                   That should only find records in dept1 linked to a record in Master where DateShipped is NOT empty.

                                   If you make this an Omit request, it should then find all records in dept1 that either do not have a related record in Master or for which the related Master record has an empty DateShipped field.

                              • 12. Re: Find across tables
                                mgores

                                     Hmmm when I made it Omit, I had no records found. 

                                     I cheated and made a Dept1::DateShipped field and made a 1 line script to SetField Dept1::DateShipped to Master::DateShipped and triggered that OnModify for the field on the layout.  Then set up the find to Omit Dept1::DateShipped >=1/1/1960

                                     That seems to have it working again.  But not sure why it was working when I did the find on Master::DateShipped before...