3 Replies Latest reply on May 11, 2014 12:45 PM by mark_b

    Relationship help

    chronister

      Hello all,

       

      I hope everyone is doing well and has enjoyed their weekends.

       

      I am hoping to get help with a relationship question. I am used to Access and MySql in that when I want data, I query it and get what I want. Simple.... the relationship aspects of FM to "query" data is kinda eluding me still.

       

      At it's simplest is this. Creating an Animal Shelter Management system. Animals table (animalID_PK), linked to MedicalTreatments (animalID_FK). In Med treatments, I want to get just the subset of treatments that are scheduled for the future. I don't see a need to create 2 tables when 1 table and a completed checkbox will suffice.

       

      So I have the first relationship taken care of.I can get Medical Treatments by animal, and view it in the animal records or in the med treatments layout. I have not decided if TreatmentsDue relationship graph item will be a portal or main layout (list view), but I need to filter out completed records, and then sort by date. Should be simple right??

       

      I created a duplicate of the Medical treatments table (upcomingTreatments) and joined it to the MedicalTreatments table and have my join fields set, but it is not filtering and I cannot for the life of me determine why. It is a T / F field and I have tried using it as Text and using "True" as my match item (using not equal to in the join relationship), And I have tried using numeric field type and 1 as the match field.

       

      Can anyone see why I can't create a list type layout with the upcomingtTreatments view and have the completed filtered out?

       

       

      med_treatment_match_FM.jpg

       

      Thanks Folks,

       

      Nate

        • 1. Re: Relationship help
          chronister

          For a clarification... I am desiring to have a layout with all upcoming treatments.... Basically a list of the treatments that are sheduled to take place or are overdue (not completed).. This is not linked in a portal to a specific animal (I actually have that one working fine and showing incomplete treatments for a specific animal) but this one will not do what I want.... or more correctly, I don't know how to tell it to do what I want... I assume this is doing exactly what I am telling it to do...

           

          Thanks,

           

          Nate     

          • 2. Re: Relationship help
            erolst

            See attached file for a way (several ways, actually) to show upcomings treatments related to a specific animal, or for all animals.

            • 3. Re: Relationship help
              mark_b

              Hi Nate,

               

              It looks like erlost has a great solution.  What I saw in your screenshot that could be your problem is your relationship to UpcomingTreatments.  Look at the tail of the diagram going to UpcomingTreatments. 2014-05-11_13-34-04.pngThe terminal shows that completedCompare can not be indexed, therefore it cannot show multiple records from that table. Is completedCompare a global field or a calculated field that references a field through a relationship. Since it can't be indexed, it can't show detail records in the UpcomingTreatments table.

               

              Cheers, Mark