Best way to store table data - with columns managed per-page?

[cst989]

Hi guys, this is one that's stumped me for a while and I've tried various methods none of which I've been particularly happy with.

Basically I want a table field where the CMS user can add or remove columns as they need them.

The usage for this is a catalogue website where products have a long list of model variations. For each product category or even individual products, the headers and number of columns could be completely different.

A HTML table in a WYSIWYG would probably suffice - at least the kind of functionality you get with that is the kind of thing I'm after. Except that I want it to be both importable from a CSV, and the resultant data to be a bit more accessible afterwards.

 

One option I've used in the past was to use Repeater Matrix, where the first entry controls headers and subsequent entries control all rows (and have to have the same number of sub-fields to match the header row). This worked from an import/usage perspective, but is hardly clean management in the CMS.

Other times I've resorted to the fact that the CMS user can never manage the table with the CMS, and simply has to re-import. This way I can simply store the data however I like (custom table, json etc).

 

I considered using ProFields: Table and simply putting all the possible columns in one field, then the user simply scans over fields that don't apply to the product. My concern with this is that the table could be huge and slow down the site - is that a valid concern?

[MarkE]

Something like this?

If you are interested I will post details later - no time now...

[cst989]

That looks... remarkably like what I mean! Probably more feature-rich than I even need but definitely solving the same issue.

The latest idea I was going to do was to use the ProFields: Table I mentioned above, and then use hooks to cache and store only the columns I need on a per-page basis. But I might keep an eye out to see what your solution is first! ?

[BillH]

A ProFields Table field sets up its own database table, so I don't think you'd have any performance issues at all, even with large numbers of columns and rows.

But I'm not sure what the UX would be like with very many columns, perhaps most of them empty. I don't know if it'd be possible in your circumstances, but might it reduce the number of empty columns to have various types of product, and a different table field for each type (with subsets of columns), hiding/showing the fields depending on the type selected?

Meanwhile, you might find this discussion useful:

 

[adrian]

This might be what you're looking for: https://processwire.com/modules/fieldtype-matrix/

[MarkE]

4 hours ago, cst989 said:

But I might keep an eye out to see what your solution is first!

I suspect that one of the other suggestions might fit your particular needs best @cst989, but FWIW, here is a very brief outline of my 'spreadsheet' solution:

• The whole 'sheet' is a page - rendered in the front end.
• Each column ('stage') can be of multiple types and is a repeater matrix item.
• Each row is a field. Not all stages have all the fields.
• The rendering template php loops though the 'stages' within the fields.
• Some row/column combinations are editable in the front end and may be complex fields in their own right (e.g. repeaters or my FiledtypeMeasurement), so the use of table or matrix  did not fit my need.
• Users can add new columns as shown. To resequence columns (at the moment) they need to edit the page in the back end.
• The sheet is responsive and scrollable.

So, in summary, it is a hand-built solution based on repeater matrix. If you do want to go this route, I can share code snippets.