How-To: Extend gFM-Business ERP software with FileMaker

One of the great advantages of gFM Business ERP software is the FileMaker basis. With FileMaker Pro, you as a user can customize and/or extend all print layouts in all versions according to your wishes. In addition, all data tables are enabled for access to external FileMaker databases in all versions. You can also use FileMaker Pro to develop your own FileMaker solutions that modify or add data from gFM Business. In the following article, you will learn how to create a small FileMaker solution to import items from a material list as an Excel file.Table from the software 3CAD evolution as items in a quotation from gFM-Business. In principle, such solutions can be created in all areas of gFM-Business because all data tables are released for external access. The solution presented in this article can also be purchased as a ready-made module that is open for customization with FileMaker Pro.

Create a new FileMaker database

In our example, we would like to select article items from a product list from 3CAD evolution as quotation items into an existing quotation. To do this, we first need a new FileMakerDatabaseinto which we can import the data from the Excel tables. To do this, open FileMaker Pro and select the item [File > New solution...].

In the following file selection dialog, first select the folder in which the databases from gFM-Business are located (gFM_Business.fmp12 or gFM_Business.gfm). In the field [Save as] a new name for your FileMaker solution, in our case we enter "3CAD-FileMaker.fmp12".

Import Excel table as new FileMaker table

Once the new FileMaker database has been created on the data carrier, the second step is to import the Excel table that will later be entered as items in the quotation. To do this, select from the menu bar the item [File > Import records > File...] from.

In the file selection dialog, select under [Show] Select the "Excel workbooks (xlsx)" item and then your Excel spreadsheet that was exported by the "3CAD-Evolution" software. After selecting the file, select the workbook in which the data was saved within the Excel spreadsheet in the following dialog, whereupon a dialog for assigning the import fields appears:

Activate the option [Do not import first data record]. Select at the top right under [Target] the option [New table] from. Then click on the button [Import]to import the Excel table into your solution as a new FileMaker table. A new table is now automatically created in FileMaker with the field names that were specified as field names in the Excel table. All data in the Excel table is also imported into the new table. FileMaker also creates two layouts for the new table, which contain the fields of the table. These layouts will be edited later in this article.

Create external data sources for gFM-Business

Next, we integrate the databases of the required tables from gFM-Business into our new FileMaker solution. To do this, select the menu bar item [File > Manage > External data sources] from. Click on the button [New...]to create a new external data source.

Click on the button [Add file] and select in your gFM Business directory the file [gFMw_Article.fmp12] resp. [...gfm] button. Confirm the dialog with the button [OK]. Repeat this process for the following files:

• gFMw_Faktura.fmp12 (or .gfm)
• gFMw_Contacts.fmp12 (or .gfm)
• gFMw_Stammdaten.fmp12 (or .gfm)

At the end, the dialog of the external data sources should look like this:

Create offer ID and global key

Now we need a field in which the offer ID of the offer into which the items from the material list are to be imported can be entered later. This field must be saved globally so that the value is the same for all data records called up. To create the new fields in FileMaker, select the item [File > Manage > Database] tab. Select the middle tab [Fields] and enter under [Field name] to enter the name of the new field: [_fk_Angebot_ID]. Click on the button [Create] and then click on the button [Options]. Select the tab [Memory], activate the "Use global storage" option there and confirm the dialog.

To create the global key, enter the following in the [Field name] the name [_sGlobal] and select the type [Formula]. Click on the button [Create] and enter only a 1 in the following dialog. After confirming the dialog, your field management window should look like this:

Create relationships between data tables

Now we need some relationships so that we can assign the data correctly within gFM-Business. The first relationship points to the table [Article master] from gFM Business, which is colored green in the relationship graph. In this example, we have colored our import table light red.

Create relationship to the article master

About the new relationship [Article master] click on the [+]-icon at the bottom left of the window. Under Data source, first select the file [gFMw_Article] and then the table [Article master]. Confirm the dialog with OK. The field to which you link the relationship in the item master depends on the field in which you enter the supplied number in the Article management save. In our case, the Excel file provides the article number of the supplier, which is maintained in the "Article_No_Supplier" field in our example. In this case, click with the mouse on the "ART. NR." field in the "CAD_Evolution" table and, holding down the mouse button, drag the mouse to the "Article_No_Supplier" field in the "Article master" table. You have now created the first relationship.

Create global relationships

The relationships "glo_Offers" and "glo_Master_Data" are defined via the global key [_sGlobal] linked. Create these two relationships according to the above pattern and link the tables as shown in the image above (both pages each with the global key). Select the "Offer items" table of the "gFMw_Faktura" data source and the "Master data" table of the "gFMw_Stammdaten" data source as the tables.

