What are the opening hours?

Monday 9am - 5pm Tuesday 9am - 5pm Wednesday 9am - 5pm Thursday 9am - 5pm Friday 9am - 5pm

http://www.fullcityconsulting.com/

FullCity Consulting, Red Hook, NY (2026)

07/24/2022

The crew at has been asked to execute a special mission for a client. Here we go!

04/13/2022

We love supporting our clients every possible way

03/13/2022

Custom software can help every type of business reach its highest potential

04/20/2015

I came across a Swift fun fact a couple days ago courtesy of Global Nerdy. It turns out you can use Emoji (those symbolic characters that help mobile phone users around the world express real human emotions) in certain components of your code, including variables. That got me wondering about FileMaker. We have variables. We have real human emotions. Can we play too?

It turns out we can on the Mac (I haven’t attempted this on a PC). Fundamentally, Emoji are characters like (this week’s sponsors) the letter Q and the number 2. Given that you can name a variable just about anything you like, there’s no reason you can’t give it an Emojinal name. Here’s how I got it to work in FileMaker Pro Advanced 13 running on OS X Yosemite.

Create a new script and add a Set Variable script step.

In the Name box, type in two dollar signs.

Open up your Character Viewer. (If this items doesn’t appear in your menu bar, go to your System Preferences, open the Keyboard Preference Pane, choose the Input Sources tab and check “Show Input menu in menu bar”.)

Select “Emoji” from the left column then drag the Emoji character of your choice to the Name field in the Set Variable dialog box. I chose a globe because it’s a global variable. I find this amusing despite your groans and eye-rolling.

Select the contents of the Name field and copy.

Enter something in the Value field. It doesn’t really matter what you put here so long as you don’t leave it blank. Click OK.

Add a Show Custom Dialog script step. Give it any title you want and paste your variable name into the message body.

Save your script and run it. If it works, you’ll see the value of your Emoji variable in the dialog box.

This technique probably has no practical applications, so I wouldn’t use it in a production database. Certainly not a mixed platform, multi-user production database. Still, it’s a pretty decent FileMaker geek party trick.

04/08/2015

Scanning the growing list of functions of the week, I noticed a lack of date and time functions. Dealing with temporal matters isn’t always easy. If time were a base-ten system with, say, ten hours in a day and a hundred minutes in an hour, calculating would be a lot easier. That said, most of the neuron straining elements of dealing with time are already baked into FileMaker. All you have to do it learn how to wield them. We’re going to start with three day functions.

Day ( date )

The Day function hands back the numerical date of the date in its parameter. Put like that, it probably sounds a bit useless. If you’re putting a date in the parameter, you already know the number of the day in question.

The above use of Day will work, but it’s true purpose is figuring out the day from a date value in a field. Suppose you have a process that must run on the first day of every month. Combining Day with Get ( CurrentDate ) will derive the current day of the month (any month). Now you just have to see if the result of the calculation below equals 1 and you can go from there.

One more scenario. You’re the party planner for a big company that throws a little birthday observance at the middle and end of each month. To get the right names on the cake you need to know whose birthdays fall on the first through the 15th, and whose come in the second half.

If you’ve already got individuals’ birthdays in your database, the calculation above neatly classifies each person according to which of the two monthly they’ll be feted.

DayOfWeek ( date)

This function will give you a numerical value for the day of the week that the provided date falls upon. It’s an efficient way to, say, determine whether a date falls on a weekend. Or if you have tasks for certain days of the week, DayOfWeek can be used to enforce that they only happen the proscribed day. DayOfWeek assigns 1 to Sundays, 2 to Mondays, and so on. (If I ran the zoo we’d assign 1 to Mondays so that weekend numbers would be consecutive, but there’s probably a good reason why I’m not in charge.)

April 12th, 2003 happened to fall on a Saturday, so DayOfWeek assigns it a value of seven. You take the concept a bit further to check and adjust dates based on the day of the week. Suppose you have automatically-created shipping dates in an order system. DayOfWeek can be employed to check the default shipping dates and, if they land on a weekend, bump them back to the following Monday.

