Multiple Contact fields in a Project

[LaRetta]

I need help clarifying the best approach for this solution. It is an older solution being completely rewritten so the structure will be different in build after this next release we're working on but for this one, I still want the best solution possible. None of the existing process can be changed since this setup is used substantially throughout the existing solution.

Situation:  There are many Contacts involved in a Project. Only one person can hold each of these Contact Types (applicant, owner, tenant). There are several of these but I've kept it to three for simplicity. The current structure has been replicated in the attached fp7 file for clarity. Future version will move these Contact/projects to a join table but currently this is what we have.

In the graph:  The blue Contacts table occurrence is used to group all of the Contacts associated with a project into a single 'team.  The green Contacts table occurrence is used for reports - the id_Contact records are found and the global gFoundContacts in Projects is set with the found contact Ids. It is necessary because of another twisted structure issue which we cannot correct in this build.  I believe the pink table occurrences are worthless here - they are fields placed directly onto Project layouts with 'allow creation on'.

The need: From the reporting layout, we need to perform a find which produces a found set of all project applicants or all project Owners etc. It would normally be simple to search the Projects table directly if it were 1:n from a join table, looking to the Project::id_applicant and search for * but I do not have that kind of relationship.  I cannot use the blue table occurrence because it includes all the Projects' contacts.

For example, if in Projects and I search for * in id_applicant (which is what I would want), I get 3 Contacts. This is correct. If I perform this search from the blue Contacts, I get 8 because it includes contacts which are owners and tenants as well.  I cannot use the green table occurrence because if the global is empty, it can't search Projects at all or I would need to place every contact ID in the global before performing the search and there can be 30,000 contacts or more. YUK.

I considered ExecuteSQL() - simply pulling the values out of the id_applicant (when searching for applicants) etc but still I would need to set the global in Projects which could result in a multiline 20,000 ids long ... and then GTRR to Contacts Found for the resulting list.  If I don't use this global then they could not pull data from the Project for display in the list.  Or I could create a summary ListOf in Projects for each contact field (type), YUK.  Or I could loop the Projects after I performed the find, set a script variable then set the global ... all options appear to suck.  

I can change this report list to any other table occurrence but I really don't want to create layouts for each of these Types.  I would like to use the same, single layout (no matter it's table occurrence in the backend).  It seems to clarify for me why this type of structure can be a problem. Oh sure, I could close my eyes and begin adding calculations (all of which would be unstored) and table occurrences everywhere and multiple layouts but I am looking for simple answers and fast results, LOL, and I am hoping very much that I am missing some obvious simpler solutions.  Surprisingly, after 10+ years in this business, this is the first time I have encountered this type of structural issue.

Anyone who has hung in there to read this and view the file, bless ya very much.  It is major reminder that 'like fields' should be a table ... I didn't build this, by the way.  

 

MultipleTypeContacts.fp7.zip

[comment]

Perhaps I am missing something here (it's a long post!) but why can't you use:

Only the expanded TOs need layouts.

Edited June 1, 2015 by comment

[LaRetta]

Hi Michael,

In your example, I could use the index which is what I want.  I was hoping not to have to add all the additional table occurrences (there are five contact types in total) but I believe it is the most light-weight approach of all.  Thank you for hanging in there to read this.  :-)