Jump to content

how to find records with no related records?


Jason H
 Share

This topic is 2629 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I'm trying to create a report that shows mee all Tutors that are not tutoring a course.

The tables involved are:

Tutor --> TutorCourse --> Course

Where TutorCourse is a join table.

 

It's easy to see which tutors have courses as that is prcisely what is stored in the join table.

 

The approach seems to be to interate through the Tutor IDs in the Tutor table and see which one's do not have a relation to TutorCourse... but I don't know how to do this.

 

Is this even a good approach??

 

Jason

Link to comment
Share on other sites

From Tutors, perform a find in your TutorCourse table.  

 

It would look like this:

 

Enter Find Mode [ uncheck pause ]

Set Field [ TutorCourse::TutorID ; "*" ]

Set Error Capture [ On ]

Perform Find [ ]

#  this finds all Tutors with courses assigned

Show Omitted

Link to comment
Share on other sites

  • Newbies

LaRetta's method is most efficient.  You could also create a field at in the Tutor table that counts the number of courses which would probably be helpful in determining how to spread things out.  You could also then find and sort on this field from there on.

Link to comment
Share on other sites

You could also create a field at in the Tutor table that counts the number of courses which would probably be helpful in determining how to spread things out.  You could also then find and sort on this field from there on.

 

I should mention that a calculation in Tutors which counts a related table would be an aggregate and unstored.  If you need to simply display the counts from a related table then that is fine but It would be VERY inefficient to then sort or find on this aggregate calculation in Tutors.  Always avoid searching or finding on unstored calculations.  Searching a related ID is searching a stored field.

Link to comment
Share on other sites

This topic is 2629 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Similar Content

    • By eXcelisys
      Dogged by a Slow FileMaker Database?
      Cache in on this Quick Tip to Improve Layout Load Times & Report Generation
      By Joe Cellino, FileMaker Developer

       
      Have you ever wondered, “What is taking this report so long to generate?” or “Why does this layout take so long to load?” It may be that your FileMaker fields are set up inefficiently. If you have unstored calculation or summary fields on your layout, this will most certainly be the issue. Just as the names indicate, “unstored” calculation fields and “summary” fields don’t store any data. They must calculate the data each time the field is displayed. It may be easier to use unstored calculations in order to get your database functionality working, but this will inhibit your system from scaling with your business in the long run. The good news is — you can improve FileMaker performance with this data caching trick. Read More - > https://excelisys.com/dogged-by-a-slow-filemaker-database-check-out-this-data-caching-tip/
    • By NetCov
      Good Morning,
      I need help trying to create this report as I can not figure out the best method of doing this. Here is what I need the report to do. My report is a sorted list of technology assets by location name. In each sub summary I need to list the count of all different types of devices. The device types are a value list which has all the items such as “Desktop, Laptop, Firewall, Cellphone” in the list. How can I get the totals for each location of each of these devices to appear? 
      Please see the image below for an idea of what I am talking about.
      Thank you in advance for your help!

    • By Kilroy024
      I have a customer database and with a customer table and a service call table.  I am looking to create a report that would summarize total payment received in each month and a total for any given year with a grand total of every year.  Not sure how to set up the layout part for the Year and Month so that they will produce the desired result.
      Thank you for any assistance.
      Greg
    • By ejolesch
      I have a database made up of 3 tables, Company, Event, and Registration.
       
      I have created a report showing all of the events each company attend, summarized by Company.
       
      What I am trying to figure out is how to create two different reports
       
      One Report showing only the Companies that are attending only one event
      and 
      One Report showing companies that will be attending more than one event.
       
      Each report should be able to show the name of the company and the event or events they are attending
       
      Can anyone offer me a suggestion or guidance as to how to accomplish this?
       
      Thanks
      Elliot
    • By Stu412
      I have several reports which I'd like the user to be able to select from a list and then have FM print overall upon one button press, rather than the current set up I have which is a button on each report which fires the relevant print script.
      To add a little layer of complexity to the process, the reports need a letter index added to them prior to printing.  This is because, depending on the circumstances, in one instance Report XYZ could have an index position of say, 'D', but the next time, due to an additional report being required, Report XYZ would have the index position of 'E'.  This index position needs to be visible on the report.
      The reason I'm posting is because I'm not really certain where to start on this one.  The one thing I do have is a table which lists all the reports and currently allows a user to enter any report via a portal list based on that table.
      Any thoughts or suggestions greatly appreciated on this!  Thanks
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.