fmSMS Integration - Linking

Linking the fmSMS file to your existing FileMaker solution is the quickest way to integrate fmSMS and be up and running sending SMS messages from your solution. You will link fmSMS to your existing FileMaker solution and end up with 2 linked files. We’re going to use the FileMaker Pro Contacts Starter Solution as our example solution file we wish to integrate with.

Step 1: Backup Files and Create Fields

Before proceeding with the integration please backup your existing solution file/s – you want to have a stable version you can revert to in case something goes wrong. We also recommend making a backup of the fmSMS file so you can refer to this to see how it was originally (particularly when updating layouts).

Once you’ve made a backup of your solution files copy the fmSMS file into the same folder as your solution file/s.

Your solution file is going to need a few existing fields in your Contacts table that we will reference:

ContactID – the primary key field for your Contacts table, typically this is an auto-enter serial number field

Contact Name – typically a field for First Name and another field for Last Name

Mobile/Cell Number – the field that stores the Contact’s mobile/cell number as you would normally dial it (no country codes)

Country – this is required to we can generate the recipient’s number in the international format with the country code (e.g. 61412345678). If you will only ever be sending messages to recipients in the one country you can hard code the country code instead, but we will be using a Country field in these instructions).

Mobile/Cell Number International Format – stores the international formatted version of the Contact’s mobile/cell number. You can copy the field PhoneMobileIntFormat from the fmSMS Contacts table to use here.

If you will be using fmSMS with FileMaker Go for the iPad or iPhone there are a number of fields that we use for the FileMaker Go layouts (you can copy/paste these from fmSMS as well):

SortField_c

zv_SortOrder_gt

zv_QuickFind_g

Step 2: Create External Data Source

Once you have the required fields in your Contacts table open fmSMS. We need to create a new External Data Source to your FileMaker solution file To change the Contacts references in fmSMS select select File>Manage>External Data Sources. Click the New button then click the Add File button and select your FieMaker solution file that should be in the same folder as fmSMS:

All the table occurrences on the graph for the Contacts base table will need to be updated to point to your Contacts table in your existing FileMaker solution file, and then the relationship match fields will need to be updated to reference the equivalent fields in your Contacts table.

Step 3: the Relationship Graph

Now select File>Manage>Database and click on the Relationships tab to view the graph. We need to change all the red Contacts table occurrences to use the new External Data Source that you just created.

First make a noted of each of the match fields used in the relationships from the Contacts table occurrences (or refer to your copy of fmSMS or the list below) then for each red Contacts table occurrence:

Double click on the table occurrence to bring up the Specify Table dialog. Copy the name of the table occurrence to the clipboard

From the Data Source menu at the top change it from “Current File (fmSMS.fmp12) to the Data Source you created in Step 2 then select your Contacts table.

The name of the table occurrence has probably changed – if so paste back the original name that you copied at step 1 and click OK.

The table occurrence will probably have collapsed as the match fields for the relationships to other table occurrences have broken. You will need to click on each of the relationship lines to edit the relationship and select the match field for each of the relationships (the majority of the relationships are to the primary key field of your Contacts table). Repeat this process for each of the red Table Occurrences based on the fmSMS Contacts table.

Here’s a list of the match fields for each of the relationships to the Contacts table occurrence:

Step 4: update fields in fmSMS Messages table

A number of auto enter calculation fields will need to be updated to reference the appropriate field from your Contacts table. To update these select File>Manage>Database and click on the Fields tab. Select Messages from the Table menu in the top left hand corner. For each of these fields you will need to double click on the field and then click the Specify button next to the “Calculated value” tickbox to update the Auto-Enter calculation formula to point to your equivalent field in your Contacts table.

RecipientCountry

RecipientPhoneMobile

Step 5: update Layouts

You will need to update the fields on several layouts to specify the appropriate field from your Contacts table. Change to Layout Mode and navigate to each of these layouts and specify the appropriate fields from your Contacts table:

Contacts List (specify all fields on this layout)

You also need to update the parameter attached to the New SMS button. Double click this button then click Specify then Edit to update the Script Parameter to reference our Contact primary key field. It should look something like this:

"Table: " & "Messages" & "; " &<br>"Type: Form" & "; " &<br>"ContactID: " & Contacts::_kp_ContactID & "; " &<br>""

Contacts Form (specify all fields on the left hand side of the layout)

You also need to update the parameter attached to the New SMS button. Double click this button then click Specify then Edit to update the Script Parameter to reference our Contact primary key field. It should look something like this:

"Table: " & "Messages" & "; " &<br>"Type: Form" & "; " &<br>"ContactID: " & Contacts::_kp_ContactID & "; " &<br>""

Contacts Form WebViewer (specify all fields on the left hand side of the layout)

Contacts List - iPad (this layout is used for FileMaker Go access – you can ignore this if you will not be using fmSMS on FileMaker Go)

Contacts List - iPhone (this layout is used for FileMaker Go access – you can ignore this if you will not be using fmSMS on FileMaker Go)

Contacts Form - iPad (this layout is used for FileMaker Go access – you can ignore this if you will not be using fmSMS on FileMaker Go)

Contacts Form - iPhone (this layout is used for FileMaker Go access – you can ignore this if you will not be using fmSMS on FileMaker Go)

DEV Contacts (this is a “developer” layout – you can delete this or quickly create a new one)

Messages List (specify the field under Recipient Name)

Messages Form (specify the field next to Recipient)

WebReplies (this is used for incoming replies via the FileMaker PHP Web Publishing – if you’re not getting replies pushed to your server you can ignore this)

Step 6: create relationship in your Contacts file to CountryCodes

We now need to create a relationship in your Contacts file to the CountryCodes table in the fmSMS file – this is required for the PhoneMobileIntFormat field, which is the field that stores the Contacts mobile/cell number in the international format with the country code and no leading zeros. If you are planning to only send and receive messages to one country you can hardcode the country/country code value and ignore this step.

We need to create a new External Data Source from your FileMaker solution file to fmSMS (the opposite of what we did in Step 2). Open your solution file and select select File>Manage>External Data Sources. Click the New button then click the Add File button and select the fmSMS file that should be in the same folder as fmSMS.

Click OK to return to your solution file.

Now select File>Manage>Database and click on the Relationships tab to view the graph. We need to create a new table occurrence called ContactsCountryCodes – click the Add a Table button in the bottom left hand corner to display the Specify Table dialog box. From the Data Source menu at the top change it from “Current File (your solution file)” to fmSMS then select the CountryCodes table:

Change the name of the table occurrence from CountryCodes to ContactsCountryCodes and click OK.

Now create a relationship from your Contacts table occurrence to the ContactsCountryCodes table occurrence. You will be matching your CountryName field to the ContactsCountryCodes::CountryName field.

We now need to update the auto enter calculation for the PhoneMobileIntFormat field. Click the Fields tab then double click on the PhoneMobileIntFormat field and then click the Specify button next to the “Calculated value” tickbox to update the Auto-Enter calculation formula. It should look something like this:

Let ( [<br>phoneNumber = Filter ( PhoneMobile; "0123456789" ) ;<br>phoneLength = Length ( phoneNumber ) ;<br>countryCode = ContactsCountryCodes::CountryCode ;<br>phoneTrim = Case ( Left ( phoneNumber; 1) = "0"; Right ( phoneNumber; (phoneLength -1)); phoneNumber)<br>] ;<br>countryCode & phoneTrim<br>) // Let

Click OK to return to your solution file.

Step 7: testing

That’s it! You can now open the fmSMS file and you should now see your Contact records in the Contacts module and be able to create and send a new SMS to a Contact.