5 Replies Latest reply on Jul 24, 2009 6:31 PM by FluffyBear

    Relationships with static value

    FluffyBear

      Title

      Relationships with static value

      Post

      I have a question for anyone that have ever seen the answer, or for TSGal & Developers.

       

      When defining a relationship foreign key between tables as a way for sorting and limiting the resulting set,  why isn't there a way to specify a static value in the relationship definition?

       

      ie:

      Table A

      field_1

      field_2

      field_3

       

      Table B

      field_1

      field_2

      field_3

       

      Currently, we can do A.field_1 = B.field_2 ,  but there is no way to do A.field_1 = 'MOO'  where you can just type in a value right there.  We have 5 different subsystems that get data from 1 big giant table as a super holding tank.  Each of the 5 databases only need a subset of all the records.  We can limit to what we want, but right now we have to add another field in each of the sub databases to specify a global field with the value we want as a filter  (A.field_1 = B.field_2  AND A.field_3 = B.field_3  where B.field_3 hold the value of 'MOO').  

       

      This currently create extra fields in the table definition for the sub systems.  So my question is,  why can't we just have a feature where I can specify  A.field_3 = 'MOO' and type in moo right in the ER definition window?

        • 1. Re: Relationships with static value
          comment_1
            

          Theoretically, there's no reason why that couldn't be possible. Conceptually, it would be wrong - "MOO" is data, and data belongs in a field.

           

          If you like, you can define an unstored calculation field = "MOO" and use that as the matchfield in your relationship.

          • 2. Re: Relationships with static value
            FluffyBear
              

            That is what we're doing right now, but that is precisely what we're trying to avoid, as having that extra field create much clutter in the table definition.  Our system right now has close to 5100 tables, each with anywhere from 20 to 150 fields per table.  Imagine trying to do that across a few hundred tables, and with multiple fields for multiple ER since many tables access like 20 other tables.  We're litterally adding several such fields per table.

             

            As far as the matching go, while 'MOO' would be data, the way the ER system work in filemaker basically make it the equivalent of a SELECT sql statement.  There is no down side to specifying the static value in the ER definition, which would clean up the tables quite a bit and allow you to filter right then and there without having to go back to the table definition and create a new field.

             

            To me,  if something is in a database table, the cardinality should be more than 1,  if it's you got like 1 thing in a table that has the same value for all records, that's kinda bad DB table designs.

             

             

            • 3. Re: Relationships with static value
              comment_1
                

              "1 thing in a table that has the same value for all records" is practically Filemaker's definition of a global field. Filemaker has had global fields since the very beginning, I think (version 3 for sure), and I wouldn't expect them to change such a fundamental concept anytime soon.

               

              The most one could hope for, IMHO, is being able to use a variable as the matchfield instead of a "real" field.

              • 4. Re: Relationships with static value
                LaRetta_1
                  

                FluffyBear wrote:

                Our system right now has close to 5100 tables, each with anywhere from 20 to 150 fields per table.  Imagine trying to do that across a few hundred tables, and with multiple fields for multiple ER since many tables access like 20 other tables. 


                I cannot imagine 5100 different tables.  Each table should represent a GROUP, ie, Customers, Vendors, Invoices, LineItems and such.  What could possibly make up 5100 different tables for a business?  I suspect that you have many SAME tables which could be combined usiing one field called CATEGORY.  

                 

                Are you sure you are relationally sound and properly normalized?


                • 5. Re: Relationships with static value
                  FluffyBear
                    

                  It's nearly 10 years of development for a financial services company with many many programs, all very complex.  

                   

                  A lot of it has to do with the lack of FM using variables, so some tables has like 150 fields in the table definition, but 50-60 are global variables being used as storage for things.  While it all work,  it create a massive cluttering with extra fields.