FM - Design & Strategy for New DB - Part II

Posted on 2011-03-09
Last Modified: 2012-05-11
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.)
Question by:rvfowler2
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 12

Expert Comment

ID: 35088869
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.
LVL 12

Accepted Solution

North2Alaska earned 125 total points
ID: 35088883
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.

Author Comment

ID: 35089079
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 250 total points
ID: 35090097
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.

Author Comment

ID: 35090309
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.)
LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 250 total points
ID: 35091820
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.

Assisted Solution

jvaldes earned 125 total points
ID: 35095936
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

Author Comment

ID: 35096898
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).  

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Filemaker script send mail ATT00001.bin attached 7 832
Filemaker Pro and FM Go Synch 1 258
audit trail 6 208
conditions for filemaker pro 10 if statement. 5 66
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question