Using Textpattern as an inventory management system

Bloke · 2025-05-12 23:36:15

You may have noticed of late I’ve been asking tonnes of pointed questions about how to do tiny little things like splitting fields and iterating and stuff like that. I suppose I ought to spill the beans.

I have a friend who runs a printer ink business and his website is, bluntly, shit. It’s WordPress for a start. It’s slow, bordering on 6 seconds to first contentful paint. Updating a product field takes the best part of three minutes to a) find the right one, b) wait for it to load up, c) find the field in the maze of fields and plugin data, d) Save. And that’s just the data. The site template is custom code and nobody can figure out how it’s put together, so making changes to the site is virtually impossible.

He has a HUGE CSV file of his 3500+ products and has a tonne more he wants to sell, but changing the file and importing into WP doesn’t seem to work any more.

So we’re taking the opportunity to start with a fresh site.

Now, while Textpattern can’t do e-commerce (yet), it can make links between articles. It is relatively easy to make/duplicate articles. It has custom fields. Images. Templates. And, crucially, pages and forms can be used to export this data in a zillion different ways.

So, rather than use WordPress to manage his product inventory and be locked into its tragic interface, I devised a Textpattern site to manage it instead. This allows him to:

Add/Edit Printers when manufacturers alter their line-up. One printer per article in the “printers” section.
- The printers are mapped in a category tree to allow navigation from Brand > Printer family > Model.
Add/Edit ink cartridges, toner, imaging units, etc. One product per article in a section that matches its type (ink, toner, drum, etc).
- Use com_article_image to drag generic product shots onto an article. Or source them from his existing website as a link.
- Use jakob’s modified glz_custom_fields to manage product attributes like colour, pack size, cartridge capacity, and manage compatibility tables.
- The compatibility table is a custom field that contains a giant list of printer article IDs. So one cartridge fits into N printers.
- A custom PHP script in glz_cf allows a filtered list of printers to be associated with each cartridge. A Javascript plugin handles turning unwieldy select lists into checkbox sets with ‘x’ buttons for removal (see screenshot below).
The front end uses the bog standard four-point-nine theme with a few tweaks:
- The default form shows a list of hyperlinked printers by splitting the compatibility custom field up via breakby and breakform (see below).
- Each printer uses a <txp:article_custom> to extract and display a list of all cartridges that fit in that printer by looking up its ID in the compatibility field (also see below).
- Each image has a CSS overlay added on the fly which contains the product SKU, the capacity, and a natty CSS colour blob depicting the colour.
I wrote a pair of import scripts in a Txp article (in a /manage section), to take his humongous CSV file, map and merge the fields into Txp’s field structure and import the data. One script was for importing printers into the category tree, the other was for importing products. It injected all the Cartridge>Printer field mappings too.
I then wrote a custom ‘Export CSV’ article in /manage that presents a little form to allow anyone to choose which records to export. Date/time range, number of records, offset, which section, etc.
- When the script runs, it grabs all the data and spits it out into a huge CSV file suitable for importing (via a WP plugin) into WordPress.
- Since WP is unable to manage image overlays (every image has to be unique and in its Media library), Textpattern generates an image on the fly by compositing the base pic with the overlay and writing it into a temporary, web-accessible file. That file URL is the one referenced in the CSV so that, during import, the pic can be automatically uploaded into the WP Media library as a dedicated image.
- During export, cross-selling information is built on the fly via a query and stuffed in the CSV so that WooCommerce can show compatible Magenta, Cyan and Yellow cartridges if viewing the Black cartridge, for example.
- The Compatibility table is imported into a WP category taxonomy so site visitors can browse their printer model to find compatible cartridges. In fact, the compatibility table is bi-directional and drives both this navigation aid, AND the cross-selling information.
Stupidly, Textpattern article IDs do not correspond with WP IDs. Despite me exporting them and telling the import plugin to use them, WordPress cannot do this and uses its internal auto_increment values. Luckily, after hunting for suitable import scripts (most of which want $$$) I found a free flexible one called “Import WP” that has a free WooCommerce extension and allows a “key field” to be specified. When I defined the (extensive) mapping configuration, I told it that our article ID is the key so that, when a CSV file is re-presented for upload, it somehow knows that it’s related to the WP article ID and performs an Update/Insert as appropriate.

The upshot of all the above is that the inventory (data) and its management are now separate from the website (display). Txp acts as an intermediary data source only. The bare front-end site allows rudimentary navigation between products, compatibilty links between articles to be checked, search and image previews per product, etc. Galleries are supported if you upload more than one pic. Edit links on each individual product page jump to the admin side to manage that article on the Write panel, for convenience.

