Using multiple criteria relationships to create filtered layout

[jeremychance]

Hi, 

 

I am making a CRM database for my company and I'm having trouble understanding the fundamentals of using table occurrences and multiple criteria relationships to create specific layouts.

 

I have a Contacts table in a many to many relationship with a Tags table.  They are joined by a Tracking table that has a "Reminder" check box, a "Primary Partner" field (i.e. "Staff") , and a Next Contact date.  From here, I want to create layouts for each Primary Partner that only list all the records with the Reminder check box filled and sorted by Next Contact date. (Ultimately, I'm aiming to script automatic emails with these lists to be sent out at regular intervals).

 

The way I've attempted this so far is to connect two occurrences of the Tracking table by matching the Pkey to itself AND to compare the Reminder check box field, which returns "yes", to a verifyReminder field that simply contains the word "yes".  My assumption is that a layout created through this table occurrence would then show only the records with the reminder box checked, but I fear that I am making a fundamental mistake because the layout continues to show me all the records in the table.  I haven't even attempted to filter it by the Primary Partner, figuring I need to fix and understand this first.

 

I'm attaching my file.  The layout I'm focusing on is Contact_Tracking_All.  I'd appreciate any suggestions.

 

Best,

Jeremy

 

SITUDB2.zip 

[brian rich]

Hi Jeremy

 

The fields used in a relationship need to be indexable.  Change your verifyReminder field from a stored Global calculation to a stored calculation field by turning off the global storage option and turning indexing on.  Each tracking record will then have a usable constant value available. 

 

The self-join you have specified equates Pkey Tracking and verifyReminder on both sides of the relationship.  I think that you need to equate the field Reminder to the verifyReminder field to make this work as you want.

 

Brian

[jeremychance]

Hi Brian, 

 

Thanks for the suggestion! I'm still see all the records though.

 

I made the verifyReminder field an indexed calculation.  so now the relationship is:

 

Pkey Tracking = Pkey Tracking

AND

verifyReminder = Reminder

 

could it have to do with the context of the verifyReminder calculation?  or the lack of Fkey Tracking?

 

Thank you,

Jeremy

 

 

SITUDB2.zip

[brian rich]

Hi Jeremy

 

Why don't you move the field verifyReminder from the Tracking table to the Contact table. Then create a new instance of the Tracking table called (say) T4C and  join it to the Contact table with these relationships

 

Contact Table      Tracking Table

 

Pkey Contact    =    Fkey Contact

AND

verifyReminder  =   Reminder

 

 

Place a portal based on this relationship on the Contact_Tracking_Tags layout, and add the two fields T4C::Next Contact and T4C::Reminder to it.

 

You should now see Tracking records for the contact where reminder = yes in the portal.  You can test this out by clicking the reminder checkbox in the existing Contact_Tracking portal on that layout.  (You may have to use the Refresh Window command on the Records menu to make the portals update fully.)

 

HTH

 

Brian

[jeremychance]

Hi Brian,

 

That works!  Thank you for helping me.  

 

But what's the difference between the layout and the portal?  Why is that the layout still shows all the records while the portal shows just those with the Reminder checked?

[Fenton]

A layout has access to all the records of its table (base table). You can use a Find or Relationship (script) so that you only have a found set of particular records. A portal on the other hand shows only records that are related to its Relationship (or those filtered). So, basically, they are not the same kind of thing.

 

The method you used does not stop the other records of that table from being seen, just by the Relationship you've said the layout has. The relationship could however be added to the relationship if you added a portal to that layout. Which isn't enough to do what you want really though. 

 

So, to do what you want, you'd want to either view from "higher ground" (brian's method). Or by using a Find or Relationship script to filter down, when you went there. 

 

Basically, I've seldom every used anything except the basic table for a layout, unless it was a special layout for a separate relationship "group" on the Relationship Graph, or a special action (like saving a found set). Others may know more about this. And likely could say it better. I have trouble with words in general.

[jeremychance]

Thanks Fenton!