fmAccounting Link (MYOB AccountRight Edition) Preview Video

/1 Comment/in , , , , , , , , /by

We’ve just uploaded our first preview video for fmAccounting Link (MYOB AccountRight Edition). This video demonstrates the following:

  • authenticating against the MYOB AccountRight API
  • downloading a list of available AccountRight Company Files
  • selecting the AccountRight Company File and entering the credentials for the selected Company File
  • uploading a Contact from FileMaker Pro to AccountRight
  • uploading an Invoice from FileMaker Pro to AccountRight
  • uploading a Payment from FileMaker Pro to AccountRight
  • downloading Chart of Accounts, Tax Codes, Categories and Inventory Items from AccountRight to FileMaker

The video also demonstrates how you can integrate with MYOB AccountRight (a Windows only application) from a Mac OS X computer.

You can watch the video below or directly on YouTube via this link.

We should be releasing fmAccounting Link (MYOB AccountRight Edition) in the next couple of weeks – please Contact Us if you have any questions in the meantime.

Databuzz to Exhibit at the 2013 FileMaker Developer Conference

/in , , , , , /by

FileMaker Inc. have recently released the schedule for the 2013 FileMaker Developer Conference, to be held in San Diego, California from August 12-15. We’re excited to announce that we will be exhibiting at the conference for the first time, showcasing fmSMS v3, our 2 way SMS solution for FileMaker Pro.

 

banner__adhorz-copy

We’ve completely rewritten fmSMS from the ground up for FileMaker Pro v12, with a number of new features including support for FileMaker Go. If you’re coming to the conference please stop by and say hello and we can demonstrate how you can send and receive SMS/TXT messages using FileMaker Pro.

We hope to see as many of you there as possible and look forward to answering your questions about FileMaker and SMS integration.

fmSMS v3 Coming Soon

/in , , , , , /by

We’re putting together the final touches to v3 of fmSMS, our two way SMS FileMaker solution that allows you to send and receive SMS/txt messages from your FileMaker database. With the release of FileMaker Pro v12 we decided to take this opportunity to rewrite fmSMS from the ground up, which has meant the v3 release has taken us a bit longer than we expected. Version 1 of fmSMS was originally created with FileMaker Pro v6, and v2 was developed with FileMaker Pro 9, 10 and 11. With the new v12 file format, plugin installation updates and the ExecuteSQL function we decided that it was a good time to go back to the drawing board and start afresh.

We’re also changing the plug-in we use with fmSMS v3 to the free BaseElements plug-in. The BaseElements plug-in already had a number of HTTP functions which we were able to implement quickly, however it was lacking a function that a number of our customers rely: proxy support. We decided to sponsor the development of this function which was included in the recent v2 release of the BaseElements plugin. The function is called as follows:

BE_HTTP_Set_Proxy ( proxy {; port ; username ; password } )

You can supply the proxy server address, along with the three optional parameters for the port number, proxy server username and proxy server password.

We’ve also reduced the number of files down from 3 to a single file. Using the BaseElements plug-in also allows us to remove any licensing restrictions and to include support for FileMaker Server scripts as standard (previously this required customers to purchase an additional plug-in license). We’ve also included support for additional SMS Gateways around the world, including the USA.

We hope to have the new version ready in a few weeks.

FileMaker API for PHP Valuelists and FileMaker Server v12

/in , , , , /by

When working with the FileMaker API for PHP there are a number of functions that are helpful when working with Value Lists. You can use the listValueLists function  to return the names of any value lists associated with the  layout you are referencing, and the getValueLists function to return a multi-level associative array of value lists (i.e. the contents of the value lists on the layout).