The back-end allows product and printer articles to be managed. Links can be made between them via the custom glz_cf scripts. Articles can be updated en-masse (e.g. for altering pricing or sales) via adi_matrix. And the whole caboodle can be sliced and diced and exported in any format he desires, to upload to his WP customer-facing site, his WP trade-facing site with prices excluding tax, his HubSpot CRM to allow trusted partners to order a subset of products directly while on-site at preferential rates. And so on.

Everything is driven from Txp: management in one place, export to many systems. If I could solve the authentication issues, there’s nothing to stop a third party system from directly accessing the Txp site and scraping the data from a custom export template instead of downloading it first and uploading the CSV. That, however, carries the danger that a competitor could scrape the data off the website too. The amount of work that has gone into preparing and ultimately managing the data links is incredibly valuable, so I’m resisting that for now and have the data squirrelled away behind do-not-index rules, and we’ll live with the two-step process.

I’m simply blown away with how fast Textpattern is to manage the stock records. Even the inefficient method to display the compatibility is lightning fast:

<if::individual_article>
   <txp:variable name="prnid"><article::id /></txp:variable>
   <article::custom limit="0" label="Products"  labeltag="h4" wraptag="ul">
      <if::custom_field name="compatibility" match="any" separator="|" value='<txp:variable name="prnid" />'>
         <li><txp:permlink><txp:title /></txp:permlink></li>
      </if::custom_field>
   </article::custom>
</if::individual_article>

It returns results in under quarter of a second, from inspecting 3500 HUGE pipe-delimited fields, splitting them up and matching the printer article ID. It would probably be more efficient with an actual direct query in PHP, but this is simpler and easier to maintain.

The companion piece of code that displays the printers that are compatible with the cartridge is a lot less invasive. It simply splits the custom field by pipe and passes each value through a form to render a hyperlink:

<custom::field label="Compatible with printers:" labeltag="h4" name="compatibility" breakby="|" wraptag="ul" breakform="article_link" break="" />

and Form article_link is:

<article::custom id='<txp:yield item />'>
   <li><txp:permlink><txp:title /></txp:permlink></li>
</article::custom>

The hyperlinks are messed up because the /printers section uses breadcrumb URL mode. Not sure why the permlinks duplicate the section name in the URL, with /printers/printers/{cat}/{hierarchy}/{here}. But the links work so I’m not questioning it!

All in all, I love that Txp can help in this way as the glue that binds muiltiple sites together. It means less reliance on manually managing complex CSVs and manually taking 1000s of image product shots (which is how stock was managed up until last month) and opens up the scope for using the inventory management tool for more powerful single-point-of-access data management and export to any number of systems in future.

Plugins:

adi_matrix
bot_write_tab_customize
com_article_image
glz_custom_fields
smd_custom_scripts (specific to the site for interfacing with glz_cf and handling the JS)
smd_where_used

mistersugar · 2025-05-13 00:16:37

I stand in awe. Phenomenal detail here, representing a lot of work for you, and the great tool that is Textpattern. I came to the forum just now to share my new site (will do in separate post) and found this and I am humbled.

bici · 2025-05-13 02:32:28

Bloke wrote #339699:

You may have noticed of late I’ve been asking tonnes of pointed questions about how to do tiny little things like splitting fields and iterating and stuff like that. I suppose I ought to spill the beans.

I have a friend who runs a printer ink business and his website is, bluntly, shit. It’s WordPress for a start. It’s slow, bordering on 6 seconds to first contentful paint. Updating a product field takes the best part of three minutes to a) find the right one, b) wait for it to load up, c) find the field in the maze of fields and plugin data, d) Save. And that’s just the data. The site template is custom code and nobody can figure out how it’s put together, so making changes to the site is virtually impossible.

He has a HUGE CSV file of his 3500+ products and has a tonne more he wants to sell, but changing the file and importing into WP doesn’t seem to work any more.

So we’re taking the opportunity to start with a fresh site.

Now, while Textpattern can’t do e-commerce (yet), it can make links between articles. It is relatively easy to make/duplicate articles. It has custom fields. Images. Templates. And, crucially, pages and forms can be used to export this data in a zillion different ways.

So, rather than use WordPress to manage his product inventory and be locked into its tragic interface, I devised a Textpattern site to manage it instead. This allows him to:

Add/Edit Printers when manufacturers alter their line-up. One printer per article in the “printers” section.

The printers are mapped in a category tree to allow navigation from Brand > Printer family > Model.

Add/Edit ink cartridges, toner, imaging units, etc. One product per article in a section that matches its type (ink, toner, drum, etc).

