Integration»Linking To Other Tables

Linking To Other Tables


Most of the time we think of emailing, or sending letters to, "contacts" and thus we send lists of contact IDs to fmSpark. However, you may want to base your mailings on things other than found sets of contacts. For example, if you wanted to send an email to everyone who ordered a certain product last year, you may want to base this on a found set of "sales" instead of contacts, so that you can include information about the sale: the date of the sale, the name of the product, etc.

Now some of this information may be accessible through a contact's record: this tends to be aggregate information like the contact's "last" purchase, their "total" purchases, etc. We'll begin the notes below by showing how to add these kinds of merge fields to fmSpark. If you don't want the "last" sale, but rather want to reference a specific sale, you'll follow the instructions in the second part for sending lists of sales to fmSpark.

In the second part of the instructions below will show you how to link fmSpark to more than one table in your solution, so that you can send lists of sales OR contacts to fmSpark. (Of course you'll still be sending your mailing to contact, but this will let you send it to the contact associated with a specific sale.)

Part 1: Linking additional tables to your Contacts table occurrence in fmSpark

One of the easiest ways to extend fmSpark is to hang new Table Occurrences (TOs) off of the Contacts table occurrence in the relationship graph of the fmSpark file. This can give you access to aggregate information about other entities related to a contact, such as the contact's orders, payment history, etc. You can even use calc functions like Sum() and Last() in your mailings to get at information for which you may not have calcs in your data files.

For example, let's say that you have an Orders table and the graph in your contacts file looks like this:

Here is how you'd access that information in fmSpark...

1. Add the relevant parts of the graph to fmSpark. You'll want to "mirror" the relevant portions of the graph in fmSpark, hanging new table occurrences off of the CONTACTS table occurrence already in fmSpark (making sure to keep the name of CONTACTS as is, but naming your new TOs as you see fit.) Like this:

2. Add records for your tables in the developer settings. Select "Settings..." from the fmSpark menu and then click he "Developer" icon in the upper right. Navigate to the "Fields and Functions" tab and the select the "Table List" tab. Click "New Table" in the bottom right and add entries for each of the new table occurrences you added to the graph. Be sure to enter the table name exactly as you did in the relationship graph.

3. Rebuild the field list. Now switch to the "Field List" tab and click "Rebuild". You'll see that you now have access to all the fields in your newly created table occurrences hanging off CONTACTS.

4. Use the new fields in your mailings. Now you can use fields from Orders and OrderLines in your mailings to a contact. If you've sorted the relationship between CONTACTS and CONTACTSOrders to be descending by date in the fmSpark relationship graph, you can use the Last() function to get information about the last order the customer placed. Note that "Last (" is inside the <<brackets used to merge data into the letter.

You can really use any calc inside fmSpark's mailings once you have some additional TOs attached to CONTACTS. Here we've wrapped a Sum() function inside fmSpark's CurrencyFormat function to show a contact their total purchases. If the relationship to CONTACTOrders showed only unpaid orders, then this would be showing the contact's total unpaid balance:

You can get quite creative with this and leverage your knowledge of FileMaker calculations (and remember, these fields are NOT added to your table's schema, they just exist as merge fields on a letter, keeping your data tables as simple as possible). The merge information below...

...returns "Here are the last two items you purchased; 1 Cordless Drill(s) and 2 Rechargabe Battery(s)." Remember that in order for things like Last() to be meaningful, your relationships in the fmSpark graph will need to be sorted.

5. Going further. Note that you can have relationships in the fmSpark graph that don't exactly mirror those in your data file. For example, you might want to make a relationship that only shows unpaid invoices, or order items that have not yet shipped. You can easily access all this information in your mailings using fmSpark.

Part 2: Sending lists of other entities to fmSpark


Creating a new Merge Group is one way to link fmSpark to tables in your own file. You may not need to do this, however, as most integration can be handled by pointing fmSpark at your file and perhaps attaching new Table Occurrences (TOs) to the TOs already there. Read the section on LinkingToOtherTables for an overview of when creating a new merge group might be necessary.

If you know that you need to create one, the instructions below will show you how.

In this example we'll create a new Merge Group for an Invoices table so that we can send fmSpark a found set of Invoices instead of a found set of contacts; fmSpark will then use the contact information for the contact associated with each invoice we send.

1. File References. Before you begin this be sure your file is in the same relative location to fmSpark (in the same folder) as it will be when the files are deployed. Then, if you haven't yet made use of your file in fmSpark, create a new file reference ("data source" in FM9) for your file in fmSpark.fp7 by selecting "Manage External Data Sources..." from FileMaker's File menu and click "New" to create a reference to your file containing invoices.

2. Relationship Graph Changes. Open the relationship graph in fmSpark and notice how there is a CONTACTS table occurrence (TO) and a _CONTACTS Recipients TO. (You may also have one called "_Contacts LogTable") These are the instances of your data in fmSpark: you'll create one of each of these for the table you'll be sending to fmSpark in this new merge group. In our example, this will be the invoices table:

Notice how we've created one called "INVOICES" and a second called "_INVOICES Recipients". You can substitute whatever you'd like for the word "invoices" but you'll want to keep the underscore in the second TO name, and I think keeping the capitalization and syntax the same makes it easier to keep things straight.

In both TOs we've use the primary key from the Invoices table in the right hand side of our relationship and on the left hand side we've used the fields "EntityID" and "entityIDs_backup" already in use in the CONTACTS... relationships.

3. Linking Contacts to the New Merge Group. Now we need to know about the contacts associated with each invoice. If your new merge group represents "people" you won't need to perform this step, but if you new group represents something else, like invoices in our example, you'll need to teach the relationship graph about who should be contacted about a given invoice. Do this by adding a TO for your contact's table to the right of each of the new Merge Group TOs as shown below:

Note that in both cases we link to invoices using the Contact's ID and that in the case of the "..._recipients" layout we keep the leading underscore in the name.

4. Duplicate the Manage Recipients Layout. Note that when working with layouts in fmSpark you may find the menus reverting to the custom fmSpark menus we use when the file is doing its work. If that happens you'll see things like the "Layout" menu disappear. Simply use the Tools menu in FileMaker Advanced to select the "Standard FileMaker Menus" from Tools / Custom Menus and you'll be back to work.)

Duplicate the "manage recipients (CONTACTS)" layout, renaming it with the name of your new group ("Invoices" in this example).Next, double click on the portal on this layout and change the target of the portal to match the new "..._recipients" TO created in step 2 above ("_INVOICES Recipients" in our example). You'll now double click on the field in this portal and select the field which best represents your contact's name: on our example this field is in the TO "_INVOICES Recipients Contacts". You may want to add other fields to this portal: for example, if your Merge Group, like our invoices group, doesn't represent people, you may want to include some fact about the entity itself (the invoice in our example) in the portal. We've chosen to include the invoice number, but it is easiest to do this at the end of step 4 after you've already gotten the field's conditional formatting and script parameter fixed.

Continue editing this layout by right clicking on this field in the portal again and selecting "Conditional Formatting..."Change the conditional calculation for the field to include the primary key of your table:

not MemberOf ( _Merge Batch::entityIDs ; _YOUR Table::YourKeyField )

In this case "_YOUR Table::YourKeyField" above refers to the field linked to the "_Merge Batch" TO in step 2 above, not the primary field in your contact's TO which may be to the right of it.

Finally, double click on this field in the portal again and you'll see it is formatted as a button to call a script. Change the parameter of the button attached to the field to use your key field. In our example, replacing the parameter of "_CONTACTS Recipients::Contact ID" with the "_YOUR Table::YourKeyField" we just used above: _INVOICES Recipients::InvoiceID_kprime

5. In Developer Settings, Create the MergeGroup Record Select "Settings..." from the fmSpark menu and then click the "Developer" icon in the upper right to arrive at the Developer screen. Once there, create a new MergeGroup with the name that you encoded above (e.g. "Invoices" in our example). Put the layout name in, from step 4 above. And add a Tool Tip format calculation if you'd like:

7. Add records for your tables in the developer settings. Navigate to the "Fields and Functions" tab and the select the "Table List" tab. Click "New Table" in the bottom right and add entries for each of the new table occurrences you added to the graph. Be sure to enter the table name exactly as you did in the relationship graph. You won't be adding the "..._recipients" tables, just those to the right of "_Merge Message": "INVOICES" and "INVOICES Contacts" in our example. Mark these new tables as belonging to the merge group you names n step 5 above: they likely won't belong to the Contacts group in addition to the new group ("Invoices" in our example).

8. Rebuild the field list. Now switch to the "Field List" tab and click "Rebuild". You'll see that you now have access to all the fields in your newly created table occurrences hanging off CONTACTS.

9. Link One or More Templates to the New Group. Switch back to the "Settings" screen and you'll likely want to link the "Blank" templates to the new group. That way, your users at least have a blank template to select when they come in with the new group. You may also want to add additional templates specifically for the new group, but you can do that later.

Note that fmSpark determines which merge fields you can see by which Merge Groups your selected template is linked to. So linking a template to both Contacts and Invoices (for example) may make sense for a blank template, you'll want most of your templates associated with only one merge group. With templates that are only linked to Invoices so you only see the INVOICES Contacts merge fields instead of those and merge fields from CONTACTS (which wouldn't apply to records you created from Invoices). (The template's merge group is also what determines which "manage recipient" layout is used, so if you don't see the layout you expect, check to see if your template isn't linked to more than one group.)

10. Import the fmSpark Scripts Into Your File. If this new mere group is the first one to link fmSpark to your file, you'll need to add the fmSpark scripts to your file. If you already have the scripts in your file, you'll duplicate them and edit them to use the Primary Key of your new merge group instead of the ID of your contacts.

Follow steps 6 and 7 of our Integration/QuickStartGuide. In our example you'd use the field "Invoices::InvoiceID_kprime" in the Set Variable line in step 7. You'll need to further edit the three imported "Create..." scripts to indicate the new media group you've created. You'll find a statement for this near the beginning of each script, highlighted in blue below:

That's It. You can now call these newly imported scripts from your file and send mailings to found sets of invoices. Enjoy!

Going Further: Add Group Specific External API scripts There's an "encode Group ( Group* )" script that is performed in the "Open Spark ( IDList* )" script. Currently, it encodes "_app::gDefaultGroup" (a calculated Global set to "Contacts"). After duplicating, you can encode whatever you want to call your Groups -> "Students", "Parents", "Gifts", "Contacts", etc.

Journaling Your New Merge Group: Additional Changes May be Necessary You may need to modify the "create journal entry" script to accommodate the additional group(s). TOs may have to be added to support the additional journal record create.

Original source (login required):