I noticed something strange today when working on a FileMaker v12 file hosted with FileMaker Server v12 – using these 2 functions I was seeing value lists that I was sure were not on the layout. After a bit of testing I found the source of the problem – some of the fields which were set to display as a simple “Edit box” had previously been set to display as a “Drop-down list”, and it was the value lists that were previously associated with these fields that were showing. Simply changing the field’s control style from “Drop-down list” to “Edit box” was not enough to remove it from the XML that is returned by the Web Publishing Engine – if you’ve used FileMaker Pro for a while you’ll have encountered this feature where FileMaker can “remember” previous settings for a field on a layout and when you switch it back from “Edit box” to  “Drop-down list” it will remember the value list you had previously selected.

The only way to completely refresh the layout so that “remembered” value lists were not returned by the listValueLists and getValueLists functions was to remove the fields from the layout and manually add them again, ensuring that the control style was set to “Edit box” to start with (this is the default style when adding new fields to a layout).

I quickly checked a v11 solution running under FileMaker Server v11 and I wasn’t able to reproduce this issue, so it looks like a new issue with FileMaker Server v12 only. I’ll report it to FileMaker Inc in case it’s considered a bug, but it’s definitely a change from previous versions of FileMaker Server.

Databuzz now FileMaker 12 Certified

/in , , , /by

Databuzz is pleased to announce that Andrew Duncan recently passed the FileMaker 12 Certification Exam and is now FileMaker Certified in v8, 9, 10, 11 and 12. FileMaker Certification is your validation that you are hiring an experienced FileMaker professional who has technical knowledge of the complete FileMaker product line and has passed the “Developer Essentials for FileMaker” certification exam.

 

FileMaker 12 Certified Developer

Using ExecuteSQL to Query the Virtual Schema/System Tables

/12 Comments/in , , /by

Update for Claris FileMaker Pro 19.4.1 – a new faster query to return base tables is now available in Claris FileMaker Pro 19.4.1 or later. Details can be found in this article.

Update for Claris FileMaker Pro 20 (2023) – FileMaker Pro v20 includes new functions that return information about the base tables instead of all table occurrences making it much easier get a list of base tables. Details can be found in this article.

FileMaker Pro v12 introduced the new ExecuteSQL function, which allows you to perform an SQL query on your FileMaker database. The query can can combine the results of two queries, include dynamic parameters, and lets you specify relationships independent of the relationships graph. It is presently limited to the SELECT statement, which is similar to doing a find using the native FileMaker Pro commands, but is not tied to a particular layout or relationship.

I’m going to discuss one particular use of the ExecuteSQL function in this post: the ability to query 2 “virtual tables” for information about the schema of your FileMaker Pro v12 files. The ability to execute SQL statements has been available via the external plug-in API since FileMaker Pro v7.0v3 was released, and many plug-ins have included this functionality, including MMQuery and the BaseElements plugins. Plug-ins that utilised the SQL functionality have also had the ability to query 2 hidden “virtual tables” for information about the schema of your FileMaker database, including Table Occurrences and Field Names. Now that we have the ability to perform native SQL queries using the ExecuteSQL function in FileMaker Pro v12 we can also query these “virtual tables”.

The 2 “virtual tables” that you can query are FileMaker_Tables and FileMaker_Fields.

FileMaker_Tables returns the following columns:

1. TableName
2. TableID
3. BaseTableName
4. BaseFileName
5. ModCount

Here’s some ExecuteSQL function examples that illustrate how you can query FileMaker_Tables (note that the table occurrences also work with external file references that appear on the graph of the current file. I’m using the Event Management starter solution from FileMaker Pro v12 to illustrate what data is returned as well):

ExecuteSQL ( “SELECT * FROM FileMaker_Tables” ; “” ; “”  )
// Returns TableName, TableID, BaseTableName, BaseFileName, and ModCount  for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda,1065093,Agenda,Event Management,46
Assignees | Contributors,1065094,Contributors,Event Management,115
Contacts,1065098,Contacts,Event Management,140
Contributors,1065097,Contributors,Event Management,115
Events,1065089,Events,Event Management,137
Guests,1065092,Guests,Event Management,62
Guests | Contacts,1065095,Contacts,Event Management,140
Tasks,1065091,Tasks,Event Management,89
Venue Looked Up,1065107,Events,Event Management,137

