Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Using Textpattern as an inventory management system
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
breakbyandbreakform(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.
- The default form shows a list of hyperlinked printers by splitting the compatibility custom field up via
- 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
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
Re: Using Textpattern as an inventory management system
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.
Offline
Re: Using Textpattern as an inventory management system
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
breakbyandbreakform(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.
…. texted postive
Offline
Re: Using Textpattern as an inventory management system
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.
Offline
Re: Using Textpattern as an inventory management system
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.
Offline
Re: Using Textpattern as an inventory management system
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!
TXP Builders – finely-crafted code, design and txp
Offline