Search Project Mgmt
Search FileMaker Blogs

Thank you for visiting the FileMaker Thoughts blog. I recently moved this content over from my blogger account. Hope you like it! When you get a chance, check out the centralized search feature for all the FileMaker blogs found along the right side panel. It is quite handy!


Friday
Dec312010

A READER ASKS: FileMaker Email And Automation

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

A READER ASKS
I am a factor looking for a functional CRM. Can email correspondence be attached to jobs/opportunities?  Is it possible to automate certain outbound email templates  to customers (not mass mail, but individual emails)?  There is more that I require, however, please respond...

DWAYNE RESPONDS
Great to hear that you are considering FileMaker for your CRM needs. FileMaker is hard to beat when it comes to costs, flexibility and reliability. You packed a lot of interesting questions in just a few words, bravo! I thought I’d take a little extra time in writing back to you and reuse this information for a blog post.

EMAIL INTEGRATION WITH FILEMAKER
In regards to email correspondence, the answer is a definite “yes”. The challenge is  deciding what form of email integration would work out best for you. Our product InBizness 2.8 has email integration with campaigns, invoices, timecards and a number of other modules. The scripts that perform these actions can be copied and recycled for use in other modules.

There are at least three different ways to integrate email correspondence with FileMaker. InBizness uses the Send Mail script step. This method requires the user to have a separate e-mail package installed on the computer. FileMaker will pass the e-mail information directly from the database to that application. The strength of this is that you can use information directly from a field in the database to fill out the To, CC, Subject or Message areas of the e-mail. You can also specify to attach an enclosure.

Other options include the ability to use the Send Mail script step with SMTP, using a third party FileMaker plug-in or having FileMaker Server send out emails via a scheduled event. All of these other options have strengths and drawbacks that an intermediate or advanced FileMaker developer should consider. The InBizness implementation of the Send Mail script is not that complicated and most casual FileMaker developers can embrace it without too much concern. I would recommend using the standard implementation of the Send Mail script step. If you find limitations that affect that method, investigate the other options of SMTP, plug-in or server side schedules.

To make matters even more interesting, there are other FileMaker based products that you can blend into an existing FileMaker system such as the .

ABOUT AUTOMATION FOR TASKS
None of the InBizness products have automation features but that is more of a design choice and not a FileMaker limitation. Like email, there are a number of automation options you can put into a FileMaker solution. Most of these would be beyond the casual FileMaker developer, unless that developer welcomes the opportunity to grow their design skill set. It isn’t that the setup of the automation is hard, because it really isn’t. The challenge is making sure that the automation works reliably under a variety of user conditions and that it doesn’t interfere with other database tasks. The golden rule for automation activities is to test, test and then test again. If there is a flaw in your automation design, that flaw can automatically affect the integrity of your database system again and again ... automatically.

The best automation is the FileMaker Server variety. This method takes the majority of user related issues out of the equation and is quite reliable (once it has been setup and tested properly). There are scripts steps that are incompatible with Server executed schedules and there are some steps that work but have constraints to consider. This is why you have to test every single server side script you run! Yes, I realize I'm close to sounding like a broken record in regards to my views on testing. As Popeye once said, "I ams what I ams Olives". (grin)

The next automation feature is using the OnTimer script trigger. Introduced with FileMaker 10, the Install OnTimer script step is used to run a script at a particular moment in time (based upon a number of seconds after it has been initialized). When the Install On Timer script step is executed, it attaches itself to the current window. If the window attached to the Install OnTimer script step is closed, the Install OnTimer event is flushed from the active FileMaker system. A window can only have one OnTimer event attached to it. If a second OnTimer event is applied to a window, the first OnTimer event instructions are flushed from the system. Some developers are using this technique as a way to turn off a timer without closing the window it was attached.

