self-join relationship for filtering

[dar]

Hi. I'm rather new in this Filemaker but am very excited in its possibilities.

Background:

I have a prospects database. I want to filter the records by StateCategory using filterStateCategoryCriteria (global field). So, I created a self-join relationship between these 2 fields.

StateCategory is a calc field dependant on State. It generates 3 categories - Lead, WIP & Product.

I am trying out 3 layouts - Table, Form and Portal

Problem 1:

The Portal works but doesn't display the "State" correctly. Why?

Problem 2:

The Table & Form layout is giving me repeated records (about 7 times) and it's not filtering correctly. Why?

I've taken hours on this . Somebody please help! Teach me what's wrong. I've attached the file.

prospects.zip

[-Queue-]

1. The State field is not from the related TO. It is from the current table. Double-click on the field in Layout Mode and change it to Prospects 2::State.

2. Relationship::field is always going to be the same field on the same record (the first related one), if you are referencing it specifically. A portal is required to show more than one related record's data. Your table and form layouts will not work. If you attach a script to the radio button field, you can force only the filtered ones to be displayed by using Go to Related Record [show only related, Prospects 2]. You will also need to change the related fields to be fields from the current table to see the desired data, by modifying the Layout Setup to show records from Prospects. The same goes for the form layout.

I've modified your file to include the changes.

prospects.zip

[dar]

Queue! Thanks so much for your help! I really appreciate it. Prob 1 was silly mistake, Prob 2, I don't think I could have figured that out. Well, that adds to my learning process. I'm glad.

[dar]

Hmmm... I've just discovered an anomaly. If the "filterStateCategoryCriteria" does not match any "StateCategory" records, it will maintain display of the last set of records. How to overcome this?

[-Queue-]

On which layout? The script will make the table and form layouts display no records whatsoever. The portal filter performs similarly. You can test if there are any related records on the table or form layout by adding If [not IsEmpty(Prospects 2::Prospect ID)] directly before the Go to Related Record step, and Else Show Custom Dialog ["There are none."] afterward.