This one takes a bit more unpacking. If you’re not familiar with the Case function, check out this post first. The Case “wrapper” essentially lets us test different scenarios and provide an outcome for each. Line 2 of the calculation asks “does the date in the shipDate field fall on a Sunday?” If the answer is true, line 3 is invoked. Line 3 adds one day to the shipDate, giving us a Monday date and FileMaker ignores the rest of the calculation.

If the answer to the question posed in Line 2 is false, FileMaker ignores line 3 and skips to line 4 (I’m ignoring blank lines for numbering purposes). Line 4 asks “does the date in the shipDate field fall on a Saturday?” If that answer is true line 5 is evaluated giving us a Monday date, this time by adding two days. As before,if Line 3 was true, FileMaker stops calculating at this point.

Should the questions posed on Lines 2 and 4 both be false FileMaker evaluates Line 6, known as the default result. Line 6 essentially says “keep the original ship date”. If we get to Line 6, we’ve already determined that the ship date isn’t on a Saturday or a Sunday, so it must fall on a weekday where no change is necessary.

DayName ( date )

Knowing what you now know about DayOfWeek and Case (or Choose, if you’re really fancy), you’ve probably already figured out how to turn DayOfWeek results into the names of the days of the week. But FileMaker has baked that logic right into its own function. Want to know what day you were born on? Put your date of birth into the parameter of a DayName function and FileMaker will reveal it to you. Using DayName, you can calculate a message like “Your order will be shipped on Thursday, April 24″.

03/15/2015

After a brief hiatus, the Function of the Week is back and just as ordinary, normal, typical, standard and regular as ever! Why so mundane? Because we’re picking apart the Average function this week and we’ve caught the Average Spirit! Average might not be new or sexy, but there are three variations in its application and if you apply them incorrectly, you may end up with below-average satisfaction.

Averaging Multiple Fields

In this example, we have three fields named numberA, numberB and numberC. We didn’t have to stop at 3 fields; the Average function allows us to string together as many fields as we like. When evaluated, FileMaker will add up all the non-blank values in those fields and divide by the number of values it found. Non-blank is a critical feature of how Average computes. Take a look a the examples below.

The top example above works exactly as you’d expect an Average function to operate. Each of the three fields contains the value 2. FileMaker adds them together to get 6 and divides by 3 (the number of values present) to derive an average value of 2.

In the middle example, one of the three fields is blank. So the sum of the three fields is now 4. But 4 divided by 3 is (approximately) 1.33 and FileMaker is insisting that the average of the three fields is still 2. This is where non-blank comes into play. FileMaker is still dividing the sum by the number of values present and because one of the three fields is blank, only two values are present. Thus 4 ÷ 2 = 2.

The bottom example shows when FileMaker would return an average of 1.33. Now, instead of being empty, the numberC field contains a zero. With three values once again present, FileMaker divides 4 by 3.

The non-blank stipulation wasn’t invented by FileMaker. A fundamental tenet of mathematics is that zero is not the same as nothing. While scholarly papers have been written on the topic, this Difference Between Article boils is down nicely. If you care to go a little deeper, Stuff You Should Know has a great podcast about zero.

Averaging Related Records

If you toss a single related field to Average, it’s going to give you the average of the non-blank values in all the related records. A valid relationship and a number field (or calculation field with a number result) are all that is necessary to calculate the average. The related field does not have to appear in a portal or even on a layout at all. If you do happen to have a filtered portal displaying some, but not all, related values, keep in mind that FileMaker will always calculate the average based on all the related records. Filtered portals do not affect the calculation result.

As soon as you give Average more than one field, and a combination of local and related values like the example above is perfectly acceptable, FileMaker reverts to averaging multiple fields. It will no longer average all the related values. Instead, in the example above, FileMaker will take the first related value for relatedTable::numberField, add that to the value for numberA in the current record, and compute the average of those two.

Averaging a Repeating Field

Your humble blogger is of the opinion that repeating fields are an anachronism from FileMaker’s pre-relational days, now two decades behind us. Many developers whom I know and respect disagree with me on this point and it certainly won’t be resolved in a blog post about the Average function. But the function, like the rest of FileMaker, still supports them, so here’s what you get.

With a single repeating field for your Average parameter, FileMaker will average all the non-blank values appearing in the repetitions as illustrated below, very much akin to averaging a single related field.