Use com_article_image to drag generic product shots onto an article. Or source them from his existing website as a link.

Use jakob’s modified glz_custom_fields to manage product attributes like colour, pack size, cartridge capacity, and manage compatibility tables.

The compatibility table is a custom field that contains a giant list of printer article IDs. So one cartridge fits into N printers.

A custom PHP script in glz_cf allows a filtered list of printers to be associated with each cartridge. A Javascript plugin handles turning unwieldy select lists into checkbox sets with ‘x’ buttons for removal (see screenshot below).

The front end uses the bog standard four-point-nine theme with a few tweaks:

The default form shows a list of hyperlinked printers by splitting the compatibility custom field up via breakby and breakform (see below).

Each printer uses a <txp:article_custom> to extract and display a list of all cartridges that fit in that printer by looking up its ID in the compatibility field (also see below).

Each image has a CSS overlay added on the fly which contains the product SKU, the capacity, and a natty CSS colour blob depicting the colour.

I wrote a pair of import scripts in a Txp article (in a /manage section), to take his humongous CSV file, map and merge the fields into Txp’s field structure and import the data. One script was for importing printers into the category tree, the other was for importing products. It injected all the Cartridge>Printer field mappings too.

I then wrote a custom ‘Export CSV’ article in /manage that presents a little form to allow anyone to choose which records to export. Date/time range, number of records, offset, which section, etc.

When the script runs, it grabs all the data and spits it out into a huge CSV file suitable for importing (via a WP plugin) into WordPress.

Since WP is unable to manage image overlays (every image has to be unique and in its Media library), Textpattern generates an image on the fly by compositing the base pic with the overlay and writing it into a temporary, web-accessible file. That file URL is the one referenced in the CSV so that, during import, the pic can be automatically uploaded into the WP Media library as a dedicated image.

During export, cross-selling information is built on the fly via a query and stuffed in the CSV so that WooCommerce can show compatible Magenta, Cyan and Yellow cartridges if viewing the Black cartridge, for example.

The Compatibility table is imported into a WP category taxonomy so site visitors can browse their printer model to find compatible cartridges. In fact, the compatibility table is bi-directional and drives both this navigation aid, AND the cross-selling information.

Stupidly, Textpattern article IDs do not correspond with WP IDs. Despite me exporting them and telling the import plugin to use them, WordPress cannot do this and uses its internal auto_increment values. Luckily, after hunting for suitable import scripts (most of which want $$$) I found a free flexible one called “Import WP” that has a free WooCommerce extension and allows a “key field” to be specified. When I defined the (extensive) mapping configuration, I told it that our article ID is the key so that, when a CSV file is re-presented for upload, it somehow knows that it’s related to the WP article ID and performs an Update/Insert as appropriate.

The upshot of all the above is that the inventory (data) and its management are now separate from the website (display). Txp acts as an intermediary data source only. The bare front-end site allows rudimentary navigation between products, compatibilty links between articles to be checked, search and image previews per product, etc. Galleries are supported if you upload more than one pic. Edit links on each individual product page jump to the admin side to manage that article on the Write panel, for convenience.

The back-end allows product and printer articles to be managed. Links can be made between them via the custom glz_cf scripts. Articles can be updated en-masse (e.g. for altering pricing or sales) via adi_matrix. And the whole caboodle can be sliced and diced and exported in any format he desires, to upload to his WP customer-facing site, his WP trade-facing site with prices excluding tax, his HubSpot CRM to allow trusted partners to order a subset of products directly while on-site at preferential rates. And so on.

Everything is driven from Txp: management in one place, export to many systems. If I could solve the authentication issues, there’s nothing to stop a third party system from directly accessing the Txp site and scraping the data from a custom export template instead of downloading it first and uploading the CSV. That, however, carries the danger that a competitor could scrape the data off the website too. The amount of work that has gone into preparing and ultimately managing the data links is incredibly valuable, so I’m resisting that for now and have the data squirrelled away behind do-not-index rules, and we’ll live with the two-step process.

I’m simply blown away with how fast Textpattern is to manage the stock records. Even the inefficient method to display the compatibility is lightning fast:

<if::individual_article>...

It returns results in under quarter of a second, from inspecting 3500 HUGE pipe-delimited fields, splitting them up and matching the printer article ID. It would probably be more efficient with an actual direct query in PHP, but this is simpler and easier to maintain.

The companion piece of code that displays the printers that are compatible with the cartridge is a lot less invasive. It simply splits the custom field by pipe and passes each value through a form to render a hyperlink:

