Fixing FileMaker Macintosh Outlook Carriage Returns With A Custom Function

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 or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).


Back in February 2012, I wrote a post titled "FileMaker, Microsoft Outlook 2011 And Missing Carriage Returns". Since then, I've been updating different scripts that integrate email messages that consist of concatenating multiple FileMaker data fields.

Last week I let out an audible groan when I came across another script that was affected by the Macintosh Outlook users. Only then did is occur to me to create a custom function to make it easier for me and other developers.

Brain Dump Of A FileMaker Portal Troubleshooting Session

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 or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).

Previously I chatted about a portal sorting via hidden tabs that I encountered in a clients database. In the same database, there was another portal that would show product sales or purchase totals for a number of years. Now the portal only went to 2008 and they wanted a new portal row for the 2009 year. This was a very different implementation because there was a value list next to the portal. Using it, you could dynamically show every sale or purchase history. So we must be using some sort of join table and I'm thinking it must be pretty large in size.

Currently, the customer has 1,324 records in the products table and the portal shows years 2003 to 2008. So we have six years, then multiply that by 1324 records and then multiple that by 2 (for purchases or sales). So that gives me a grand total of 15,888 records.

Now I was wondering how they handled the join table for new records, so it was going to be interesting to look under the hood. It seems quite nimble and I'm wondering how FileMaker is doing these aggregate totals so quickly. After taking a peek, I became bewildered because the join table in use, the total field is a straight number field. So it is getting stamped somewhere. I wonder if this might be part of some nightly routine using the FileMaker Server scheduling. The entire office is updating to FileMaker 10, so this might be a great place to start introducing script triggers.

Sure enough, I'm finding a number of maintenance scripts but none of them are pointing me in the direction I was hoping. Wow, as I continue to look under the hood, there are hundreds of scripts here. They are nicely organized into folders but the sheer number of them is out there. I'm not sure I'm going to be able to find them via a seeking and discover manual process. After spending a hour, going to try and see if I can get a DDR or BaseElements to help me out here.

If you haven't checked out BaseElements, do yourself and give it a go!

Well, I know now that I would probably never have found this without Goya BaseElements. It looks like the value are set via a stamping process from a report run from a Vendors perspective. The script goes from one vendor to the next manually setting the total field for the join table equal to the aggregate total of the sales order and purchase order line items. So it looks like the very cool feature I'm interested in is just a by product of running the master report. How absolutely weird.

So I had to take a week or so away from this project and knock out some other project work. Getting back to work on a complex and undocumented database you don't have much experience with is hard. I was able to import the data into the join table and all looked well and good. So I'm three steps into the myth of the separation model allowing you to leave the data file intact. So far I have had to add 4 fields and import 2,400 records to build a join table for 2009. I take a look at the reporting script that BaseElements leads me to believe is the one stamping the data. Towards the top of the script, I see that it is setting a variable to a carriage return list of years and 2009 is absent. It looks like this list is parsed later on, so I add a 2009 value to the list and move one. I ran the report that I thought would stamp my annual value data for 2009 but I was mistaken. My imported 2009 value in the portal is empty for something I know was sold in 2009! Darn!

I then try to add a new sales order and see if I can reproduce the stamping event. Twenty minutes later, I cannot figure out how to add a line item to a new order. It has an elaborate selector field where you have to pick a supplier from one field, then a product from another field and apparently some action is required to add the item. However, I cannot find any button anywhere that will execute the action. So I have to downshift to looking at the scripts in ScriptMaker and I found a promising candidate. I decided to execute it with the script debugger running. This script "Add Item To Order" did absolutely nothing at all. Then ran a script underneath this called "Add Item To Order - New" and this did add a line item but without any product code or description information. Very frustrating and not sure how much longer I can continue to throw darts at the board with a blindfold on.

So I'm going to try and see if I can get this tagging done onsite or have the clients authorization to research it further. It may very well take me a few hours or more to hunt down this process. In the meantime, I'll go ahead and do a work around for the stamping process. I'll try to make it so that it fails gracefully and only runs on 2009 transactions. Of course, this is going to require even more fields for me to track.

About 30 minutes later and I'm still making little or no headway ... when ... my eye catches something. There are two 2009 records and one has data. So I deleted all the 2009 join files, ran the script again and 25 minutes later (yeah, it is NOT a barn burner), the correct 2009 totals are there! So I had stumbled upon the solution but missed it because my earlier attempts had sabotaged me!
=
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.



