Solved

FM - Design & Strategy for New DB - Part II

Posted on 2011-03-09
8
528 Views
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.)
0
Comment
Question by:rvfowler2
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Expert Comment

by:North2Alaska
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.
0
 
LVL 12

Accepted Solution

by:
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.
0
 
LVL 2

Author Comment

by:rvfowler2
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.
0
 
LVL 24

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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Author Comment

by:rvfowler2
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.)
0
 
LVL 24

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:

List(
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 =
List(
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.
MultiKey-Duplicate-Check.fp7
0
 
LVL 9

Assisted Solution

by:jvaldes
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
0
 
LVL 2

Author Comment

by:rvfowler2
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).  
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now