Click this video (use the full screen or click to go to YouTube) to see what you may be missing in your copy of FileMaker Pro! I don't develop without it!
News & Blogs
California based .com Solutions Inc. has released FmPro Migrator Platinum Edition 6.97 with support for FileMaker Auto-Enter calculation fields and FM query operators when using the PHP conversion feature. This release also adds support for the CakePHP 2 framework providing improved functionality and performance... - (.com Solutions Inc.)
Troi Automatisering today announced the availability of Troi Activator Plug-in 3.5 for FileMaker Pro 12. Troi Activator Plug-in is a very powerful tool for triggering scripts across the network, even over the Internet... - (Troi Automatisering)
One of the nicest feelings in the world is getting something for free. Especially when it really helps you out and it's valuable. At the small cost of giving up a little bit of data, to one of the world's biggest data collectors, you can see how users use your FileMaker solution and what they do... - (ISO FileMaker Magazine)
Database systems often call for value lists that only show the "Active" or "Current" records in a table. In this post we will explore how we can forever wave goodbye to context-dependent value lists for "Active" records in FileMaker 12 using ExecuteSQL and a few other tricky techniques. In addition, we will cover how and when to use context-independent and context-dependent value lists in FileMaker 11 and prior versions in case you have not yet upgraded to 12.
First let's define the terms we are working with:
Context-Dependent Value List ~ a value list that can only be applied to a layout based on a particular table occurrence.
Context-Independent (or Global) Value List ~ a value list that can be applied to any layout.
It's important to note that all static value lists where you just type in a list of values into the value list definition are context-independent. The real beauty of this idea emerges when making a dynamic value list context-independent. My colleague Martha Zink has posted some helpful videos on a similar topic on our SoliantTV YouTube Channel here. In her post she makes use of global fields. My post will explore other methods. And I'm sure there are other ways of producing the same result. Feel free to share your favorite method with me in the comments section below!
Here's the scenario - picture it: you're designing a system for a footwear manufacturer. You've built four tables: Products, Orders, Customers, and Projects. The business rules require that you need to give customer service representatives the ability to assign Products to Orders so you create a value list of all Products in your system. However, you realize during the course of gathering requirements that users should not be able to place Orders for footwear that is no longer in production. That means you need to refine your list to only show currently available, or "Active", Products.
There are a number of ways to do this in FileMaker 11 that we will talk about later in the post but the burning question on my mind is: how can we do this using ExecuteSQL? John Ahn presented a neat way of doing it at a FileMaker DevCon 2012 Unconference session, and his demo file can be found on the very cool FileMaker Hacks site here - he makes FileMaker perform awesome acrobatic feats. For our purposes, we won't need to build out to the level of complexity featured in his file so I've prepared a simpler exercise inspired by his work. The basic concept is that you create a Global or Utility table with calculations based on global variables into which you throw your list of values by using an ExecuteSQL function on a script trigger that is activated upon entering the field attached to the value list. The global variables parse out the list into records in the Global table and the value list uses those records. Clear as mud? I've created a file that you can download and explore: Shoe_Tracks.fmp12.zip And we can step through the basics together. First, create a new table called Global. In this table create the following fields:
(Really, we could name these fields without the "Products" descriptor in it because they are dynamic and can be used for other means, but I think it provides more clarity when trying to understand the concept initially)
Now navigate to the Global layout that FM created automatically. View it in Table View. You will need to create as many records in this table as you think you might one day have as "Active" Products (or Customers - as you can use this table for any number of contexts). For some systems this number may be only 10. For other systems it might reach 100,000. I haven't tested this out on a large-scale system but since the calcs are all based on variables set by ExecuteSQL it seems like it should fare well. For this example let's create 100 records.
The last thing we need to set up for the Global table is a Cartesian, self-join relationship from the Global table back to itself. You can call the second Global table occurrence Global_Global_All. Next, go to the Orders layout. Pull in the __kp_Product field, set it to be controlled by a Drop-Down list and then create the value list like so (you will receive warning messages that the value list won't work because neither of the fields can be indexed, we can ignore the messages because a script trigger will set the values every time a user enters the field - you can read more about that here):
The next step is to attach a script trigger to the Order::_kf_Product field. The trigger should be activated OnObjectEnter and it should run a simple script you will need to create called ExitScript. The only step in this script is the ExitScript step. Below is what the parameter on the script trigger should look like, this will throw our list of Active Products into the global variables that feed the Global table calculations of our value list:Let ( [ $$z_valuelist_num = ExecuteSQL( "SELECT " & Quote("__kp_Product") & "FROM Products WHERE Status = 'Active' "; ""; ""; "" ); $$z_valuelist_text = ExecuteSQL( "SELECT Name FROM Products WHERE Status = 'Active' "; ""; ""; "" ) ] ; "" )
Notice in the above code that we had to put quotes around the __kp_Product ID field in the SQL statement because SQL does not play nice with field or table names that begin with the underscore character ("_"). There is one more layout trick we are going to put in place. I am in awe of John's creativity with this feature. The basic concept is to use an ExecuteSQL function in conditional formatting to update a variable that will display the text Name of the Product that you have chosen. Check the file I've provided for download to see how this works (Shoe_Tracks.fmp12.zip).
Et voila! That is the last step to setting up your completely context-independent value list to show only "Active" records in FM 12 using ExecuteSQL! I will be using this a great deal in the future.
In previous versions of Filemaker, without the power of the ExecuteSQL function, the methodology is a bit different. Let's first take a look at how, when, and why to set up a context-independent value list. The first step to refining your list of Products to only include "Active" records is to create a calculation field in your Products table named: Name_Full_Active_ct and set it to be equal to: If(Status = "Active"; Name_Full_ct; ""). This field will only show the Name of the Product if the Product is marked as "Active". Then set up your value list like so:
We will be using the primary ID of the Product table to pop into the Order::_kf_Product field when a user selects a Product from the list but the user will only see the Product Name and not the ID. To give the users that polished interface we must choose to "show only values from the second field", which disallows us from sorting on the first field, which is our unique ID field. Important: We can only "sort on the second field" using this method if we know for certain that the data in the second field will always be unique across records. In the system we are discussing, we know that we will never name two shoes with the exact same name. That is a business rule that we can rely on. Therefore, we can use this context-independent method to create a dynamic value list that we can apply on any layout in our system.
Oftentimes, we can't rely on data in a field to be unique across records. With the Customers table, for instance, we know that many people have the same name. Thus we must set up the Active Customers value list to be context-dependent. This difference hinges on the way that the "Sort values using:" radio box works in conjunction with the "Include only related values starting from" selection. If we choose to "Include only related values" from a table then we can display the second field even if it does not contain unique data across records. Why is it important to have unique data across records when sorting on a field in a value list? If you don't then you won't see each individual record in the table. For example, if we have 2 Customers in the database named Samantha Jones then our value list would not show a value for each of the different Samanthas it would only show the value for the first Samantha entered into the system. Obviously, this is a problem if we want to place an order for the Samantha Jones that lives in New York, not the one in Chicago.
The context-dependent method requires a few more steps than the previous method. First, you will have to create a calculation field in the Orders table that is set to always result with the text string "Active" (Let's assume you name the field zConstantActive_ct.) Then you would build a relationship from Orders to Customers based on zConstantActive_ct and the Status field on the Customers table. Next you would set up a dynamic value list based on those components, like so:
Notice that this value list only includes related values starting from the Orders table occurrence pointing to the Orders_Customers table occurrence. Also note that we have selected the box to Show Only Values from the second field. Because this value list is dependent on context it is limited in that we can only apply it to layouts based on the Orders table occurrence. We cannot use it anywhere else in the system. If we need to use a list of Active Customers somewhere else in the system, such as on the Projects table, we would have to replicate the steps we just took for Orders context to make it work. Specifically, you would need to add a constant field to the Projects table, build another relationship from Projects to Products and add an additional value list based on those components and so on and so forth for any other context as your system grows. Unfortunately, this practice can clutter up your relationships graph, introduce more risk, and make your system harder and more expensive to maintain. To get around that you might also consider tacking the city name into the Active Name calculation field and in so doing transform this into a brand-new, sparkly, context-independent value list!
I hope this post has helped you more clearly define the ins and outs of dynamic value lists. Thanks to all those in the community whose thoughts and work helped me to come to my own understanding. And, if I've butchered anything, please do let me know!
More Reading: I kept the ExecuteSQL statements pretty simple in this example but you may want to read up on making them less breakable. Kevin Frank wrote a good piece on that here.
Database FileMaker FileMaker Value List Context Independent Active records Global Value Lists FileMaker Execute SQL Script Triggers
All-new Made for FileMaker is portal to hundreds of solutions, plug-ins, tools and more for FileMaker
FileMaker has announced the all-new Made for FileMaker, an online collection of nearly 300 FileMaker database solutions and resources in multiple languages from some of the worlds best FileMaker developers. All products help users and developers get the most out of FileMaker, and are offered by approved members of the FileMaker Business Alliance.. - (FileMaker Inc.)
In most organizations, not everyone needs access to full-blown CRM functionality. There might be users who never touch Leads or Opportunities, need read-only access to just Accounts and Contacts, or maybe a custom app. One Enterprise Sales Cloud user license costs $1500/per year at current pricing. I suggest spending a bit of time analyzing your users, and what they are using Salesforce for. There are definitely ways to get creative with licensing and put more of that money back in your pocket.
The Spring '13 release brings two new Force.com licensing options that are worth checking out. You can combine traditional Salesforce licensing (full-blown access) with Force.com licensing that allows you to pay for exactly what your users need. Existing customers that already have Force.com licensing in place can stick with their current licensing and pricing, or switch to one of the new offerings.
Force.com Light, the first new choice, is priced at $10/ per app/ per user. In addition to the custom app(s) the user can access, Light includes:
Force.com Enterprise, the second choice, costs a little more at $25/per app/ per user, and gives the user access to quite a bit more functionality in addition to what is listed above for Light:
Both license types come with the same capacity:
Since you can combine license types, you might find that you have groups of users that could easily be moved to a Force.com license, while maintaining full-blown CRM licensing for only those users that really need it. I believe a bit of research and planning in regard to what level of functionality is actually needed by each user can go a long way in maximizing Salesforce use as well as minimizing costs.
Cloud Salesforce Salesforce force.com Licensing Spring 13 Force.com Light Force.com Enterprise Options. Salesforce License Salesforce Licensing Options
From Excelisys by Doug West, Product/Project Manager, a Free FileMaker Pro 12 Tip and Trick demo to retrieve City, State, Country with just a Zip code using a web service called Ziptastic... - (Excelisys)
From Excelisys by Doug West, Product/Project Manager, a Free FileMaker Pro 12 Tip and Trick demo to retrieve City, State, Country with just a Zip code using a web service called Ziptastic.
Basic demonstration of accessing a web service called “Ziptastic” from within FileMaker Pro 12 to retrieve City, State, Country with a Zip using the insert from URL script step introduced in FileMaker Pro 12 (no plug-ins!). The web service provides the corresponding city, state, and country for a given ZIP Code. The scripts in this file parse the JSON-formatted response from the web service into the appropriate fields.
So, basic concepts:
We run our FileMaker Developer Ad Sale each each year and each year it an immediate sellout. It's our incredible $1 Buck a Day Sale with only 10 slots open. When the 10 are gone, so is this offer... - (FileMaker Today)
FileMaker provides us with the wonderfully simple Duplicate menu option right there within its Records menu. The problem, however, with a relational database, is the fact that many times you're in need of duplicates of both a master record and any of its child records... - (ISO FileMaker Magazine)
FileMaker Developer Conference explores design, development and business innovation with the FileMaker Platform
FileMaker, Inc. today announced schedule highlights for the FileMaker Developer Conference 2013, the largest annual gathering of worldwide FileMaker independent and corporate database developers, trainers and users. "Platform for Innovation" is the theme of the 18th annual conference, which features two new tracks and will be at the Hilton San Diego Bayfront hotel in San Diego, Calif., August 12-15... - (FileMaker Inc.)
Comm-Unity Networking Systems has released version 1.4 of CNS Barcode, the FileMaker Pro plug-in that provides scanning and generating barcodes. This version fixes a few bugs and modifies how the plug-in handles Number data from FileMaker when generating barcodes... - (Comm-Unity Networking Systems)
Productive Computing, Inc. today announced the release of Music Director Pro, a web-based software management tool for band, orchestra or choral directors. With this software music program directors will have the ability to track and catalog music libraries, instrument and uniform inventories, people, ensembles, lockers and schedules... - (Productive Computing Inc.)
This free eBook on FileMaker Pro Themes by Michael Rocharde is a simple visual reference guide on how each of the different FileMaker Pro themes look in a production environment. Each example uses the same base data and has tab panels, a portal and buttons. There are also some additional hints and tips about some of the "oddities" found in FileMaker Pro Themes... - (Excelisys)
This free eBook on FileMaker Pro Themes by Michael Rocharde is a simple visual reference guide on how each of the different FileMaker Pro themes look in a production environment. Each example uses the same base data and has tab panels, a portal and buttons. There are also some additional hints and tips about some of the ‘oddities’ found in FileMaker Pro Themes.
Team development in the Salesforce platform can be very frustrating if you don’t have the right process defined. For developers used to “traditional” software development it can be painful (it was for us), until you figure out the right way to do it.
Here at Soliant Consulting we have been working on defining the right process for quite some time, and it seems like finally we have most of the nuances figured out. We may not have all the answers, but we have a process that is working for us, and that is allowing us to keep our code under version control, and to automatically deploy to our Test/UAT environments through our Continuous Integration server.
Let’s start from the beginning. What are the main elements that you need to do team development in the Salesforce platform? They are really the same ones that you need in any other software platform. You need:
As you can see, there is nothing new here. The way you use version control is also very similar to the way you would with any other development environment. This would be how you work with it (using SVN terminology).
Working with Version Control
See my Force.com IDE & SVN post for specifics on how to do these things with the Force.com IDE.
What is important to keep in mind here is that (contrary to what you might have read in some of Salesforce’s documentation) you should treat the repo as the stable version of the code.
Repository = Gospel
Developer Org = Scratch Paper
In some of Salesforce’s documentation they talk about the org as containing the stable version of the code, but that is because they are referring to production or to a sandbox where all changes from production are being duplicated. We will see later how to address these issues in the context or our development process.
Let’s talk now about Continuous Integration. Some developers might not be as familiar with it as they are with Version Control and Unit Testing.
A CI server can do many things, but let’s list the main tasks will set it up to perform in the environment of our Salesforce Team Development process. On a scheduled basis (or every time a developer commits to the repo, depending how we set it up) the CI server will:
Steps 2 and 3 are sometimes in reverse order in other development environments. First the unit tests run, then the code is deployed, and finally functional testing is performed. With Salesforce however, you can only run the tests from inside an org, so we have to deploy first and then run our unit tests, followed by functional tests, if we have any.
Using Continuous Integration has many advantages, among others:
If this was a live presentation this would be the time where demoed our process. :) Since it’s not, let me try to describe it.
Let’s assume that we already have our code under version control, and that we have two developers working on the project. Each of them (let’s call them John and Peter) has a separate development environment, and both are using the Force.com IDE. This could be a common set of events:
Now everyone is in sync! You can go to the Test/UAT org and verify that Jenkins automatically deployed the changes there. They are now ready for QA.
Salesforce Development Scenarios
So far we’ve been talking about team development in general terms, and looking at the similarities it has with team development in other platforms. However, the Salesforce platform has some very distinct features, and we will now try to dive a little deeper into those.
There are actually two very distinct types of development in the Salesforce platform:
B. Unpackaged applicationsA. Developing a Salesforce Package
This is the easier of the two scenarios to deal with. When you are developing a package you are doing so in isolation from any other code. This means you don’t have to worry about not being able to install third-party managed packages in each of the developer’s orgs (like you do for unpackaged applications).
To do team development in this scenario you need the following:
This scenario can be a bit trickier, as we’ll shortly see. To do team development in this scenario you need the following:
The main challenge here is that the Enterprise Edition (the most popular one) comes with only one Developer Sandbox. Additional ones need to be purchased from Salesforce. Only Unlimited Edition comes with multiple Developer Sandboxes.
In this scenario developers need to work with Developer Sandboxes because of the possibility of having third-party managed packages installed in production. If there were not managed packages in production (not with the characteristics described below) developers could actually use Developer Edition orgs. This will be described in more detail in the "Salesforce Unpackaged App Development Gotchas" section. Bear with me. We are almost there.
Salesforce Package Team Development Gotchas
There are a few important things to take into account when you are developing a Salesforce package with the methodology described in this article:
1. You should NOT create the Package in any of the developer orgs. Creating a package will add the selected prefix to all classes, files, objects, etc., and may complicate things (more info regarding package creation)
2. To avoid prefix issues so that your code can be deployed to multiple Developer Orgs, you should follow these recommendations:
a. Do not use any prefix in Apex code (Apex code will work fine without the prefix even after the package has been created)
e. Try to avoid dynamic SOQL. Since these queries are generated from text strings the namespaces won’t be automatically added. You could for example return Iterable<sObject> instead of Database.QueryLocator from the start method of classes that implement Database.Batchable. => This was an issue at some point in the global namespace, but it has been fixed
To automate some of these issues you may want to check out this Github project that adds namespace prefixes to the metadata source-codeSalesforce Unpackaged Application Team Development Gotchas
Why the Need for Developer Sandboxes
Let’s talk in more detail about a topic we’ve been avoiding so far. Why do we need to use Developer Sandboxes instead of Developer Edition Orgs for development? After all the latter ones are free. Well, the issue is that you need to be able to deploy code from one org to another. No only that, you need to be able to pull any new changes from production and deploy them to all of your development orgs. These changes might have dependencies to third-party managed packages.
Don’t get me wrong; we have nothing against managed packages. They provide great functionality at very reasonable prices (sometimes even free). The problem is that it is sometimes impossible to install a managed package in a Development Org. If you have managed packages installed in production and you cannot install those same packages in your development orgs you will never be able to use that org for development.
And why wouldn’t you be able to deploy code that you pulled from production into a Developer Org if there are managed packages installed in production? Because most likely (almost certainly) there will be references somewhere in the metadata to those managed packages. For example, a field might have been added in a layout that is actually a field of an object of the managed package. That field will have a prefix that does not exist in the Developer Org where you are trying to deploy your code.
Ok, but why wouldn’t I be able to install those packages in my Developer Orgs? Don’t package creators allow you to install the packages for free in a Developer Org? Unfortunately sometimes they do, and sometimes they don’t. There is for example a very popular managed packaged that cannot be installed in developer orgs. Another package of the same vendor can be installed with no problem. Another case that we have seen is that a client has a managed package installed in production that has now been deprecated. Which means that it’s just not possible to install it in any other org. However, if you have a Developer Sandbox all these packages will be automatically pulled from production, with the rest of the code, and installed.
A hack that I’m hesitant to bring up, but that might cross some developers minds, would be to try commenting out the sections of the code where there are references to the managed packages, but that’s most likely a recipe for disaster. It would mean that developers don’t really see the full picture. They would be testing their work in incomplete environments. It would also be very easy to forget commenting back in all those changes and end up removing functionality from production that you didn’t intent to remove.
So there you have it. If you are developing traditional Salesforce applications better provision a Developer Sandbox for each developer involved in the project. Otherwise robust team development will most likely not be possible.
Replicating Production Changes
Another very important issue to keep in mind is that all changes made in production must be replicated to all environments. If a change is part of the metadata, you should instruct the Salesforce admin (or whoever makes changes in production) to replicate the change in one specific Developer Sandbox. Once that change it’s in that Sandbox the Sandbox owner can pull it (use the synchronize perspective from the Force.com IDE to review it) and add it to the repo. At that point it will propagate to all of the developers’ environments. (If the change is not contained in the metadata I’m afraid it will need to be manually replicated to all development environments.)
Refreshing the Sandbox
Last thing to keep in mind in this section is that you should only refresh a Sandbox from production after all changes have been deployed. Never do that in the middle of an iteration or you might find yourself in a world of pain. Believe me, I know. :)
That’s all for now regarding team development in the Salesforce platform. Don't forget to take a look at the post regarding how to use the Force.com IDE with SVN. It can clarify questions you might have regarding the specifics of how to do some of the things described in this post. Also, I plan to write another post in the next few weeks regarding the Ant Migration Tool and how to use it with Jenkins. You may want to watch out for it. Until next time.
Software Development Cloud Salesforce salesforce.com force.com Cloud continuous-integration version-control
The Force.com IDE is one of two IDEs that we use here at Soliant Consulting. The other one is MavensMate. I personally prefer the former at the moment, mainly because of the Synchronize perspective, which, as mentioned below, can be very useful in different situations. Some of my colleagues prefer the latter, so you may want to try both by yourself and then decide. (Also, keep in mind they may both undergo changes with the release of the new Tooling API in Spring 13.)
In this post I will explain how install and configure the IDE to work on projects with and without version control. Of course, we would recommend the use of version control, especially if you are working with a team of developers. Version control will allow you to store all changes (that have been committed to the repo) and be able to roll back to previous versions. If you are in a team environment, you can also use version control to transfer changes between different orgs through version control, as described in the post Team Development in the Force.com platform.
*Note: the Force.com IDE was created on top of Eclipse. We might refer to it by either name.
*Important Note: if you are going to have both MavensMate and the Force.com IDE installed do NOT share their workspaces. Have a separate workspace for each, even if you think you might try alternatively working with both in one project.
Force.com IDE Installation
To start setting up your SFDC development environment with the Force.com IDE, follow these steps:
Setting up your project
Once you have the IDE all set up you will need to set up a project to work on. We give you the steps for setting it up with or without version control. These are your options:
Here you are just setting up a project in your local workspace and connecting it to your org:
If you are not planning on using version control, that’s as far as you need to go in this section (don’t forget to check out the “Salesforce-specific Force.com IDE views,” though). If you are planning on using version control, the first thing you need to do is to create a version repository (we will be using Subversion here). You can create it from the code repositories view in Eclipse:
Once the repository has been created you need to do the first commit. This is quite simple once the project has already been created in the previous step. Just do as follows:
Remember, this is the case where we assume that the code has already been committed to the repository (the first commit has already been done). In that case, to create your project in the IDE you should do this:
1. First you will need to add the repository to check out
2. Select the project, right-click and choose Check Out:
Using Version Control (specifically SVN) Committing changes to SVN
Once you have done some work on the project you will want to commit those changes to the repository. To do so, I'd recommend that you go to the SVN Synchronize perspective (Right-click project > Team > Synchronize with Repository) and look at the outgoing changes. Review any outgoing changes, select those that should be committed, and click Commit.Updating changes from SVN
If there are other developers working on the same project you will want to retrieve the changes that they have committed to the repo. For this I'd also recommend using the SVN Synchronize perspective, so that you can see what changes are coming in from the repository.
Once you update with new changes from the repository the IDE will automatically deploy them to your org.
Salesforce-specific Force.com IDE views
These are all the Salesforce-specific views (sections) in the Force.com IDE (they are all very useful for development):
Software Development Cloud Salesforce salesforce.com force.com Cloud saas IDE SVN
Let us tell you when a new video is posted. We'll send you an email with a direct link right to your email inbox.Make sure and whitelist (or add to your address book email@example.com
10 Most Recent Videos