A READER ASKS: Dynamic FileMaker Portal Filtering

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 or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).

A READER WRITES
I was looking at your web site, which is great! I am literally new to FileMaker Pro, I have some experience in database management, and I know if I am able to work with File Maker Pro I can do a lot of things. I would to know if you can help me either to give a small example or guide on where can I see an example on the web on how to display data/records based on a filter or a drop down menu.

Iet's say if I have data about a staff member, but I would like to be able to just show the date when I select the staff from a drop down list, hit select and then display the data, I don't want to see ALL of the data at once, I want this filter first, then based on my selection display the data.

I would really appreciate it if you can help me.

FileMaker Dynamic Portal Filtering
Single Character Filter With All Option

Although you didn't specifically say portal filtering, I found this old example file of mine and dusted it off. You can manipulate found sets of data with a pull down menu, a global field, a script and a script trigger. That would likely be a big chunk to try out of the gate. However, there are links on the web to show you how to do this.

FileMaker has the ability to dynamically change a relationship by altering the parent key information. Many times this is called a filtered relationship because you can add options to your parent key data that filters the resulting child records. I originally created this example to show off the ability to filter a portal by a single character. You can click a single character and see all your matching child records in a portal. Then it occurred to me, the user would also like to have the option of a birds eye view of all the possible related records. So I added the "All" option to the value list.

FYI... I only created example child records for A through C, so if you click any other option... the portal will not show any related records. Feel free to click the A, B, C and All radio button values and watch the portal update accordingly. You can also add new records to the child file were the company name or contact name start with letters other than A, B or C.

WHAT I DID TO THE CHILD TABLE - In the child table, I created two data fields, a calculated combination of those two fields, a calculated child key and an auto enter primary key. I don't even use the primary key field in the child table but it's a habit.

The two text fields are Company Name and Contact Name, which I use to calculate what I put in my portal to show the matching child record results.

Company With Calculation ( calculation - text result ) which is designed to show both the Company and Contact information unless one of those fields is empty. The to show just what data is there. The calculation is ...
Case(
IsEmpty(Company Name), Contact Name,
IsEmpty(Contact Name), Company Name,
Company Name & " - " & Contact Name)

The Company With Calculation is designed to show the user the company and contact information in the portal. We are combining two fields of data into one field, as long as both fields have data. This saves room in our portal row because we only have to have one field that can show X amount of characters. This is great when there isn't data in one of the fields or one of the fields has a lot of characters.

Child Key For Portal ( calculation - text result ) is my calculated child key field. It is used to show the first letter of the entered company and if the company name is empty, then it will show the first letter of the contact name. We use the left function to capture that one character. We are also adding the text string of "All" to every record but put a carriage in between them. This adds the effect of having a multiple line key field. The calculation looks like ...
Case(
IsEmpty(Company Name), Left(Contact Name, 1),
Left(Company Name, 1)) & "¶All"

WHAT I DID TO THE PARENT TABLE - We started off by creating Filter Character Global, a global text field. We added it to the layout and set it up to be a radio button type of value list. The value list has every letter of the alphabet, the number characters of zero thru nine and the text string of "All". We then setup our global field to be the parent key in the relationship to the field in the child file Child Key For Portal.

Next we added the portal to the layout and added the Company With Calculation field in the portal using the relationship we just created.

The file can be downloaded by clicking (here).

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

© 2007 - 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.



FileMaker 12 Release Links

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 or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).

For every new release I try to collect and post links about what folks are saying about its features and what they like about them. So here is my current collection for FileMaker 12, I'll continue to add new links to the list over time and please feel free to drop me a line if you find other links to add!

My article is now available at the web site for MacWorld UK magazine! It is titled "FileMaker 12 Review: Everything is new again and some of it is free!"

Added April 24th - Business Insider
FileMaker 12 Has New Features That Shouldn’t Go Unnoticed

Added April 17th - FileMaker Hacks
FM 12 ExecuteSQL, part 1

Added April 15 - MacLife
First Look: FileMaker Go 12

Added April 15 - Finanical Post
New FileMaker Go 12 for iPad and iPhone Apps Surpass 100,000 Downloads in First Week

Added April 15 - iLounge
FileMaker releases FileMaker Go 12 for iOS

