4 Replies Latest reply on Feb 20, 2013 1:54 PM by philmodjunk

    Indexing work around



      Indexing work around


           Hey guys,


           So I have three tables, Admin TO-DO, Personnel Records and Employment history.  Employment Histroy tracks start and edn dates for all our employees through a portal.  Several workers are seasonal so they have several start and end dates and in order to properly track years of service I decided to take this route.  The relationship between personnel records and Employment History is based on the serial Id of the personal Records and is sorted from lastest start date to oldest.  I have a calculation field in my personnel Records that calls upon the lastest start date and adds three months to that date.  That all works fine.  The problem is that since the calculation field calls upon records from another table I cannot index it and there for cannot use it in my relationship with the TO-DO list.


           What I've done with all the other realted TO-Do tables is a realtionship as follows.

           TO-DO Table X Task Table

           cCompleted (not equal to - too lazy to find the symbole) Completed Field

           CurrentDate + 1 month (equal or less then) Effective Date

           I cannot use this relationship because the last line calls upon a field that requires it to be indexed.  


           Any work arounds???

        • 1. Re: Indexing work around

               Is this really a relationship or a portal filter expression?

               Your example shows using the cartesian join operator and then lists several expressions that compare values.

               I think you are using a relationship, but have used the expressions in place of the calcualtion fields used in it to explain the function of the relationship, but would like to be sure.

               But if this were a filter expression, the limitation on indexed fields does not apply. Thus, you might be able to use a filtered portal with a portal filter expression that compares these values instead of defining it in the relationships.

               Another option that may work is to set up an auto-entered calculation to compute the date. This field will be indexable, but you may need to take extra steps to be sure the field updates corrrectly should a field from a different table be changed as auto-entered calculations fail to update automatically when the field that changes value is from a different table or record. (And this won't work at all if the calculation refers to get ( CurrentDate ). )

               And unindexed fields CAN be used in some relationships. It depends on the context. In a one to many relationship, both global and unstored fields can be used on the "one" side of the relationship but not the "many" side.

          • 2. Re: Indexing work around

                 So I've rejigged my relationships/design to make this work without the calculation fields but was forced to use a portal filter to only show all the full-time and part-time employees.  Is there a way to have this built right into the relationship?  As long as the employement type matches one in a list of approved types the link will be made?  this way my calculation count fields will work properly, right now it counts the records that have been filtered out as well.

            • 3. Re: Indexing work around

                   Sorry, immediately after I posted I thought of a way to make this work but it's not an easy solution.  I realized I could have a constant fields on the one table of all the job terms I want filterd out and then have

                   cJobTerm (not equal to) Employement History::Type


                   for this case it will certainly work but I'm still curious to know if there another way in case I stumble on this again someday and the list of wants is one of say 5 or 10 and the list of do not wants is in the hundreds, my method would be horribly tedious.

              • 4. Re: Indexing work around

                     With FileMaker 12, you have the option of useing the ExecuteSQL function which provides options not possible in earlier versions.

                     You could use this function to produce a return separated list of ID numbers and then you could use it as the match field in your portal's relationship.

                     For certain "read-only" applications, the calculation field could take the place of the portal all together if you use returns as the record separator and tabs as the field separator. you can give the field a scroll bar and use the Appearance tab in the Inspector to position tab stops to get nice columns of data. The result will look very similar to a portal, but you can't edit any of the data.