7 Replies Latest reply on Feb 27, 2013 12:48 PM by Mike_Mitchell

    Relationship with multiple intervals

    larsheise

      I know I can construct a find where I find records all the records in a table that as an example have id’s within the range of 1000..3999 and new find 5000..5999

       

      But is it possible to create a relationship that from another table shows the same records in a portal ? ( I need the sum of another field in those related records )

       

      Or if not is it possible to construct a calculated field with an Execute SQL statement that does the same ?

       

      Lars

        • 1. Re: Relationship with multiple intervals
          Mike_Mitchell

          Hello, Lars.

           

          Yes, both of these are possible. The relationship one is a little ugly, but still possible.

           

          1) To create the relationship, create a field that has a list of all possible IDs in it. For example:

           

          1000

          1001

          1002

          .

          .

          .

          3998

          3999

          5000

          5001

          .

          .

          .

          5999

           

          Use that as your "parent" (primary) key field. The "child" (foreign) key is the ID in the targeted table.

           

          2) Your ExecuteSQL statement is considerably easier:

           

          SELECT SUM ( [field you need ] ) FROM ( [ child table ] ) WHERE [ idField ] BETWEEN 1000 AND 3999 OR [ idField ] BETWEEN 5000 AND 5999

           

          (At least I think that's right. If not, someone smarter than I am on SQL will correct me.)      

           

          HTH

           

          Mike

          • 2. Re: Relationship with multiple intervals
            larsheise

            Thanks for your input Mike,

             

            Actually I did think of your first and "ugly" solution, but had hoped there might be a better one. In my soultion the user has to define these intervals in a field. It is for a user definable report. So when I run this report, I would have some kind of script to begin with to establish this relation.

             

            I guess the  SQL solution will be more elegant - only problem - I know to little about constructing this query.

             

            So the SQL should find from the child tables these records

             

            records in date interval xx..xx with ids in range 1000..3999 and another field should be empty

            +

            records in date interval xx..xx with ids in range 5000.5999 and another field should be empty

            + eventually more intervals.

             

             

            If I at least could get just one Query done - I would be able to loop through the different intervals and calculate the result via variables.

             

            So if somebody could help to construct this SQL - I would be very happy.

             

            Lars

            • 3. Re: Relationship with multiple intervals
              Benjamin Fehr

              I'm not sure if I got you right but wouldn't a relation with bolean-formula work?

              Ad global-fields "from" and "to" to yours invoice(?)-table:

               

              invoice::from ≥ invoiceSUM::ID

              invoice::to    ≤ invoiceSUM::ID

               

              I wonder how FM acts when you use repeating-fields definition for field "from" and "to" *).

              from= 1'000 | 5'000

              to = 30999 | 5'999

               

              *) be aware that if this works it might be a shaky solution and might fail in the future with next FM updates

              • 4. Re: Relationship with multiple intervals
                Mike_Mitchell

                Great idea. Unfortunately, it doesn't work. (See attached file.) Bummer, because it would be really cool if it did.

                 

                However, it did trigger another thought. You could follow the same logic and use a series of start / end globals, like this:

                 

                gRangeStart01

                gRangeEnd01

                gRangeStart02

                gRangeEnd02

                 

                and then use a multi-predicate relationship to pull it. Might be more than a pain than it's worth; you'd have to add more fields if you needed additional ranges, but it also could end up being easier if you wanted to make the ranges more dynamic.

                 

                Thanks! Good contribution!

                 

                Mike

                • 5. Re: Relationship with multiple intervals
                  Mike_Mitchell

                  Lars -

                   

                  You could try something like this:

                   

                  SELECT SUM ( [field you need ] ) FROM ( [ child table ] ) WHERE ( [ idField ] BETWEEN 1000 AND 3999 OR [ idField ] BETWEEN 5000 AND 5999 ) AND [ field that shouldn't be there ] IS NULL

                   

                  See how that works for you.

                   

                  Mike

                  • 6. Re: Relationship with multiple intervals
                    Benjamin Fehr

                    I would give it another try:

                    the fields 'from' and 'to' must be set to indexed "global"!

                     

                    It works at least without that repeating-field trick.

                    Though only 1 argument for 'from' and 'to' will work.

                     

                    Any input

                    from = 1 | 100

                    to = 200 | 300

                     

                    shows results from 1 to 300

                     

                    The relation-graph allows only AND arguments between the statements:

                              from ≥ ID

                    AND     to ≤ ID

                     

                    I have posted a feature-request long ago to expand this with further arguments like OR

                    You would need this and a feature to set parantheses in arguments to get done:

                     

                             (        from_a ≥ ID

                              AND to_a ≤ ID      )

                    OR     (       from_b ≥ ID

                              AND to_b ≤ ID     )

                     

                    But you can use that relation (without repeating-fields) in a script-loop to set an index (field Mark = 1) and have another relations 1=1

                    • 7. Re: Relationship with multiple intervals
                      Mike_Mitchell

                      No, sorry. Still doesn't work. Changing the storage to global makes no difference.

                       

                      But an "OR" relational join would indeed be nice.

                       

                      Mike