Added April 15 - MacWorld Magazine
Review: FileMaker Pro 12 and FileMaker Pro Advanced 12

Added April 14th - The Support Group
A little bit of SQL in FileMaker 12

Added April 11th - Soliant - Martha Zink - YouTube Video
Delve into FileMaker Interface - Starting from Scratch

Added April 10th - dbservices
FileMaker 12 Managing Container Data

Added April 7th - SeedCode: Next
FileMaker 12: Why SQL? (Context Independent)

FILEMAKER ON YOUTUBE
FileMaker Pro 12 in 3 Minutes

FILEMAKER TECHNICAL SUPPORT
Miscellaneous behavior changes in FileMaker Pro 12

TABTIMES
A stylish database for iPad? First impressions of FileMaker 12

INFORMATION WEEK
FileMaker Gets iPad-Inspired Makeover

ZDNET
FileMaker 12 released; FileMaker Go now free on iOS

C/NET
Apple iOS users can get the new FileMaker Go app for free

MACWORLD US
FileMaker spruces up its look with FileMaker 12 release

TUAW
FileMaker Pro 12 offers new design features, improved iOS compatibility

NEW ZEALAND HERALD
Go the kicker for all new FileMaker

DBSERVICES BLOG
FileMaker 12 Whats New

BEEZWAX BLOG
FileMaker 12: Exciting New Possibilities

EXCELISYS BLOG
New FileMaker 12 Delivers Breakthrough Design Features for Creating Stunning Databases for iPad, iPhone, Windows and Mac

ISO FILEMAKER MAGAZINE
FileMaker 12 New Features

MIGHTY DATA
Announcing the 12 Days of FileMaker 12

FULL CITY CONSULTING BLOG
What I’ve been waiting for in FileMaker 12

©2012 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.

FileMaker Developer Learning Microsoft SQL Server

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 or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).

In addition to my Software Product Management Certification Course at University Of Washington, I recently signed up for an online based Database Management Certification course. I thought it would be interesting and thought it may stretch me due to it having zero Macintosh or FileMaker attributes. That logic turned out to be solid as a rock and I thought I'd share a recent experience about it with the FileMaker community.

Here you can see the homework assignment from week 5. The class isn't meeting my expectations mostly because it isn't organized or executed all that well. In order to take a swing at completing it, I needed to look outside of the UW resources provided for this class.


As many of you may know, I'm a big fan of the online libraries from Lynda.com and VTC.com. They have an empowering collection of FileMaker training videos for both beginner and intermediate developers. Back in early 2011, I went through the Lynda.com training series for Microsoft SQL Server. In fact, I even blogged about it with a posting titled "Dude, you like mentioned FileMaker twice!"  At the day job, my group has a corporate Lynda.com account, so I beat feet over to their site for illumination.

In essence, I was able to complete this homework assignment from scratch within a few hours with very little practical experience about Microsoft SQL server. The way Lynda.com lays out the content in shorter videos with superb documentation was a life saver!

Although easy in hindsight, the process is completely different how FileMaker accomplishes the same tasks. So I thought I'd jot down my notes in this posting.

PART 1: Installation Of Microsoft SQL Serer
That was pretty straight forward (at least for a Microsoft product install) and I didn't really need much guidance. The UW MSDN license came in very handy! I did this on my Macintosh running Bootcamp.

PART 2: CREATING THE DATABASES
After the install and launch SQL server, you simply right click the databases folder to create a new database. Give it a name and then right click the Tables folder to create a table. Out of the gate it wants you to define your columns, so you go ahead and do that. If you don't, you don't get a table. You control S to save. You repeat these steps for all the tables you need to create.

Creating the primary keys is a bit of a hassle and I can only imagine there is an easier way. I ended up creating my column and then right clicking the column to choose "Set Primary Key". The scrolling down in a lower accessed box to open up a Properties setting and then setting the identity spec to "Yes". The movie on Defining Keys was very helpful here.

PART 3: IMPORTING DATA
This was more challenging than I expected because FileMaker has always made this so easy for us. I had to review the movie about "Importing Excel files in SQL Server" a few times as I worked my way through some of the settings.

PART 4: BACKING UP THE DATABASE
This was a snap using the Lynda.com video. I submitted my backup file on line and got a 100% grade for the assignment almost immediately! 

©2012 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.