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 v12 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.

Files in Folder

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:

Edit Data Source

Manage External Data Sources

Click OK to return to 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.

Graph

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:

 

(i) double click on the table occurrence to bring up the Specify Table dialog. Copy the name of the table occurrence to the clipboard.

Specify Table

(ii) 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.

Specify Table - Select Contacts

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

(iii) 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 occurrences:

Table Occurrence Match Field Table Occurrence Match Field Notes
Contacts _kp_ContactID ContactsMessages _kf_ContactID Use your Contacts Primary Key field
Contacts Country ContactsCountryCodes CountryName Use your Contacts Country Name field
Contacts PhoneMobileIntFormat ContactsReplies SenderPhoneMobileIntFormat Copy the PhoneMobileIntFormat to your Contacts table
Contacts _kp_ContactID Messages _kf_ContactID Use your Contacts Primary Key field
Contacts zCreatorAccount Preferences N/A – uses the  Cartesian Product Relationship operator N.B. this relationship uses a Cartesian Product Relationship so you can use any field in your Contacts table
Interface zv_Pivot_g ContactsPivot _kp_ContactID This is used for the Trial Version and can be ignored
BulkSessions _km_ContactIDs BulkSessionsContacts _kp_ContactID Use your Contacts Primary Key field
BulkSessions _kz_SelectedRecipient_g BulkSessionsContactsPreview _kp_ContactID Use your Contacts Primary Key field
Replies SenderPhoneMobileIntFormat RepliesContactsByMobile PhoneMobileIntFormat Copy the PhoneMobileIntFormat to your Contacts table
RepliesMessages _kf_ContactID RepliesMessagesContacts _kp_ContactID Use your Contacts Primary Key field
DeliveryReceiptsMessages _kf_ContactID DeliveryReceiptsMessagesContacts _kp_ContactID Use your Contacts Primary Key field

 

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” & “; ”  &

“Type: Form” & “; ”  &

“ContactID: ” & Contacts::yourPrimaryKeyField & “; ”  &

“”

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” & “; ”  &

“Type: Form” & “; ”  &

“ContactID: ” & Contacts::yourPrimaryKeyField & “; ”  &

“”

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.

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.

Add fmSMS Data Source

 

Your Solution Data Sources

 

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:

Link to fmSMS Data Source

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 ( [

phoneNumber = Filter ( Mobile Phone ; “0123456789” ) ;

phoneLength = Length ( phoneNumber ) ;

countryCode = ContactsCountryCodes::CountryCode ;

phoneTrim = Case ( Left ( phoneNumber; 1) = “0”;

Right ( phoneNumber; (phoneLength -1)); phoneNumber) ] ; countryCode & phoneTrim )

// 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.