FM - Design & Strategy for New DB - Part II

Took all your advice and thought awhile and then it came to me:  Since both businesses are linked via Filemaker, why even create a new db?  Why not give the Realty agents access to the Property Management Leads file, but only those referred to them?  Just add two fields:  1) Company assigned to and 2) Realty Co agent assigned.  

So, question.  How do I allow them the full functionality of the layout, but restrict them from seeing ANY leads from the PM side?  A) Create a duplicate layout, but then associate all the fields to a new TO that is filtered just for the Realty Co?  However, I think this breaks the scripts associated with some of the fields and the portal filter at the bottom, etc.  B) A trick I learned:  if I create a TO over in the Realty DB with the same name, then when I create a new layout and paste all the fields in, the fields will be linked to that TO; I don't have to relink them all.  However, haven't done this with scripts.  If I import all the scripts first BEFORE pasting the fields, will the script triggers work?  Yet, any calc fields would not work, I think.  C) Just create security in the Leads db where each Realty agent can only see their own leads.  I could even create a dupe layout but leave it with the same TO, just remove fields I don't want them to see.  Then, if they so choose, I could have a button that would import their Lead into their Contact table on the Realty side.

(Sorry to go on so long, but I think best when written out.)
Who is Participating?
North2AlaskaConnect With a Mentor Commented:
To address your question, if you have the same TOs and layouts, then you can copy/paste or import your scripts without issues (or fewer issues).  If they don't exists, you can still import, but you will have to fix them.
I think the adage "If it's too good to be true, it probably is." could apply.  It sounds a little hackery.  (I just made that up... :-) )  Consider all the long term ramifications.  It sounds like the negative consciences have already started with having to create special layouts, scripts or privilege sets.  Think about the overall design and how it fits in.  It may seem easy today, but will bite you in the behind later.

If you maintain both databases, now you will need to consider both when doing any modifications.  "How will this one change affect both databases"  Who will have find, insert, update and delete privileges?  How will you manage them?  Do you need to restrict one side of the house (sounds like you do)?  As you can see, it can go south very quickly.  

One of the great things about FileMaker is that you can throw something together very quickly.  The bad thing about FileMaker is that you can throw something together very quickly, and later it gets adopted as your main database and it doesn't scale well.  I know it can be difficult to sell the boss on the idea of it taking longer to get a better result, but if you can pull it off, you will be the hero the next time changes are needed.
rvfowler2Author Commented:
N2A, I think I didn't explain well.  What I'm trying to do here is NOT create a second database, though I may create a second TO.  Actually, the more I think about it, I'm leaning toward just scripting it so the Realty agents can go the the already existing Leads db/layout, just that they can only see their own assignments.  A modification of that would be if I didn't want to give them to see certain data, I may just create a duplicate of the layout and have them navigate to it.  This will keep them from trying or accidentally navigating to other parts of the database through the naviagation buttons up top.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Will LovingConnect With a Mentor PresidentCommented:
I think you're on the right track Randy. Using various filtering strategies, either portals and/or privilege restrictions you can keep them from seeing any records but their own. That way, if a lead becomes a customer you can just change a flag on the record. One thing I can imagine being  potential issue is duplicates as the same lead comes in multiple times for different properties or different sources. Some degree of duplicate checking can be useful. I generally setup a multi-key relationship that matches on Phone, email, street address and name and produces a warning if the same name is entered multiple times.
rvfowler2Author Commented:
Will, how do you do this multiple key thing?  I do a warning by having an invisible portal show itself if the person's cell phone has ever been entered before.  (I don't use name anymore because sometimes our people may spell the name wrong.)
Will LovingConnect With a Mentor PresidentCommented:
If you are checking lots of fields then using a more complex multikey solution can be useful and I will try to address that tomorrow,  but if all you are doing is checking the cell phone and email, then the easiest thing is to have two self-join relationships, one for Phone and one for email. You then either create a separate calculation that sets a flag or displays text when you have a potential match, or you can script the check as part of navigation or some other function. (On a number of projects I have an "Exit Record Validation" script that runs whenever the user leaves a record. It checks to makes sure all entries are valid before allowing them to move on.)

The calc field can be something like this:

Case( Count( Contact_SJ_PhoneCell::Constant ) > 1 ; "Duplicate Cell Phone" ) ;
Case( Count( Contact_SJ_Email::Constant ) > 1 ; "Duplicate email" )

YOu're checking for greater than one because each record will match to itself.

The multikey part comes in if you want to create a single key to check for multiple different types of entries such all phone numbers and all email addresses, assuming you have more than one of each. The key might look something like this:

DupeCheckMultikey =
List( PhoneHome ; PhoneWork ; PhoneMobile ; Email1 ; Email2 )

However, in order to deal with the fact that phone numbers are often entered quite irregularly, I will use the following to strip the phone numbers to just the numbers:

DupeCheckMultikey =
Filter( PhoneHome ; "1234567890" ) ;
Filter( PhoneMobile  ; "1234567890" ) ;
Filter( PhoneWork ; "1234567890" ) ;
Email1 ; Email2 )

Using this as the key on both sides of the relationship you can generate a list of all potential matches. However, the matches will include the records match to itself, so you eliminate that by including a second predicate in the relationship such that ContactID ¿ ContactID. Then you only get matching records other than to itself. See attached file.

 Relationship diagram
the reason for using a multikey calculation over individual predicates such as email to email and phone to phone is that all the predicates would have to be true for there to be a match. With the multikey, you'll get a match if any one of the values listed is matched.
jvaldesConnect With a Mentor Commented:
If you make sure that each user sets a global field to their user name and you link the tables by username, you will avoid all the security concerns you have and you will be able to limit the users access with the same layout. You will only need an admin layout to give broader access. Seems to simple , I must have misunderstood the objective
rvfowler2Author Commented:
Actually jvaldes, you're right; I was way overthinking the issue.  In fact, I even have an existing Employee table with their username, initials, etc., so I can use a join that way and limit their access.  I've decided to just use the current Leads db over on the Property Management side, allowing the Realty side to have access only to the recs assigned to them, and allow them an import button (and flag it) when they want to import a lead into their contacts dbase (only about 5% of leads turn into contacts).  
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.