<custom::field label="Compatible with printers:" labeltag="h4" name="compatibility" breakby="|" wraptag="ul" breakform="article_link" break="" />...

and Form article_link is:

<article::custom id='<txp:yield item />'>...

The hyperlinks are messed up because the /printers section uses breadcrumb URL mode. Not sure why the permlinks duplicate the section name in the URL, with /printers/printers/{cat}/{hierarchy}/{here}. But the links work so I’m not questioning it!

All in all, I love that Txp can help in this way as the glue that binds muiltiple sites together. It means less reliance on manually managing complex CSVs and manually taking 1000s of image product shots (which is how stock was managed up until last month) and opens up the scope for using the inventory management tool for more powerful single-point-of-access data management and export to any number of systems in future.

Plugins:

adi_matrix

bot_write_tab_customize

com_article_image

glz_custom_fields

smd_custom_scripts (specific to the site for interfacing with glz_cf and handling the JS)

smd_where_used

wow. this deserves a major article.

Dragondz · 2025-05-13 09:07:49

I know that textpattern is great, I am using it myself for a marketplace with hundreds of users and products and it s still fast.

etc · 2025-05-13 09:14:45

Out of curiosity, would pre-filtering by compatibility be any faster?

<if::individual_article>
   <txp:variable name="prnid"><article::id /></txp:variable>
   <article::custom compatibility='%<txp:variable name="prnid" />%' limit="0" label="Products"  labeltag="h4" wraptag="ul">
      <if::custom_field name="compatibility" match="any" separator="|" value='<txp:variable name="prnid" />'>
         <li><txp:permlink><txp:title /></txp:permlink></li>
      </if::custom_field>
   </article::custom>
</if::individual_article>

A better option might be storing compatibility values pipe-enclosed: |1|2|66|. Then you could match the compatible articles with compatibility='%|<txp:variable name="prnid" />|%', removing the need for if_custom_field check.

jakob · 2025-05-13 10:07:21

Great to hear it has worked out, and that Textpattern remains so responsive despite the huge quantities of data you’re handling. It sounds like you have truly given him back the reigns to managing his business. Maybe you’ll find a smoother way to do import and export over time, but if it only happens periodically, then that sounds manageable. Brilliant work!

Bloke · 2025-05-13 15:25:32

etc wrote #339708:

Out of curiosity, would pre-filtering by compatibility be any faster?

I expect so. I’ll give that a spin, thanks.

A better option might be storing compatibility values pipe-enclosed

It’s a glz_cf field so I’m at the mercy of how the data is stored in the plugin. And this particular field sometimes has up to 750 chars of data in it currently (those printer manufacturers like to make their ink cartridges work in as many products as possible!).

Every character counts (and yeah, I know it’s only 2 extra chars). I already had to manually boost the CF field width to 1k characters.

Bloke · 2025-05-13 19:13:19

etc wrote #339708:

Out of curiosity, would pre-filtering by compatibility be any faster?

*refreshes the page*
*blinks*
*misses the refresh*
*refreshes again, just in case*

That’s almost instant now. Wow. What a great idea. Thank you so much.

Bloke · 2025-07-04 13:56:05

Circling back on this, after a few meetings with the client, I’ve solved another of his pinch points: image management.

If you read my OP blurb above, you’ll note that it uses CSS overlays to add the product info to the images, and squirts those overlaid images out as new images during export, ready to be imported into WP’s media library. That’s all well and good, but he still has the pain of uploading each new image to each article. He needs to upload a ‘blank’ product photo to a group of articles so the overlay can kick in and add the cartridge colour, SKU and so forth.

So I retreated to my thinking box. And came up with a solution.

Instead of making the overlays on the fly, why not just generate the images en-masse and associate them with the Article Image field in one step? Then, the export file just contains a direct URL to the pre-generated /images/NNN.png file, and WP dutifully imports it. That makes the export faster too.

I hunted for third-party tools to do it. Came up empty, without paying someone or buying custom software. Plus I’d have to prepare a CSV file in a specific format to do it. He’s traditionally paid some agency to do this donkey work. They take new product photos from various angles, blank out the label, take a spreadsheet from him, and marry the two to create him a folder of 3500+ images, with the sku code added to the blank space on the product packaging, and each image file named with the {SKU}.png. Which he then has to manually associate with the WordPress products (though I think it can be done by bulk uploading and telling WP to use the SKU as the key – not sure).

Anyway, my brain:
a) Textpattern has all the product data in custom fields.
b) He has a range of blank asset photos.
c) PHP has the Imagick library installed.
d) Add code glue for a match made in heaven.

So I wrote a back-end management pane, courtesy of smd_tabber.