Create relationship to the offer items

Next, we need a relationship to the quotation items. As the quotation items in gFM Business do not have a global key, link the import table to the field [_sGlobal] and the offer items with [_Offer_ID]. Then double-click the connecting line and select "x" as the connection type (pop-up field between the tables).

Relationships for data to be transferred from gFM-Business

Finally, we need the relationships "Offers" and "Offers_Customers". To do this, first create the "Offers" table as described above and link them using the [_pk_Offer_ID] with the field [_Offer_ID] of the offer items. Then create the "Quotations_Customers" table (table "Customers" from the data source "gFMw_Customers") and link the field [_pk_Customer_ID] with the field [_Customer_ID] from the "Offers" table.

Create user interface and system layouts

We need three layouts for our small import solution:

• The user interface on which the import will subsequently be carried out
• A system layout of the "CAD_Evolution" table
• A system layout of the "Offer items" table

For the user interface, we can use the first layout that FileMaker has already created automatically. Switch to the Layout mode under [View > Layout mode]. In the menu bar, select the item [Layouts > Layout setting] from. Rename the layout to "scr.data transfer" and select "CAD_Evolution" under "Show data sets from". Now place the global field [_fk_Angebot_ID] from the "CAD_Evolution" table on the layout and a button for your execution script, which you label with "Data import", for example.

Create system layouts for export data and quotation items

So that our Script can access data from the export data table and the quotation items table in the conventional way, we need two system layouts. In layout mode, switch to the second layout created by FileMaker and rename it to [sys.CAD_Evolution].

For the second system layout, select the function [Layouts > New layout/New report] from. Enter the following as the layout name [sys.offer items] under "Show records from", select the "Quotation items" table, select "Computer" and "Form" and confirm the dialog. Insert all fields from the "Quotation items" table into the new layout.

Script for importing data from 3CAD-Evolution into gFM-Business

Now we need to program the script that imports the data from the exported table into gFM-Business as quotation items. Please note that gFM-Business does not automatically enter any data when importing from external solutions. All values to be imported must therefore be transferred. The script consists of the following sub-scripts:

• Check for empty offer ID - Checks whether a value has been entered in the offer ID. Abort if no value is present. In the first line, we switch on error recording because possible errors are intercepted directly in the script.

• Check for incorrect offer ID - checks whether the offer ID exists in gFM-Business. If the entered offer ID does not exist in gFM-Business, the script is canceled.

• Check for existing positions - checks whether items already exist in the offer. If yes, a query appears asking whether you still want to import into this offer. The existing items are of course retained.

• Import dialog and data import - Query whether import is to be carried out and routine for data import. First, a dialog is displayed stating that the file to be imported should be selected in the next dialog. A file selection dialog then appears, which was developed with MBS functions to read out the native path of the file. This path is read with the function [PathPluginToFM] into a FileMaker-compliant path. This function is a set of custom functions that you can import from gFM-Business. To do this, select the function [File > Manage > Own functions]. Click on the button below [Import]. Select your gFM Business database and then select the following functions: PathFMToPlugin, PathPluginToFM, Platform, PlatformIsMac, PlatformIsWin, TrimStart and LeftMatch. Confirm the dialog to start the import. [Import data records] first select one of your export files from 3CAD-Evolution. Then define the field sequence for the import in the command and select the following as the target table [CAD_Evolution] from. After defining the field sequence, click on the command again, select [File]delete the name of your local file stored there and instead enter the name defined in the upper part of the script Variable $path_fm.

  After the start of the execution loop, the table [CAD_Evolution] sets all required variables that are written to the table [Offer items] can be written. Depending on which data the exported Excel table contains and is to be transferred, the variable definitions and field value substitutions can be extended as required.

Assign execution script to button

The last step is to press the [Data import] on the first layout [Data transfer] with the new script "sf.data import". First switch to the first layout "scr.data import" in layout mode, click the [Data import] with the right mouse button and select the function [Key setting]. In the "Action" area, select the first Pop-up menu the function [Execute script]. Select the "sf.data import" script in the following dialog.

The button [Data import] is now filled with the import script, and your small FileMaker solution for importing a material list from 3CAD-Evolution as quotation items in gFM-Business is ready.

Import of line items from other third-party solutions

In this article we have shown you how you can use a simple FileMaker solution to automatically import quotation items from exported material lists from 3CAD-Evolution into gFM-Business. In principle, this procedure can also be used for lists from other programs if it is adapted accordingly. Import processes into other data tables such as customers or articles are of course also conceivable.