Making TO - filter of a main table, how?

[hlabonte]

I also posted this on the FMP7 General board, but have not gotten any responses.

I'm somewhat new to FMP7. How do I make a TO that is a filter of my main table? I have a table for report cards (containing multiple terms). It contains fields such as student ID, course, grade, term, etc. I need to filter the table based on the term field and only want to show a particular term. How do I make an occurrance of just term 1 or just term 2 (ex. term = 1)? I want a layout to only show 1 term.

Sorry if this is obvious, but this is my first database in FMP7.

Thanks in advance!

[Ender]

You can filter a relationship by adding a global to the parent table that corresponds to the field you wish to filter by in the related table:

Parent <=> Child

Parent::ParentID = Child::ParentID

AND Parent::gFilter = Child::Field

Enter the filter criteria in the global and you can view the filtered related records in a portal or, if you need to, jump to the related records with a Go To Related Records [ Show only related records ] script step.

[hlabonte]

Thank you, thank you, thank you! That's just what I needed to know.

[hlabonte]

Hmm, I thought I had it, but maybe you can clarify for me.

My main table has: contatenated key of stu_id & course_id & term

I defined a global called current_term

In my child table, I relate it to my main table with:

parent.stu_id = child.stu_id &

parent.course_id = child.course_id &

parent.current_term = child_term <---reading the global

I set the global field to 2,for term2, but I still see all records in my layout.

My new layout is displaying data from my child table only. Are you saying I HAVE to use a portal to see the filtered records? How can I make a new layout to just work with that portion of records?

What I really need is a full layout of the found set, because I need to do something with other portals based on that set of data.

Any ideas?

[Ender]

Well, using concatonated keys is different than using the multi-criteria relationship abilities of FM7. You CAN do this with concatonated keys:

In the student table:

ID_Course_Term (calculation, text result) = stu_id & " " & course_id & " " & current_term

In the enrollment?? table:

ID_Course_Term (calculaton, text result) = stu_id & " " & course_id & " " child_term

But it's easier and more efficient to use the multi-criteria relationships available in FM7. If you double-click the relationship line in the table occurence graph, you can add additional field matches without needing the concatonated calc fields. In your case:

student <=> enrollment

student::stu_id = enrollment::stu_id

AND student::course_id = enrollment::course_id

AND student::current_term = enrollment::child_term

[hlabonte]

Sorry, I AM actually using the FMP7 relationship to select the join keys...not by creating a concatenated field in the tables. I keep using terminology from other DB products that I use.

I am able to see the records that I need, but only when my layout is built off the parent table and I use a portal to view the child TO. Is there any way that I can create a layout to look directly at the child TO and see my group of records only for the term I need?

I also have a UI table which holds a series of global fields (such as current term). Can I use that somehow?!

[Ender]

Is there any way that I can create a layout to look directly at the child TO and see my group of records only for the term I need?

Add a layout based on the child TO, and use a Go to Related Records [] step to jump to the child records through the filtered relationship.

I also have a UI table which holds a series of global fields (such as current term). Can I use that somehow?!

It's easiest if the global used for filtering resides in the parent table, rather than a Globals table.

[hlabonte]

[Add a layout based on the child TO, and use a Go to Related Records [] step to jump to the child records through the filtered relationship.

I apologize, but I don't understand. I made a layout based on my child TO (term_reports_term1). My main table has the global field current_term set to "1". I put a few fields on the layout (just for reference), but the total records is the same as my parent table (all terms). Based on the relationship from my parent to the child, I should only see term 1 in my TO(term_reports_term1), right?

I'm attaching a screen shot of the relationship. Where would I put a GTRR step to change my total records (found set)? I must be missing something obvious here. If I still don't quite understand, I'll just attempt some other method to solve this layout need.

I appreciate your input.

[Lee Smith]

Hi hlabonte,

I also posted this on the FMP7 General board, but have not gotten any responses.

I have deleted your other posts in General Discussion v7.

Please [color:"red"] DO NOT DOUBLE POST in the Forum.

For one thing, it really isn't necessary because, this is only one list. In picking a topic, we only ask that you pick a Topic that best represents the sprit of your question ([color:"blue"]avoid the General areas, and Special Topics that are found at the top and bottom of the Main page, whenever possible when selecting your topic), and ride it to until a solution is found.

Double posting is frowned upon by the members for many reasons, but mainly it comes down to a waste of everyone's time.

If you have not received a response after 24 hours, it is perfectly all right to post to your own question with something like, "Still Need an answer", "is there an answer", "need to additional information", etc,

TIA

Lee

[Ender]

Go to Related Records[] is the script step you would use to jump from the parent table to the related records in the child table. Use the 'Show only related records' option, and set the destination layout to be the new layout based on term_reports_term1.

With this script in place, you can add a button to your layout that performs this script.