This is a broad view of email and automation FileMaker options. I would recommend documenting exactly what your needs are before you begin the design phase (or the developer you may hire for this). I would also recommend building proof of concept test files first before trying to add this code to a finish FileMaker based CRM system. Once the proof of concept experiments prove successful, then integrate that code into your active system. 

Sunday
Dec192010

Filter And FilterValues Explored

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Filter( textToFilter ; filterText), was introduced with FileMaker 7 but it didn't get that much attention becuase of the grander features in that release. Basically it asks you what text string or field you want to filter and then what text characters you want to see after the filtering process is complete. An example would be the filtering of a phone number field. You can set it up so only the number characters of zero through nine make it through the filter. Any other characters besides 0123456789, would make it to the final returned value.

SIMPLE EXAMPLE
Filter(”abc123xyz”, “abcdefghijklmnopqrstuvwxyz”) would return abcxyz
Filter(”abc123xyz”, “123456789”) would return 123

Some FileMaker functions will provide you with a return delimited result (DatabaseNames, WindowNames, etc...)

FilterValues( textToFilter ; filterValues) allows you to look at a string of return delimited text (textfilter) and say what values are ok to be passed on to the result (filtervalues).

This becomes very valuable when interacting with some FileMaker functions that provide you with a return delimited results such as DatabaseNames and WindowNames.

FYI... Return delimited text is a sting of text that is separated by carriage returns. That makes it easier to slice and dice that text later on for more complicated database procedures. Here is an examples of return delimited text,

Value 1
Value 2
Value 3
Value 4
Value 5

FYI... When looking at a calculations, the carriage returns are displayed with the character of ¶. So if you see a text string within a calculation that looks something like this ....

"Sunday¶Monday¶Tuesday¶Wednesday¶Thursday¶Friday¶Saturday"

What you will see as it’s calculated text result will be ...

"Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday"

EXAMPLE: Let’s say we have an online application form for a position within our company. We allow people to check mark all the applications they are familiar with. If you use the application for many positions, some of the checked data may not be useful for a particular job. So when you do your printouts to take to a committee that does the hiring, you want to filter that data. It could look something like this...

FilterValues( Applications ; “FileMaker¶Microsoft Word¶Microsoft Excel) and the result will only contain those values, if the user clicked upon them.

FILTERVALUES FUNCTION EXAMPLE
You may want to nest one of these functions within the FilterValues function to see what values you have left over. From a string detection standpoint, you can almost do the same thing with the PatternCount function but filtervalue can allow you to have more elegant calculations.

In this example, we have a text field called values_to_filter and it has return delimited data. We also have a field called filtering, which we specify what we are looking for. The filtered_results calculation field uses the other two fields as its parameter and only returns the values we specified.

An example file can be downloaded by clicking (here)

More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.



Thursday
Dec162010

No Menu Options For Copy, Cut And Paste

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

In this example, we automatically open the database with a restricted account name of other. This account name has an associated password of Other. The master account with full access is Admin without a password. You can toggle back and forth between the admin account and the restricted accounts by using the login buttons at the bottom of the example. With the Other account, you can see how the user experience is with the security restrictions. With the Admin account, you can go under the hood and tweak the security adjustments.

Looking at the privilege set that is associated with the Other account, you can see that it is defined to have Minimum Menu access. Here are some links where I discuss this in more detail ...

Taking Away Some Menu Options Via Privilege Set

This setting will make the popular options of Cut, Copy and Paste unavailable from under the Edit menu and also unavailable via the traditional keyboard shortcuts.

However, you can put buttons on the layout that will perform these options. We have done just that in this example. Highlight the text you want copy or paste and click it’s associated button. Click where you would like to paste text and click the Paste button.

If you are using FileMaker Advanced, you can have the same restrictions but add custom menus that run cut, copy or paste script steps. You can even add the shortcut commands back in but (as the developer) you have more control over the entire menu operation.

An example file can be downloaded by clicking (here)
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

Sunday
Dec052010

