Expand online store interface in the gFM Business ERP software

Based on the FileMaker platform, the ERP software gFM-Business, which runs on Apple macOS, Windows and iOS, contains an integrated online store function.Interface based on REST-API, with which customers, articles and store orders can be transferred to gFM-Business at the push of a button. gFM-Business also supports the automatic comparison of stock levels with the articles in the online store. The standard scope currently includes the store systems Shopware 5.x, Gambio GX3 and WooCommerce for WordPress supported.

As part of an open license from gFM-Business Basic or Professional the online store interface with FileMaker Pro (or -Advanced) can also be expanded to include additional fields or an additional online store system. In this article, we explain how the REST API-based online store interface in gFM Business is structured and how it can be expanded to include additional fields or another store system. A documentation of all basic definitions and standards in gFM-Business can be found at here.

General structure of the integrated online store interface

The online store interface integrated in the gFM-Business ERP software has a simple structure and consists of the following components:

Online stores in the "Settings" module

All online stores can be set up under [Settings > Online store]. The underlying Table is the "REST_API" table from the "gFMw_Stammdaten" file. If a new online store is created, all data is obtained by reference from the "REST_API_Stamm" table, which is also located in the "gFMw_Stammdaten" file. If another store system is added to the interface, a master data record for this store system should also be created in the "REST_API_Stamm" table so that you do not have to enter all the data manually each time you use several online stores.

The small version gFM-Business Basic only supports the use of one online store by default. In principle, this can also be extended so that gFM-Business Basic also allows the use of several online stores. For this, only a corresponding Portal for selecting the online store can be implemented as in the Professional version. All functions and scripts also support several online stores in the Basic version and do not need to be extended for this function.

Virtual tables

When selecting customers, articles or store orders to be imported, all data available on the store server is displayed in a selection window. The data displayed here is only stored temporarily in a virtual table in gFM Business and is discarded after the selection. The concept of virtual tables was presented at the FileMaker Conference 2015 by Arnold Kegebein presented. In a virtual table, incoming data is not stored in individual fields, but only in a repeating field. The number of repetitions corresponds to the number of fields to be displayed in a table, one data record corresponds to one data record of the supplied data. All virtual tables are located in the file "gFM_Business.fmp12".

A virtual table contains the following fields (using articles as an example):

• _fk_Article_ID (Article ID from gFM-Business with SQL-query on "_Shop_ID" in the article master. Filled if article already exists in gFM-Business)
• _id_foreign_system (ID of the external system)
• _sGlobal (Global key, always contains the value 1)
• Selection (Field for user selection of a data record that is to be transferred to gFM-Business)
• Data fields (Repeating field with 6 repetitions for 6 data fields from the interface to be displayed in the table)
• ID_selection (Calculated field, which ID was selected)
• Header fields  (Repeating field with 6 repetitions for 6 field titles from the interface to be displayed in the table)
• Object_selection (Calculated field, which position was last selected in the result set)

The other two virtual tables work in the same way in principle, with the difference that in the table for orders the invoice and order IDs are checked for existing data records rather than the item ID, and for customers the customer ID.

There are three virtual tables for the online store interface in the "gFM_Business.fmp12" file:

• vt_selection_article (virtual table for displaying stored article data, field [data fields] contains 6 repetitions)
• vt_selection_orders (virtual table for displaying stored article data, field [data fields] contains 9 repetitions)
• vt_selection_customers (virtual table for displaying stored article data, field [data fields] contains 6 repetitions)

Store IDs in the gFM Business tables

All relevant tables contain the following fields so that the gFM-Business ERP software can track which online store data has been assigned to the data in gFM-Business:

• _Shop_Id (contains the ID of the corresponding data record in the online store)
• _Shop_Foreign_system (contains the information to which external system the store ID belongs, as gFM-Business supports the use of several stores at the same time)

These fields are contained in the following tables from gFM-Business:

• Article master (master table of all products)
• Customers (master table of all customers)
• Orders (master table of all orders)
• Invoices (master table of all invoices)

All scripts listen to "REST"

All scripts associated with the online store interface can be found in the search field of the Script-editor by entering "rest". When delivered, the interface consists of a total of seven scripts:

REST_Query [external system|endpoint|]

This script is used to log on to the Serveron which the online store was installed and is called with the parameters "External system" and "Endpoint". Before a query is made to the server of the online store, this login script must always be executed first in order to log in to the server before the query.

Set error recording [ On ]

Set UserCancelAllow [ Off ]

# 

# >>>>>>>>>> VOICE TRANSMISSION

Set variable [ $dg_001 ; Value: SQLAusführen ( "SELECT \"Dialog_001\" FROM \"lg\" WHERE \"_fk_ScriptName\"='" & Hole(ScriptName) & "' AND \"_pk_Sprache_ID\"=" & prf::_Sprache_ID ; "" ; "" ) ] 

Set variable [ $dg_002 ; Value: SQLAusführen ( "SELECT \"Dialog_002\" FROM \"lg\" WHERE \"_fk_ScriptName\"='" & Hole(ScriptName) & "' AND \"_pk_Sprache_ID\"=" & prf::_Sprache_ID ; "" ; "" ) ] 