If you want to average across multiple repeating fields, you can do so by creating a new calculation field and inserting your repeating fields as the parameters of an Average function. Be sure to set the calculation to contain the same number of repeats as the number fields you’re averaging. An example of the output appears below.

Do this is you want to make me cry.

Like so many FileMaker functions, Average is superficially quite simple. Knowing the vagaries of how it’s implemented will help you be successful in using it.

02/03/2015

The past few posts have covered an array of text parsing functions. There are countless scenarios where you may need to manipulate text in FileMaker, but the two I most often encounter are data migration and data grooming. I don’t know how many times I’ve been tasked with taking a free-form telephone number field, laden with inconsistent information and formatting, and cleanly breaking it out into discrete fields for the area code, phone number, and extension. When you have to clean up sloppy data like that, the Left, Right and Middle functions I’ve been writing about just aren’t sufficient for the job. Adding the Length and Position functions to the mix brings significantly more power.

Length has just one parameter for some text or the name of a field containing text. The result you get is the number of characters that appear in that text. FileMaker counts every character including punctuation, returns, and spaces.

The Position function is a bit more involved. The first of its four parameters is text. In the above example our text is the word “HELLO”. The second parameter is a search string, or more plainly put, it’s whatever you’re looking for. We’re looking for the letter L in this example. The third parameter specifies where in the text FileMaker should start scanning for that search string; the number 1 here tells FileMaker to start at the very beginning of the text. The final parameter is the occurrence. Because your search string can appear more than once in the text, you must specify which match you’re seeking. My text has two Ls and by putting a number 2 in the last parameter, I’ve told FileMaker to give me the position number of the second occurrence of the letter L. In this case, the second L is the fourth character in the text so the Position function returns a 4.

Now that we’ve got the tools, here’s how we can put them together for some tricky data grooming. I’ve got a set of inconsistently formatted phone numbers from which I need to extract the extensions. Of course, not every extension is the same length and one even has a hyphen. The only thing I can count on here is that extensions are preceded by a letter x and are the rightmost information in the field. I need to construct a calculation that will provide me with all the characters between the x and the end of the field.

Since we know the extension will always be the rightmost part of the phone number field, we can use the Right function with the phone number field as it’s first parameter. The second parameter of the Right function (line 3 in the example image above) is the number of characters to return. This is where we have to figure out how many characters each extension is. So we start with the Length function to determine how long the entire phoneNum field is. Next we use the Position function to get the location of that letter x at the start of each extension number. Subtract the Position number from the Length and you’ll get the number of characters in the extension.

Taking the third phone number in the example below, Length ( phoneNum ) is 22 and Position ( phoneNum ; “x” ; 1 ; 1 ) equals 18. Subtract 18 from 22 and to get 4- the number of characters in that extension.

Multi-step examples like these can be a bit much to try to comprehend just by reading. So build yourself a little example database with the fields and calculations presented here and you’ll see it all come together. It’s another example of how very simple functions can be combined in powerful and complex ways.

01/27/2015

Having previously ruminated on the Left, Right and Middle functions, then the LeftWords, RightWords, and MiddleWords functions, we’re going to wrap up with a third triad of functions, namely LeftValues, RightValues, and MiddleValues. Before we dig into the functions though, a definition is in order.

When the word “value” appears in the name of a FileMaker function, it has a very specific meaning. A value is a discrete line of text in a return-delimited list. It can take the form of a plain text string, the contents of a field, the contents of a variable, or simply some text that is calculated on the fly. For the purposes of any Value function, any amount of text that starts and/or ends with a line break is a value.

Butter

22

Eggs

White Sugar

Brown Sugar

100% Pure Madagascar Vanilla Extract

The items above are a return-separated list and each line is a value to FileMaker (for webpage readability, I’ve included bullets at the start of each line; you don’t actually use bullets in FileMaker). Whether it’s one word, a number, or a combination of the two, they’re all individual values. Even the blank line is a value (granted it’s a null value, but a null value is distinct from no value at all).

Like their Left, Right, and Middle brethren, the Values functions require some text, a number signifying how many values to return and, for MiddleValues, the number of the first value to return. Below are three examples of Values functions drawing from a single return-delimited list.

