FileMaker Pro Equivalent to SQL Query or View

Have you ever wondered how to create queries or views in FileMaker Pro on Mac?  I have, and it's one of those odd learning curves that come with being used to straight-forward but fairly technical SQL statements that are customary in Oracle, SQL Server, MySQL and even Access.  Searching for the word "join" returns no relevant results in the FileMaker Pro 9 Help files.

Well, for other SQL developers out there, I hope this tutorial will shed some light on how to join two tables and then create a query or a view: FileMaker Pro: Creating a "Query" or "View"

In summary, here are the key takeaways from the tutorial:

• A SQL join is analogous to a layout in FileMaker Pro with relationships between tables.
• A SQL query or view is analogous to using Find Mode in FileMaker Pro with a layout.
• Simple filters can be applied in Find Mode using Symbols and the Omit checkbox.

Let me know what you think of the tutorial!  Personally, I was super excited to figure out how to import two lists from two different databases into FileMaker Pro in order to identify discrepancies between the two lists, like I would've done using Excel or Access.  Why would I use FileMaker Pro instead of Excel on Mac to do this?  Because Excel is unfortunately case-insensitive.

Mixing Person Accounts, Contacts and Business Accounts

I really enjoy the excitement of learning new things, especially through active discussion and target questions and answers. One of the subjects that never lets me down in this area is the concept of person accounts in Salesforce.

Having raved about it in previous posts, I feel obliged to answer a question that was posed about how to actually use person accounts and business accounts in the same org.

The answer could be fairly straightforward: In the standard sales processes, there's no need to mentally make a big distinction between the two. Opportunities are tied to accounts, not to contacts. This means that if a salesperson is trying to close a deal, it doesn't matter if the deal is for a person or for another organization. The opportunity is what's important, and it's always related back to the account entity, person or organization, with which you're doing business.

A trickier question which we're running into at work is: What's the best way to handle people who are both your direct customers and who are contacts at other organizations? Theoretically, the idea of linking the contact to both the business account and to the person account would work. In practice, however, I think if an organization is investing time and energy into this level of constituent mapping, then it would be worthwhile to analyze the business needs and maybe setup some workflow, Apex and Visualforce to take advantage of the data links.

Not to dodge the question entirely, but I think that there are always specific organizational needs that drive people to setup more complex and complete data models. If anyone's interested in discussing a specific use case, I'd be totally game!

The Ingenuity of Person Accounts

As I meditated more on the idea in my previous post about people's connections to different accounts, a surprising realization occurred to me: We don't even have to engineer a new object; Salesforce has already done this for us! And the answer is... Person Accounts!

The basic implementation can be done in two simple steps:

1. Activate the Person Accounts feature.
2. Create Person_Account__c as a Lookup(Account) field on the Contact object

Conceptually, the idea is simple: The person is the core piece of the puzzle. Take John Doe, for example. John can be a contact for Acme Corporation and simultaneously be a contact for Zenith, Inc. All that's needed to represent these relationships are a person account record for John, two business account records for Acme and Zenith, and two contact records to link John to the two accounts.

Although I haven't thought this next idea in much more depth, I have an inkling that this model could potentially even replace the Nonprofit Starter Pack's Household object with a "Household Account" record type. Wouldn't that be a beautiful alignment of developer resources? All of Salesforce's main development efforts on Contacts and Accounts could then directly benefit all users in every industry. No more waiting for updates to the NPSP Householding package; just wait for the next seasonal release of Salesforce.

Increasing Speed of Command Line Extract/Export with Apex Data Loader

I had a strange problem when I tried to automate a routine, daily export that I was performing with the Apex Data Loader (version 20.0 and 21.0): Exporting through the GUI only took 5 seconds, but exporting through the command line took anywhere from 30 minutes to 1 hour.

After 4 weeks, Salesforce Premier Support finally delivered a solution to the case I logged: Set the sfdc.debugMessages value to false. I'm very glad that Premier Support was able to find a simple solution to this problem.

Apparently, debug messages were so verbose for every record being exported that operation time increased by a ridiculous factor. Luckily for me, debug messages are not that important for an export.

Putting a Name with a User ID in DegreeWorks

I came across a question once about what users all exist in DegreeWorks. I couldn't figure out the issue at first, so I created an SR with the AL and got some pointers that ultimately led me to the following query:

select shp_access_id, rad_name
from shp_user_mst, rad_primary_mst
where shp_access_id=rad_id
order by shp_access_id;

This query showed me the names associated with each of the logins to DegreeWorks.

On a side note: The DegreeWorks schema is not documented in any of the PDF publications. Instead, the schema is explained in files that are created during the server installation inside the .../app/schema/ directory:

• dapdb
• raddb
• shpdb