Skip links

Import articles from xt:Commerce into wpShopGermany with FileMaker.

FileMaker training, courses, coaching

Use FileMaker for the import of articles from xt:Commerce to wpShopGermanyMany operators of online stores today also maintain blogs, which are often based on the popular WordPress blog system, in addition to the pure online store. A blog is usually used to generate new visitors who find the blog from search engines via the blog's mostly up-to-date content. Ultimately, however, the store operator not only wants to generate new streams of visitors, but above all new sales. The traditional way is to link content or articles from the online store on the blog in the articles or via classic banner advertising. Another new approach is to set up an online store directly on the blog, where visitors to the blog can buy products directly without having to leave the blog. With wpShopGermany is an affordable online store plugin for WordPress that is available in German and meets the requirements of the German market. The plugin is available from just 18.95 euros in a light version and for 99.99 euros as an enterprise version for any number of WordPress blogs. This article explains how the products from the online store, in this case xt:Commerce, find their way into the WordPress store system. As Interface a FileMakerDatabase used.

Set up ODBC data source to the MySQL database of wpShopGermany

In the article FileMaker and MySQL using the example of xt:Commerce we have already explained how an interface to the xt:Commerce online shop system can be integrated into a FileMaker database. For this purpose, a ODBC-A data source for the MySQL database of xt:Commerce has been set up, via which full access to all data tables of the online store is available. This article assumes that a database exists that contains the tables and data content of the xt:Commerce database. How the internal link and the relationship graph of such a FileMaker database can look was described in this article on xtc:load described. This is what a FileMaker solution with integration of articles from xt:Commerce could look like:

FileMaker database with articles from xt:Commerce

To extend a FileMaker database with an interface to the WordPress store system wpShopGermany, an additional ODBC data source is required for the WordPress MySQL database. This is set up in exactly the same way as described in the first article. After setting up the ODBC data source, you have full access to the data tables of WordPress and the wpShopGermany plugin. This article only deals with the export of pure product data without product variants or attributes. All dynamic data from wpShopGermany is stored in the following data tables:

  • wp_wpshopgermany_attributes
  • wp_wpshopgermany_cab
  • wp_wpshopgermany_downloadplus
  • wp_wpshopgermany_vouchers
  • wp_wpshopgermany_customers
  • wp_wpshopgermany_land
  • wp_wpshopgermany_suppliers
  • wp_wpshopgermany_mwst
  • wp_wpshopgermany_order
  • wp_wpshopgermany_order_products
  • wp_wpshopgermany_orderlog
  • wp_wpshopgermany_ordervars
  • wp_wpshopgermany_products
  • wp_wpshopgermany_products_vari
  • wp_wpshopgermany_productgroups
  • wp_wpshopgermany_produktvars
  • wp_wpshopgermany_invoices
  • wp_wpshopgermany_shipping_zones
  • wp_wpshopgermany_number_variations

Table wp_wpshopgermany_products and product images

To import the pure article data into wpShopGermany, we only need the Table

wp_wpshopgermany_products.

This table contains all the master data for a store item. Assigned article images are located at file level in the WordPress directory

../wp-content/uploads/product-images.

In this directory, there is a directory with the name of the respective article ID for each article that contains a product image. This directory then contains the product images assigned to the item.

Preparation of the interface from FileMaker to wpShopGermany

In order to be able to easily export the article data from FileMaker to the MySQL database of wpShopGermany, additional specific fields for wpShopGermany are required in the FileMaker database, which previously only contained the fields from xt:Commerce. To do this, open the database administration of the field definition and add the following fields:

  • wpShopGermany_Product type (Text)
  • wpShopGermany_Template (Text)
  • wpShopGermany_Category (number)

The first field must contain the value "normalprodukt" for a standard store on WordPress, the template field contains the value "standard.phtml" by default. The category field contains the category assigned in wpShopGermany, which can be set up in the WordPress store system under "Product groups".

Set up product groups in wpShopGermany