Clever and learned readers such as yourself are apt to point out that there’s a GetValue FileMaker function that could arguably render LeftValues, RightValues, and MiddleValues completely redundant and unnecessary. Not so, I say. GetValue can only return one specific value whereas the three Values functions discussed here can obtain any given number of values from a list.

Clever and leaned readers willing to concede the previous point might go on to argue that, given how a return delimited list is by definition a vertical stack, LeftValues and RightValues really ought to have been named TopValues and BottomValues. I’m with you on this one even though such lists can be represented as “value 1¶value 2¶value 3¶value 4″. My hunch is that a judgement call was made in favor of being consistent with the other suites of Left, Right, Middle functions.

01/20/2015

Last week we looked at the Left, Right, and Middle text parsing functions. These are extremely useful when you need to parse out highly regular data like, say, a telephone number where you can count on the first three characters to be an area code (in North America anyway). But when you’ve got less rigidly structured information like names, you can’t be sure how long or short any given name might be. This is where LeftWords, RightWords, and MiddleWords can save you some significant calculation gymnastics. Rather than forcing you to calculate where one word ends and the next begins, the Words functions figure it out for you.

All three functions require some text for their first parameter; this can be a plain string of text in quotes, the name of a field that contains text, or any expression with a text result. For the Left and Right variants, the only other parameter is the number of words to be included. MiddleWords requires the number of the word you wish to start with followed by the number of words to include. The table below lays out several examples for how FileMaker treats data thrown at these functions.

Examination of above examples reveals that FileMaker’s definition of a word depends very much on context. The boundary between words is almost always a space character and these are handled exactly like you’d expect. However, most other punctuation characters are treated as word breaks too, as lines 3 and 4 illustrate. A hyphen breaks words when it has alphabetical characters around it, but not when surrounded by numbers. I personally find it counter-intuitive that FileMaker considers an underscore a word break, but not a period. Any punctuation followed by a space will be discarded from the calculation result as you can see with the “Mr. F” and telephone number examples.

You can beat the system using non-breaking spaces. A non-breaking or hard space looks just like a normal space, but gets special treatment. To create one using a Mac, hold down the option key when pressing the spacebar. On Windows, hold down the alt key and, using your numeric keypad, type 0160. When you release the alt key, the space should appear. FileMaker will recognize them and treat text with non-breaking spaces as a single word.

Line breaks are also word breaks in FileMaker’s world and behave as you’d expect with the three Words functions, but next week we’ll look at a third variety of Left/Right/Middle function that specifically deal with line breaks.

01/13/2015

Text parsing pops up a lot when migrating data from an old system to a new one, or even when you realize that your data just needs some grooming. Whatever the impetus, Left, Right, and Middle are the cornerstones of text manipulation in FileMaker. All three simply extract a portion of the text you supply. Let’s start with Left and Right.

Both Left and Right take two parameters. The first is some text either as a string in quotes as shown above, the name of a field that contains text, or an expression with a text result. The second parameter is the number of characters you want returned from the text in the first parameter. For Left FileMaker starts at the first character on the left and returns the number of characters specified. Right, as I’m sure you’ve worked out by now, starts at the opposite side. Middle is just a tad more involved.

Middle requires a third parameter. The first parameter is the source text as described above. The second parameter is the position where you want FileMaker to start extracting text. In the example above I use the number 5 because I want to pull out the telephone number prefix which starts with the fifth character in the string, following the three digit area code and the first hyphen. The final parameter specified the number of characters to be returned.

Cleaning up phone numbers is a classic data migration chore, which is why I chose it for the examples here. Unfortunately, when you have to groom data it’s rarely as neatly formatted as shown here. But in some coming posts, I’ll present a few more text manipulation functions you can add to your kit.

FullCity Consulting

07/24/2022

04/13/2022

03/13/2022

04/20/2015

04/08/2015

03/15/2015

02/03/2015

01/27/2015

01/20/2015

01/13/2015

Address

Opening Hours

Telephone

Website

Alerts

Contact The Business

Shortcuts

Share

Monday	9am - 5pm
Tuesday	9am - 5pm
Wednesday	9am - 5pm
Thursday	9am - 5pm
Friday	9am - 5pm