3 Replies Latest reply on Oct 24, 2013 6:57 AM by philmodjunk

    finding records with no related ones in a child table

    med

      Title

      finding records with no related ones in a child table

      Post

           I have a student table and an appointment table and need to browse students with no future appointments. So this is what I did:

           I created a global field in the student table and assigned to it the current date. Then I related the appointment table to the student table with the following relation

           student::code = Appointment:: code  ( code is the student code)

           student::datefilter < appointment::date   (datefilter is the global field in student table and has the value of current date)

           Now when I try to perform a find to browse students with future booking I have no problem but when I try to find the ones without I tried:

           Appointment::date =

           or Appointment::date ?

           or appointment::date < today

           None of the above is working, what am I doing wrong?

            

        • 1. Re: finding records with no related ones in a child table
          philmodjunk

               Use * with an omit request. There's a button to click for manual finds and the omit records script step can turn a find request created by your script into an omit request.

          • 2. Re: finding records with no related ones in a child table
            med

                 Thank you for the answer but I have been toying with this and cannot get it to work.  When I use omit with * it gives me nothing.

                 The only way I got it to work is to put in the find criteria as follows:

                 Omit appointment::date > $currentdate

                 and that is telling me that my relationship is not working properly!!! 

                 I must be missing something basic about relationships and filters through relationships. Is there any ADVANCED reading material about this.

            • 3. Re: finding records with no related ones in a child table
              philmodjunk

                   First, if you put a field from the related table that is never empty (such as the foreign key field) on your layout, can you see data in it?

                   If so,

                   Enter find mode

                   Enter an * and nothing else into that field.

                   Click perform find.

                   If you can't see data in that field, there is an issue with your relationship. By any chance, is the match field an unstored calculation field?