ExecuteSQL ( “SELECT BaseTableName FROM FileMaker_Tables” ; “” ; “”  )
// Returns list of all underlying Base Tables for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda
Contributors
Contacts
Contributors
Events
Guests
Contacts
Tasks
Events

ExecuteSQL ( “SELECT DISTINCT BaseTableName FROM FileMaker_Tables” ; “” ; “”  )
// Returns a unique list of all underlying Base Tables for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda
Contacts
Contributors
Events
Guests
Tasks

FileMaker_Fields returns the following columns:

1. TableName
2. FieldName
3. FieldType (the SQL data type, not the FileMaker data type)
4. FieldID
5. FieldClass (Normal, Summary, Calculated)
6. FieldReps
7. ModCount

Here’s some ExecuteSQL function examples that illustrate how you can query FileMaker_Fields (once again using the Event Management starter solution from FileMaker Pro v12 to illustrate what data is returned):

ExecuteSQL ( “SELECT * FROM FileMaker_Fields” ; “” ; “”  )
// Returns TableName, FieldName, FieldType, FieldID, FieldClass, FieldReps and ModCount  for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda,EVENT ID MATCH FIELD,decimal,2,Normal,1,4
Agenda,Agenda Item,varchar,3,Normal,1,2
Agenda,Date,date,4,Normal,1,9
Agenda,Explanation,varchar,5,Normal,1,5
Agenda,Time,time,6,Normal,1,7
Agenda,Time Frame Order,decimal,8,Calculated,1,16
Agenda,Time Frame,varchar,9,Calculated,1,12
Assignees | Contributors,EVENT ID MATCH FIELD,decimal,1,Normal,1,5
Assignees | Contributors,Email,varchar,2,Normal,1,19
Assignees | Contributors,Phone,varchar,3,Normal,1,10
Assignees | Contributors,Contributor | MATCH FIELD,varchar,4,Normal,1,26
Assignees | Contributors,Role,varchar,5,Normal,1,1
Assignees | Contributors,Assignment,varchar,13,Calculated,1,19
Assignees | Contributors,Photo | Container,binary,14,Normal,1,5
Assignees | Contributors,Assignment Status,decimal,16,Calculated,1,3
Assignees | Contributors,Photo Placeholder,varchar,18,Calculated,1,3
Contacts,First,varchar,2,Normal,1,2
Contacts,Last,varchar,3,Normal,1,2
Contacts,Contacts | MATCH FIELD,varchar,4,Calculated,1,26
Contacts,Job Title,varchar,5,Normal,1,2
Contacts,Company,varchar,6,Normal,1,5
Contacts,Work Email,varchar,7,Normal,1,4
Contacts,Work Phone,varchar,8,Normal,1,2
Contacts,Mobile Phone,varchar,9,Normal,1,2
Contacts,Fax,varchar,10,Normal,1,1
Contacts,Website,varchar,11,Normal,1,1
Contacts,Address 1,varchar,12,Normal,1,3
Contacts,City,varchar,13,Normal,1,3
Contacts,State,varchar,14,Normal,1,3
Contacts,Postal Code,varchar,15,Normal,1,3
Contacts,Country,varchar,16,Normal,1,3
Contacts,Address 2,varchar,17,Normal,1,4
Contacts,CONTACT ID MATCH FIELD,decimal,33,Normal,1,7
Contacts,Initial,varchar,34,Calculated,1,32
Contacts,Sort Selection,global varchar,39,Normal,1,4
Contacts,Sort List Key,varchar,40,Calculated,1,4
Contacts,Photo | Container,binary,41,Normal,1,4
Contacts,Title,varchar,42,Normal,1,1
Contacts,Home Phone,varchar,43,Normal,1,1
Contacts,Home Email,varchar,44,Normal,1,5
Contacts,Notes,varchar,46,Normal,1,2
Contacts,Address Short,varchar,48,Calculated,1,8
Contacts,File Name Placeholder,varchar,49,Calculated,1,4
Contacts,Result Label Plural,varchar,50,Calculated,1,2
Contributors,EVENT ID MATCH FIELD,decimal,1,Normal,1,5
Contributors,Email,varchar,2,Normal,1,19
Contributors,Phone,varchar,3,Normal,1,10
Contributors,Contributor | MATCH FIELD,varchar,4,Normal,1,26
Contributors,Role,varchar,5,Normal,1,1
Contributors,Assignment,varchar,13,Calculated,1,19
Contributors,Photo | Container,binary,14,Normal,1,5
Contributors,Assignment Status,decimal,16,Calculated,1,3
Contributors,Photo Placeholder,varchar,18,Calculated,1,3
Events,EVENT ID MATCH FIELD,decimal,1,Normal,1,11
Events,Event,varchar,2,Normal,1,10
Events,Date,date,3,Normal,1,2
etc . . .

