5 Replies Latest reply on Oct 18, 2012 10:06 AM by philmodjunk

    Redefining relationships for one database

    JanMoreland

      Title

      Redefining relationships for one database

      Post

           I have one database with thousands of records, each record has a time and temperature reading and an index. Each set of records can record time every 5 minutes and have up to 2016 records. I have another database with case data that uses this time/temp database as related record. So one case has one ID that points to up to 2016 time/temp records. But when I display my related records for that case, I only want to use a certain range of the time/temp records. I have written one script to find out what the starting and ending index numbers are that are applicable and I save those index values in the case-record. In order to execute that script, my initial relationship is the ID on the case-record that matches the ID in the time/temp records. THen I set up a new relationship - for display purposes - with matching IDs and the saved starting index >= the index on the time/temp record and the ending index <= the index on the time/temp database. That brings no results - I can't display those records in a portal or use them in a script. I'm sure I am doing something wrong because I assume it is a valid thing to do?

            

            

            

        • 1. Re: Redefining relationships for one database
          philmodjunk

               What you describe should work but there are easier ways. (To see why it's not working I'd have to see what you've done in more detail.)

               Are you trying to set up a portal to display this data?

               Are you using FileMaker 11 or newer? (Required for portal filters)

               If you answer "yes" to both questions, you can set up a portal based on your ID number relationship and use a portal filter to filter them down to a specified range of time/date or timestamp values.

               You can also produce a list or table view of your data using Go To Related Records pull up  a found set of all records with the specified ID number and then you can enter find mode and constrain the found set to a range of values.

          • 2. Re: Redefining relationships for one database
            JanMoreland

                 Yes to both questions - I am trying to use a portal and I am using  What you suggest does work but I am also trying to use this limited set of records in a chart and I don't see any way to filter on the chart. Any ideas about at?

                 In general, though, I can have a relationship defined that says start index from first DB is >= INDEX from second DB AND end index from first DB is <= same INDEX from second DB?

            • 3. Re: Redefining relationships for one database
              philmodjunk

                   For charting, I suggest the second option--pull up the related records in a found set. The set up for your chart will be simpler when based on the records in a found set in many cases.

                   

                        In general, though, I can have a relationship defined that says start index from first DB is >= INDEX from second DB AND end index from first DB is <= same INDEX from second DB?

                   Not quite. What you describe requires three fields--2 to specify the range of values one the "one" side and 1 to specify the value on the "many" side of the relationship.

                   Say you specify two fields in your parent table as Parent::MinIndex and Parent::MaxIndex

                   Then this relationship is possible:

                   Parent::MinIndex < Child::Index AND
                   Parent::MaxIndex > Child::Index

              • 4. Re: Redefining relationships for one database
                JanMoreland

                     I apologize for being unclear - I do have a minimum and a maximum on my parent but they are pointing to just the one index onthe child. In the setup forthe relationship, I have parentID=childID AND parentMIN >= childINDEX AND parentMAX >= childINDEX.

                     About the found set for my chart - do I just includefinding the records in a script prior displaying the chart?

                • 5. Re: Redefining relationships for one database
                  philmodjunk

                       Note the difference between:

                       I have parentID=childID AND
                       parentMIN >= childINDEX AND
                       parentMAX >= childINDEX.

                       That you posted.

                       and what I posted:

                       Parent::MinIndex < Child::Index AND
                       Parent::MaxIndex > Child::Index

                       For the chart, you can use either Go To Related record followed by constraining the found set or you can perform a find for the records. Either way works.