Interface part 1: Export of article data from FileMaker to wpShopGermany

Once the above three fields have been created in the FileMaker database containing the articles from xt:Commerce, we can now start exporting the products to the wpShopGermany database. The table wp_wpshopgermany_products consists of the following fields:

  • id (number, unique ID of the product)
  • cdate (timestamp, creation of the product)
  • name (Text, name of the product)
  • anr (Text, article number (MPN) of the product)
  • type (text, product type)
  • particles (not used in this interface)
  • price (number, price of the product, point as decimal separator)
  • mwst (VAT ID, can be configured in wpShopAdmin, default = 1)
  • fineness (not used in this interface)
  • quantity (number, quantity, usually contains 0.00)
  • Description (Text, article description, may contain HTML)
  • pgroup (number, product group)
  • ptemplate_file (Text, template file)
  • weight (number, contains the weight of the item, point as decimal separator)
  • stock (Number, contains the stock, is transferred with 0 in this interface)
  • stock_count (number, is transferred with 0 in this interface)
  • productvars (not used in this interface)
  • deleted (number, deletion indicator, 0 = active, 1 = deleted)
  • downloadplus (number, is transferred with 0 in this interface)
  • variantsplus (not used in this interface)
  • lang_parent (number, is transferred with 0 in this interface)
  • lang_locale (not used in this interface)

In order to write the data from the FileMaker database directly into the MySQL database of wpShopGermany, we now create a Script in ScriptMaker, which contains a corresponding SQL-statement to write the articles from the FileMaker database to the MySQL database of xpShopGermany. This is done with the following command:

Execute SQL Query [Without dialog box; DSN: ODBC data source; Calculated SQL text:
 "INSERT INTO
`wp_wpshopgermany_products` (`id`, `cdate`, `name`,`anr`, `type`, `price`, `mwst`, `fquantity`,
`description`,`pgroup`, `ptemplate_file`, `weight`,`stock`, `stock_count`, `deleted`,
`downloadplus`, `lang_parent`)
VALUES ('" &
xtc_products::products_id & "', '" &
ReadAsText(CharacterRight(Hole(SystemDate); 4) & "-" & CharacterCenter(Hole(SystemDate); 4; 2) &
"-" & CharacterLeft(Hole(SystemDate); 2) & " " & Hole(SystemTime)) & "', '" &
Exchange(xtc_products::products_name; """; """) & "', '" &
xtc_products::products_model & "', '" &
xtc_products::wpShopGermany_product_type & "', '" &
If(
  SampleNumber ( ReadAsText(xtc_products::xCal_Price_Gross) ; "," ) = 0;
ReadAsText(xtc_products::xCal_Price_Gross) & ".00";
  ZeichenMitte(xtc_products::xCal_Preis_brutto; Position(xtc_products::xCal_Preis_brutto; "," ; 1 ; 1 ) ; 1) = ",";
ReadAsText(Exchange(xtc_products::xCal_Price_Gross; ","; ".")) & "0";
ReadAsText(Exchange(xtc_products::xCal_Price_Gross; ","; ".")))) & "', " &
"1" & ", " &
"'0.00" & "', '" &
Exchange(xtc_products_description::products_short_description; """; """) & "', " &
xtc_products::wpShopGermany_Category & ", '" &
xtc_products::wpShopGermany_Template & "', '" &
"0.00'" & ", " &
"0" & ", " &
"0" & ", " &
"0" & ", " &
"0" & ", " &
"0)"]

This SQL statement assumes that the three additional fields mentioned above have been created and that the field names of the store items correspond to those in xt:commerce.

Attention! Please note that the fields for the article name and the article description may also contain quotation marks or apostrophes. To ensure the function of the SQL statement, which also contains apostrophes for its function, you should change these characters for use in the SQL statement to HTML entities convert.

Since the item price in xt:Commerce depends on whether a special price exists for the respective item, it was calculated in the formula <>:

If(IstLeer(xtc_specials::specials_new_products_price);
  Exchange(products_price; "."; ",");
(Exchange(xtc_specials::specials_new_products_price; "."; ",")) * 1,19
)

Part 2: Exporting article images from xt:Commerce for wpShopGermany

As already mentioned, the images assigned to an article in wpShopGermany are each located in a directory with the same name as the article ID. Since FileMaker can neither transfer images from a URL to a media field nor create directories with on-board tools, we need the URL plugin and the file plugin from Troi. Both plugins can be downloaded free of charge from the manufacturer's website, fully functional but with a time limit. Troi offers many licensing options for FileMaker plugins.

To create the required directory with the name of the article ID, we use the "TrFile_CreateFolder" function of the Troi File plugin, after first entering the full path of the directory in a Variable have written. Both functions then look like this:

Set variable [$FM_Pfad; value: "filemac:/PfadAufIhremRechner/" & xtc_products::Product_ID]
Set variable [$Filename; value: $FM_Path & "/" & xtc_products::products_ID & ".jpg"]

In xt:Commerce, the file name of the image associated with the article is saved in the <> field. The entire URL for calling up the product image is in FileMaker:

"http://IhrShop.de/images/product_images/info_images/" & xtc_products::products_image.

Next, we create a container field with the name <> in the FileMaker database. Using the "TURL_Get" function, the image can now be transferred from the web server to the created container field:

Set field value [xtc_products::Image container; If(xtc_products::products_image  ""; TURL_Get("";
"http://IhrShop.de/images/product_images/info_images/" & xtc_products::products_image ); "")

After this function in the script, the article image of the xt:Commerce article is located in the FileMaker media field with the name "Image container". From this field, the image can now be imported with the function

Export all field values [xtc_products::Image container; "$Filename"]

can be exported to any directory. All created directories with the images can then simply be exported via FTP to the WordPress directory

../wp-content/uploads/product-images.

be transferred. With an FTP plug-in, this step can of course also be automated directly in FileMaker.

Overview of the complete FileMaker script

With the measures mentioned, we are now able to export a single article including product photo from xt:Commerce to wpShopGermany. In order to be able to export all called articles in one go, a loop can now simply be implemented in the script around the previously programmed functions. The complete FileMaker script would then look like this, for example:

Complete FileMaker script: Export to wpShopGermany

With this script, it is possible to export all called data records in the database with the articles from xt:Commerce to the WordPress store system wpShopGermany in one go, including a query to the user as to whether he really wants to perform this action. A complete solution is expressly not provided here because the development process for this interface is very much dependent on the individual properties of the FileMaker database used. If you have other dynamic data in your database, such as different VAT rates, item weight or stock levels, you can also export this data to the WordPress store system wpShopGermany with simple modifications.

Leave a comment

  1. That's a great thing! I even got everything running with the Filemaker demo! Now it's time to buy Filemaker :-)

  2. Ouch, that's a long manual.
    I myself am very enthusiastic about wpShopGermany, except for a few small things, this is the perfect store plugin for small niche stores.
    The Filemaker thing is completely new to me, but it certainly looks like something you should test.

  3. Interesting approach. The instructions work perfectly, and can certainly also be applied to other systems. I haven't looked at wpShopGermany for a while - is the plugin now really legally compliant, or does it still contain peculiarities such as the one-page checkout?

  4. My next or first store will certainly also run on WordPress... just like any other project ;) However, I'm not sure whether you can or should really migrate so easily from XT to WordPress (URL structure, interfaces...)

  5. Unfortunately, what I don't really know is whether there is an interface to a control room management program such as Orgamax?

    1. As long as there are technical possibilities for FileMaker to access the corresponding data tables, interfaces to all systems are possible in principle. However, some systems, such as Magento, do not allow direct access to the tables, but provide an API which can then also be used for data access in most cases.

Share this page:

ERP software as flexible as your company.
We will be happy to advise you.

Customizable ERP software for Mac, Windows and iOS.

You are here: Import articles with FileMaker from xt:Commerce into wpShopGermany (Wordpress).