Taking Away FileMaker Menu Options Via A Privilege Set

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

The privilege set is used in concert with account settings to set access levels to a FileMaker database. You have to understand privilege sets to work with FileMaker security environment. Each account has only privilege set. A privilege set can be assigned to multiple accounts. Privilege Sets consist of a large number of security settings (kind of like a large lunch buffet) and the combination of selected options define what a user can see or do.


You can also define, via the privilege set, what menu options are available for a user. This is broken down into 3 categories that appear in a pull down menu. These options are All, Editing Only and Minimum.

All Menu Option
Basically, this means that you are not selecting to have any additional restrictions on what appears in the menu bar. It will look like the normal FileMaker menu, of course with the possible exception of the other settings. That is to say if you turned off the ability of a password to edit scripts, the ScriptMaker option will be grayed out.

Editing Only Menu Option
When chosen, the following are grayed out Manage Database, Manage Value Lists, Changing Password, Sharing, Print Setup, Print, Import, Save A Copy As, Duplicate, The Entire Contents Of The Mode Menu, The Entire Contents Of The Format Menu except the Text Ruler and the ScriptMaker option.

None Menu Option
This will gray out almost all of the menu commands. When using this option, you will want to provide a number buttons for users to take care of the missing menu options. This is because popular commands such as Cut, Copy and Paste are not available from the menu anymore. In fact, the shortcut of using the keyboard equivalents will be disabled as well. I’m talking about the shortcuts such as ...

Mac Users
- Copy ( command key and C key )
- Cut ( command key and X key )
- Paste ( command key and V key )

Windows Users
- Copy ( control key and C key )
- Cut ( control key and X key )
- Paste ( control key and V key )

Also, you can use FileMaker Advanced Custom Menu feature to limit, change or add to the users menu experience.
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.



Saturday
Nov202010

Shoe Inventory In A FileMaker Crosstab Report

From Dwayne Wright PMP, PMI-ACP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

One of my favorite things that I can offer clients is the purchase of one hour of design time. This allows someone to send me a request that they have been struggling with for a few days and I can try to quickly pound out a solution for them. This example file was born out of one of those requests.

FROM MY NEW CLIENT
I am currently working on a point of sale system that is almost complete. I continue to stumble over the development of the inventory matrix. I’m trying to generate what I thought would be a simple inventory report. The system is a shoe sale system whereby everything is done by shoe STYLE #.  In the inventory matrix I’m trying to generate I would be searching for obviously, inventory and then generating a report based on style #, width and size.

ABOUT CROSSTAB REPORTS
This is a classic example of a crosstab report need. A crosstab report is a report that summarizes values in both rows and columns. There is no built in assistant within FileMaker to build crosstab reports but there are multiple ways in which to design them using your skills as a FileMaker developer. Each method will have its advantages and disadvantages in key areas such as ...

- the amount of time to create the report
- how complex the report is to create
- how flexible the report is for recycling for other needs
- how large of a schema footprint it leaves (extra fields, layouts, scripts)
- how well it can handle extremely large sets of data


SO WHAT I DID
I entered in the shoe data the client provided. It ended up being three different shoe styles, seven different width options, which ended up given us nine unique combination's of a shoe style / width. In the screen shot he sent me, there were 21 different sizes of shoes for the report. Adding this all together, we ended up with 96 records specifying each unique combination of shoe style, width and size (again based upon the screen shot he sent).

I then created the report layout to have one subsummary layout part that is sorted by a concatenated value of the shoe style and width. This gives us the ability to create one unique row for each style width combination and then show the on hand totals for each size horizontally across the row. At the end of the row, we have a total field to tell us just how many sizes we have for a particular shoe / width combination.

Finally, there is a very simple script that does a find for any shoe style width combination that has at least size in stock, sorts the list and brings up the report in print preview mode in a new window.

Here is a look at the final result for the report.

An example file can be downloaded by clicking (here)
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.