This is the workflow (see screenshot below):

Click Browse… on the image picker to choose the (white-label) product shot that applies to a bunch of articles in a product range.
Use a live filter onblur() field to fetch a dropdown of all matching products in a family from the Txp database. Refine, to choose which ones the image applies to.
In a series of form “Fields” below, choose/type:
- The article/custom field item you want to display, from a dropdown of fields.
- Its X and Y co-ordinates on the image to show it.
- The angle (rotation) at which it’s to be displayed.
- The font size.
- Whatever else I may expose in future (font face, colour, etc).
Repeat the selection of CF item/co-ordinates/etc as many times as you like, one per item you want to display.
Select one of the articles from the filtered set as a ‘preview’ data set.
Hit ‘Update Preview’.

The form fields are submitted. Imagick renders each item of data from the selected article in the position/orientation specified, composited onto the blank product label, and displays the preview.

Adjust params to taste if things aren’t quite right. Each set of params is stored in the prefs table as a named ‘preset’ so he can set it up once for a particular type of product shot and then just select the appropriate profile to load the positional info into the fields.

It’s possible to easily clone a profile preset. Just load in one that’s close to the image you’re building, load the blanked-out image, pick a test article to grab the custom field data from, and overwrite the profile name before previewing it. A new profile is created, which can then be tweaked as normal.

To help, if you click on the image, the X and Y co-ords are displayed (scaled up based on viewport dimensions).

Now, Imagick has perspective transforms. My plan was to do the compositing in two phases:

Draw the text item.
Read a ‘strength’ field from the form data to determine how much ‘depth’ to apply. Render the text as a transparent .png and then composite it using the perspective transform to make it look more 3D.

However, after testing it with just simple X/Y/angle, the shallow depth of the product photo shots and fairly short nature of the text items meant that the results were passable without the added perspective complexity. So I’ve left it out for now.

All well and good. The magic occurs next. When you’re happy with the presentation of the image, just select all the filtered articles you want the image applied to, and hit the ‘Generate Images’ button. That will:

Render each image by compositing the text.
Run it through our image_data() function to insert it into the database and move it to the /images directory.
Update each selected Article Image field to point to the relevant new image ID.

The result? All product images pre-rendered with custom field data overlaid in-situ wherever he likes on a template product image.

Repeat the above process for the various product families. Any time product packaging changes, bring up the new pic, load the profile, tweak if necessary to move the text, and apply it to the relevant articles. Export to CSV. Import to WP. Done.

I’ve currently limited it to a maximum of 6 items that can be overlaid, mainly for screen real estate sanity, but if I change one value, that can be altered. I also exposed the article title in the field selector, so if he wanted to add the full title in some whitespace area above/below each product, or add some info box of product field attributes, he could. Nothing to stop me preparing a ‘watermark.png’ file and adding a special watermark option to the field list, which could be composited too. Lots of scope. Or adding fixed text/label support.

I’d love this to become a portable tool but there are a few chunks of code very specific to this application. One is the fact that Imagick can’t seem to output multi-colour gradients easily so the colour-dots on the product packaging need dedicated code. If the article is a multi-colour cartridge (or a combo set), I’m cheating and compositing a little pre-rendered multi-colour.png gradient picture onto the image, instead of using Imagick::Draw to create a filled circle. Bit of a hack, but it works.

All in all, I’m rather pleased. The back-end panel isn’t pretty, but as a proof of concept, it’s good enough.

If anybody has any need for this kind of setup to manage their product Article Image inventory, just holler. No point anyone re-inventing my wheel.

Last edited by Bloke (2025-07-04 14:12:23)

jakob · 2025-07-05 03:53:39

Pretty impressive as a sophisticated dynamic watermarking exercise. And a great example of improving client workflow and getting the computer to do monotonous work. Thanks for the write-up – it’s always nice to hear the thinking behind it, even if just as inspiration for other parallel situations.

Bloke · 2025-07-05 06:57:19

Thank you. I considered many other options first; even Affinity Photo / Publisher which have a bulk data merge facility. But that would require me to save the files out with a known filename format, and still leaves us the issue of linking them to the articles, either on export, or by uploading them and running a script to associate them.

So I then hunted for online solutions. Nope. Just people offering to do it, or services with eye-watering price tags.

I was surprised nobody had done anything like this, given the prevalence of e-commerce sites. And it was during this research phase I started to think that there must be a way of doing it using Txp data. And using core functionality to do the assignment en-masse per group of products.

A few days and some hacking later… et voila!

Textpattern CMS

Textpattern CMS support forum

#1 2025-05-12 23:36:15