ExecuteSQL ( “SELECT * FROM FileMaker_Fields WHERE TableName=’Tasks'” ; “” ; “”  )
// Returns TableName, FieldName, FieldType, FieldID, FieldClass, FieldReps and ModCount  for the “Tasks” table occurrence in the current FileMaker Pro v12 file

returns:

Tasks,EVENT ID MATCH FIELD,decimal,2,Normal,1,5
Tasks,Due Date,date,5,Normal,1,3
Tasks,Task,varchar,6,Normal,1,11
Tasks,Status,decimal,7,Calculated,1,21
Tasks,Assignee | MATCH FIELD,varchar,11,Normal,1,4
Tasks,Status Sort Order,decimal,12,Calculated,1,32
Tasks,Completed,varchar,14,Normal,1,2
Tasks,To Do,decimal,17,Calculated,1,5
Tasks,Overdue,decimal,18,Calculated,1,6
Tasks,Event,varchar,20,Calculated,1,1

ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName=’Tasks'” ; “” ; “”  )
// Returns a list of FieldNames   for the “Tasks” table occurrence in the current FileMaker Pro v12 file.The list of field names is in field creation order

returns:

EVENT ID MATCH FIELD
Due Date
Task
Status
Assignee | MATCH FIELD
Status Sort Order
Completed
To Do
Overdue
Event

ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName=’Tasks’ ORDER BY FieldName” ; “” ; “”  )
// Returns a sorted list of FieldNames for the “Tasks” table occurrence in the current FileMaker Pro v12 file

returns:

Assignee | MATCH FIELD
Completed
Due Date
EVENT ID MATCH FIELD
Event
Overdue
Status
Status Sort Order
Task
To Do

ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName='” & Get ( LayoutTableName ) & “‘ ORDER BY FieldName” ; “” ; “”  )
// Returns a sorted list of FieldNames for the table occurrence of the current layout in the current FileMaker Pro v12 file. I’m on the “Events” layout in this example

returns:

Address
Agenda Item Label Plural
Agenda Item Number | All
Agenda Item Number | Current
City
Contributor Number | All
Contributor Number | Assigned
Country
Date
Description
EVENT ID MATCH FIELD
Email
Event
Guest Label Plural
Guest Number | All
Guest Number | Attending
Layout Selector
Notes
Phone
Postal Code
QuickFind | iOS
Result Label Plural
Sort List Key
Sort Selection
State
Task Label Plural
Task Number | All
Task Number | To Do
Time Frame
Time Frame | Sort Order
Type
Venue

There’s very little official documentation about these virtual tables that I could find – if I’ve made any errors please let me know and I’ll update this post. I’ve created a couple of custom functions for 2 of the queries that I use the most:

ListBaseTables
ListFieldsFromTable