Set variable [ $dg_003 ; Value: SQLAusführen ( "SELECT \"Dialog_003\" FROM \"lg\" WHERE \"_fk_ScriptName\"='" & Hole(ScriptName) & "' AND \"_pk_Sprache_ID\"=" & prf::_Sprache_ID ; "" ; "" ) ] 

Sets the error recording and transfers the texts from the language table for the error dialog that is displayed if the login to the server fails.

Set variable [ $fremdsystem ; Value: StringColumns(Get(ScriptParameter); 1) ] 

Set variable [ $endpoint ; Value: StringColumns(Get(ScriptParameter); 2) ] 

Set variable [ $api_url ; Value: SQLAusführen ( "SELECT \"API_URL\" FROM \"REST_API\" WHERE \"name_foreign_system\"='" & $fremdsystem & "'" ; "" ; "" ) ] 

Set variable [ $login ; Value: SQLAusführen ("SELECT \"Consumer_Key\" FROM \"REST_API\" WHERE \"name_foreign_system\"='" & $fremdsystem & "'" ; "" ; "" ) ] 

Set variable [ $pass ; Value: SQLAusführen ( "SELECT \"Consumer_Secret\" FROM \"REST_API\" WHERE \"name_foreign_system\"='" & $fremdsystem & "'" ; "" ; "" ) ] 

Set all necessary variables. Variables for the third-party system (name of the third-party system, e.g. "WooCommerce") and the endpoint (e.g. "Endpoint for articles") are transferred as script parameters from the user dialog. The variables $api_url, $login and $pass are taken from the "REST_API" table of the online store set up in the "gFMw_Stammdaten.fmp12" file.

All other commands are used to log in to an online shop system with RESTful API and are generally the same for all systems. The following partial script largely corresponds to the supplied example of the MBS FileMaker Plugin.

dlg.REST_selection_customers (parameter)

This script displays the selection window with which the customers to be imported can be selected based on the data available in the online store. This script can be called with the following script parameters:

• Import - This parameter is used to display the selection window for importing customers from the online store into gFM Business. This partial script is generated from the popover script "ts.kon_data_import" and contains calls for the main interface script of all online stores, which are called depending on the selection.
• Select all - This parameter can be used to select all rows in the selection window.
• Call_customers - This parameter can be used to call up a customer from the selection window in the gFM Business window.

dlg.REST_selection_orders (parameter)

This script displays the selection window with which the orders to be imported can be selected based on the data available in the online store. This script can be called up with the following script parameters:

• Import_Order - This parameter is used to display the selection window for importing orders from the online store into gFM Business and then importing them as an order confirmation. This sub-script is generated from the popover script "ts.fak_data_import" and contains calls for the main interface script of all online stores, which are called depending on the selection.
• Import_Invoice - This parameter is used to display the selection window for importing orders from the online store into gFM Business and then importing them as an invoice. This sub-script is generated from the popover script "ts.fak_data_import" and contains calls for the main interface script of all online stores, which are called depending on the selection.
• Select all - This parameter can be used to select all rows in the selection window.
• Order_call - This parameter can be used to call up an order confirmation from the selection window in the gFM Business window.
• Invoice_call - This parameter can be used to call up an invoice from the selection window in the gFM Business window.

dlg.REST_selection_article (parameter)

This script displays the selection window with which the items to be imported can be selected based on the data available in the online store. This script can be called up with the following script parameters:

• Import - This parameter is used to display the selection window for importing items from the online store into gFM Business. This sub-script is generated from the popover script "ts.art_data_import" and contains calls for the main interface script of all online stores, which are called depending on the selection.
• Select all - This parameter can be used to select all rows in the selection window.
• Call_customers - This parameter can be used to call up an article from the selection window in the gFM Business window.

REST_Gambio_GX3_API_v210 (data type|external system|{object|ID export}|)

This script contains all sub-scripts for importing and exporting data in connection with Gambio GX3-based online stores. All commands in this script are controlled via the first parameter "Data type", which can have the following values:

• Customers (Import script for the automatic import of customers. With this import type, no selection window is displayed before the import, but all customers that do not yet exist are automatically imported. With this import type, there is a risk of also importing unwanted spam entries).
• Orders_Orders (Import script for the automatic import of store orders as order confirmations. With this import type, no selection window is displayed before the import, but all orders that do not yet exist are automatically imported. With this import type, there is a risk of also importing unwanted spam entries).
• Orders_Invoices (Import script for the automatic import of store orders as an invoice. With this import type, no selection window is displayed before the import, but all orders that do not yet exist are automatically imported. With this import type, there is a risk of also importing unwanted spam entries).
• Article (Import script for the automatic import of products from the online store. With this import type, no selection window is displayed before the import; instead, all items that do not yet exist are imported automatically.
• Customer (Update script to update a single customer from the online store in gFM-Business. To call this script, the [ID export] parameter must be supplied with the ID of the customer data record).
• Order_Orders (Import script to import a single order from the online store from an order. To call this script, the [ID export] parameter must be supplied with the ID of the order data record).
• Order_Invoices (Import script to import a single order from the online store from an invoice. To call this script, the [ID export] parameter must be supplied with the ID of the order data record).
• Single article (Import script to import a single article from the online store into gFM-Business. To call this script, the [ID-Export] parameter must be supplied with the ID of the item data record).
• Customers_selection (Import script for importing customer data from the online store with selection window. The functions within the selection window are controlled in the respective dialog script [dlg.REST_Auswahl_Kunden]).
• Orders_selection (Import script for importing store orders from the online store with selection window. The functions within the selection window are controlled in the respective dialog script [dlg.REST_Auswahl_Bestellungen]).
• Article_selection (Import script for importing products from the online store with selection window. The functions within the selection window are controlled in the respective dialog script [dlg.REST_Auswahl_Artikel]).
• Update_stock (This script can be used to compare the stock of an item with the stock of the online store. The stock on the server is overwritten by the stock from gFM-Business).
• Update_Customers (This script is used to update customer data in the online store. The data in the online store is overwritten with the data from gFM-Business. To call this script, the [ID-Export] parameter must be supplied with the ID of the customer data record).
• Update_Order_status (This script is used to update the order status from the online store in gFM-Business. The data in gFM-Business is overwritten with the data from the online store. To call this script, the [ID export] parameter must be supplied with the ID of the order data record).
• Update_Article (This script is used to update article data in the online store. The data in the online store is overwritten with the data from gFM-Business. To call this script, the parameter [ID-Export] must be supplied with the ID of the article data record).

REST_WooCommerce_API_v3 (data type|external system|{object|ID export}|)

This script contains all sub-scripts for importing and exporting data in connection with WooCommerce-based online stores. This script contains the same command parameters as the script for Gambio online stores described above.

REST_Shopware_5x_API (data type|external system|{object|ID export}|)

This script contains all sub-scripts for importing and exporting data in connection with Shopware-based online stores. This script contains the same command parameters as the script for Gambio online stores described above.

Extending fields for an existing online store

The "REST_Shopsystem" interface scripts contain all the sub-scripts required to import data from the online store. In each relevant sub-script, the data to be retrieved from the store is first stored in a Variable saved, e.g:

Set variable [$artikel_nr ; Value: MBS("JSON.GetStringValue"; MBS("JSON.GetObjectItem"; $objekt; "productModel" )) ]

When this variable is set, functions of the MBS plugin are used to query the [productModel] field of the online store. In this case, it is a text field, so the "JSON.GetStringValue" function is used. For number fields, either the "JSON.GetIntegerValue" or "JSON.GetDoubleValue" function is used. The data types should be strictly differentiated, otherwise the function cannot be executed. Once the variables have been set for all fields, the data stored in the variables is written to the gFM-Business tables using the MBS function "FM.InsertRecord".

To import additional fields from the online store, simply create additional variables for the desired additional fields and extend the "FM.InsertRecord" function for writing the data to the gFM Business tables accordingly.

The variables $json and $object

For all functions, the data supplied from the store is stored in full in the variable $json. This variable therefore always contains the complete JSON data stream that was delivered from the online store. Depending on the definition, the $object variable only contains part of the data supplied, for example a data record. If you want to add further tables to the REST-API interface from the online store or from store extensions, you must perform a new query with the respective endpoint to the interface within an import script. For the sake of clarity, you can also save the JSON result in a separate variable, e.g. "$json_extension_1" and "$objekt_extension_1".

Expanding the interface to include another online store

Adding a new online store that is not yet supported by gFM-Business is basically quite simple and consists of the following steps:

• Adding the new online store in gFM-Business under [Settings > Online store], taking into account the login data and endpoints for all data types
• Duplicating an existing interface scripte.g. "REST_Shopware_5x_API" and rename the new script to the name of the new store system, e.g. "REST_Magento_xy_API"
• Customize the fields in all functions in the new import script. The prerequisite is that the new online store also has a REST API. All variables that have already been discussed in the section "Extending fields for an existing online store" must be adapted. The names of the variables must not be changed. If new fields/variables are added, these must also be taken into account in the MBS functions "FM.InsertRecord" or "FM.UpdateRecord". All JSON values are read out with MBS functions in the standard version of gFM-Business. It is also possible to replace these MBS functions with FileMaker functions (e.g. JSONGetElement).
• Extending the "dlg.REST_Auswahl_xyz" dialog scripts. The import scripts for all online stores are called up from these scripts depending on the selection. If a new store system is added, these selection scripts must also be extended to include the script call for the new online store. The name of the online store set up under [Settings > Online store] should contain the text phrase that is queried in these scripts in the variable $shopsystem.

For existing systems, we recommend duplicating the productive system for the expansion of the store interface and carrying out the development on a test system first so as not to jeopardize the real database with later test runs.

Expansion of the interface by a service partner or by us

We or one of our service partners will be happy to assist you personally with the extension of the online store interface as part of an open license. We can either implement the complete desired extension for you or offer you or your developer appropriate coaching for the extension of the interface or for other questions relating to the gFM-Business ERP software. Feel free to contact us!

More information about the open license of the gFM-Business ERP software