Display tables with multi columns from same field

[AndyR]

Hello,

Request for guidance:


I?ve been trying out Fabrik for a few weeks now as an alternative to hand coding a website upgrade. I?ve got most of my previous database tables transferred into Fabrik and I?ve got the lists all set up for input of new data, which is great, unfortunately I can?t seem to work out how to display the data in tables of a similar layout to the current website. I?ve looked through the forums but I don?t see a solution. Maybe I?m missing something obvious.

I?ve attached a screen shot of one of the tables, which contains hours worked, by area (area_ref is joined to an ?areas table), by date (all the dates are Sundays - they update the data on a Monday and the current system just calculates ?last Sunday? and saves the date.

There?s also a screenshot of the table I?m trying to replicate.

What I?m trying to do is this:

for the field ?area_id? I want to split the data so there is one column for each area. This is the bit I?m struggling with how do do. Is there an easy way to accomplish this with Fabrik, so I can use all the associated tools & views, or do I need to write a custom template, or something else?

I still have the code for the original tables so I could adapt that, but it was hand written roughly (by me) and I?d like to progress to a better and hopefully easier way. I also need to see the data the other way round (areas down the side, dates across the top) but I?m sure once I?ve sorted out how do do it one way I can manage the other.

Any assistance & pointers appreciated.

Thanks

Andy R.


Almost forgot: Joomla 3.1.5, Fabrik 3.1rc2

 

[rob]

Hi Andy
We don't have anything in place for this type of display, which is known as a pivot table.
Give me a bit of time and I'll try to come up with a solution for you. If you could post the original code that might help me

thanks
Rob

 

[AndyR]

Morning,

Thanks for the reply - I thought I might have be missing something simple there. I've attached the PHP file of the previous version, The file is pasted directly into a Joomla article via DirectPHP. It's quite poorly written but I was learning php as I went along, and this was one of those evolving jobs where the client didn't really know what they wanted, and bits are constantly being added. There are loads of tables of data like this, all with frontend input forms that I also wrote by hand, they all just read and write to a separate database.

I'm at a place now where I've a rough idea what's needed and what is likely to be needed going forward so I've started again from scratch, trying to make the whole thing a bit easier to manage using more object oriented code and stuff.

Fabrik has been great so far for the input forms side of things, so I'm rebuilding the all the tables in Fabrik on a new Joomla 3 install, it's just the actual tables display I was struggling with.

I could probably get away with repurposing the existing DirectPHP code for the displays, but I was thinking there must be an easier way - it's not like what I'm doing is that unique or original.

Any assistance appreciated.

AndyR

 

[AndyR]

Afternoon,

I've made a bit of progress with this using the Joomla module 'ARI data tables' for quickness and the following SQL statement:

SELECT a.id, a.area_name
,MAX(IF(DATE(h.week_ending) = '2013-12-29', h.hours, NULL)) as '2013-12-29'
,MAX(IF(DATE(h.week_ending) = '2013-12-22', h.hours, NULL)) as '2013-12-22'
,MAX(IF(DATE(h.week_ending) = '2013-12-15', h.hours, NULL)) as '2013-12-15'
,MAX(IF(DATE(h.week_ending) = '2013-12-08', h.hours, NULL)) as '2013-12-08'
,MAX(IF(DATE(h.week_ending) = '2013-12-01', h.hours, NULL)) as '2013-12-01'
FROM dms_areas a LEFT JOIN dms_hours AS h ON a.id = h.area_ref
GROUP BY a.id

this generates a simplified version of what I need, It's the opposite way round at the moment to the table I uploaded (this one is from a different page) but I think it will work. I can probably expand it to work out the last 5 weeks actual dates automatically, and from there join another table 'councils' as well.

Ideally I need to be able to select the start date, no of weeks and narrow them down by council with cascading dropdowns or similar, which I can probably do by hand as I currently do but if there's any way to generate this with Fabrik I'd rather do that.

Screenshot of the current table generated by the above SQL statement attached.

Andrew.

 

[rob]

Hi
Yes that looks similar to how I was playing around with pivot tables last time I looked.
I've had to update the core code to allow for additional plugin hooks into the way we build the main list query in Fabrik, then I've added a pivot list with this commit plugin https://github.com/Fabrik/fabrik/commit/1535ef54fee7ae507584a6c4f2218b6f4cbb3f79

So You will need to update from github (see my signature for a link to the wiki on how to do that)

Then :

• discover the new list plugin in extensions->discover
• Once the plugin is installed ensure it's published.
• Then make a list which points to the mySQL database table from which you want to create the pivot view.
• Add a pivot plugin with setttings similar to this screen shot http://screencast.com/t/5DnWoWWr9v (I've tried to mirror the data in the orginial screen shot you posted)
• Save the list
• Hopefully your list when viewed will look like this http://screencast.com/t/X8kc02ptT

• Notes
• ordering should work on the Y column element (in this case the area element)
• Formatting should still work, e.g. I turned the area element into a database join element, so its pulling in the country label for the stored country id
• Filtering should work as normal as well
• Normal Group by wont work (the plugin overrides this option)
• Not tried on data sets with joined list data.

 

[AndyR]

Afternoon Rob,

Thanks for the swift response. I've updated Fabrik from Github as per your instructions, I've created a new list from the table, and filled in the Plugin as closely as possible to your screenshot (There was nothing in the 'sum' or 'group by' dropdowns - see attached screenshot - I thought this may be relevant) but my list view doesn't look quite like yours. Attached screen shot of this as well.

I do have a databasejoin element in there though, but this is needed really. Ill try setting up a test table without a join though and see what happens.

The pagination still isn't working in the backend so I published the list in the frontend so I could scroll through Attached screen shot of this as well. I've also added a shot of the elements for this list. I did note that there are only a couple of areas showing, and if I increase the 'display#' in the pagination it just increases the number of columns.

I'll carry on fiddling with the settings but if you have any clues I'd be most grateful.

If you think it would be better for you to have a look at the site directly I can give you access - It is online but not currently live.

Thanks,

Andrew.

 

[p38]

I?ve been trying out Fabrik for a few weeks now as an alternative to hand coding a website upgrade.

Hi AndyR, you won't go wrong with this choice. I have been using Fabrik for over a year, and develop some serious business systems using only Fabrik.

There is always a way of doing what you want, and Rob and Hugh are absolutely brilliant in the support they give.

Paul

 

[rob]

hi Andrew
Yes, I guess I'd need to look at your site, so if you could fill in your details here http://fabrikar.com/you/my-sites/ (only staff can see them) with a super admin user and an ftp account I can figure out what the difference is

_Rob

 

[AndyR]

Afternoon,

I haven't had time to get any further with it today, but I've just added all my details if you want to have a look.

Andrew.

 

[rob]

ok found a couple of issues with what I had done.
first I was trying to sum the formatted values (which had "," in them) - that meant the sums would return null. Changing the code to sum the raw values fixed that.
Then I realized that we were constraining the list query (only getting the first 10 rows) which isn't right for this scenario, so I removed that.
However, then that produced a query which takes a long time to run, so I've added a cache option so that the data can be cached for quick retrieval.
I've also removed the pagination as that is not needed.
Changes are up on your site and I'll commit them to github now

 

[AndyR]

Hello Rob,

Apologies for not replying to this earlier - I've been working on some othe projects for the last couple of weeks so I'm just catching up with this.

I've had a look at the data view and it appears to be just what I'm looking for. It is a bit slow, but then again I'll only need to pull out a section of the data at a time - e.g. last 5 weeks for all the areas attached to a particular council - I can probably work this bit out once I understand how the plugin works.

I've had a look at the form elements you used though to see if I can get my head around it and there seems to be a display problem when I click on elements in this particular form. Instead of the usual 'details' page with the menu on the left and the input forms on the right, i just get 3 bullet points with the following text:
Element properties linked to:
Hours
Unlink
(See attached screen grab)

When the page first loads it appears to start drawing the whole page, then about a second later it all disappears and looks like the screen grab. I think it must be just a display problem though because the form appears to be still there - when I hover over the blank areas of screen the cursor changes.

Tested in Firefox & Chrome for Mac.

Andrew.

 

[rob]

Hi Andrew
thats because those elements are linked to/copies of the original element, see this wiki page for an explainiation: http://fabrikar.com/forums/index.php?wiki/elements/#parent-child-elements, it refers to fabrik3.0 but the principle is the same in fabrik 3.1

 

[AndyR]

Hello Rob,

Thanks for that, I think I understand it now. I'll see if I can get it working how I want then.

Andrew.

 

[AndyR]

Hello,

Not sure if I'd be better starting a new thread for this, but I'm trying to refine the 'pivot' plugin as detailed here so this seems like a reasonable place...

I've created another table using the 'pivot' plugin, which is effectively just a copy of the previous one created buy Rob in case I break it, and I'm trying to filter the data shown, so I've added a pre-filter (see attached screenshot). I've used one of the wiki examples to keep it simple until I get it working. Unfortunately it doesn't seem to be doing any actual filtering but I'm not sure why.

The database field is a straightforward date/time field, and I can filter it with the search date selector in phpmyadmin directly. I've disabled Caching for this list but still nothing.

Any help appreciated.

Andrew.

 

[rob]

hi Andrew - that's fine to continue the discussion here. I think though that you forgot the attachment!
I had a look at your site but couldn't work out which list the prefilter was applied to, if you let me know which one it is I can take another look

 

[AndyR]

Aha! yes I did. Here it is, the list is called 'Weekly hours pivot 2'

 

[rob]

great thanks!
The reason why the prefilter isn't applied is that you have it assigned to the view access level 'public', but Super Users groups are not assigned to that view access level.
So if I change the view level access to 'Registered' for example the prefilter is applied when logged in as a Super Administrator

As you want the pre-filter to run for everyone, I would probably create a new access level 'Everyone' and assign all groups to it. Then assign the prefilter to this view level.

Also remeber whilst testing this to turn off the plugin cache, otherwise it will load cached values and not those set by the current plugin settings

 

[AndyR]

Morning Rob,

Thanks for that. I hadn't started messing with viewing access levels yet so I didn't think of that. I'll have a read through the viewing access levels info and try again.

Andrew.

 

[AndyR]

Hello,

I've been having another look at this list, and I've added another prefilter to narrow down the data, but I'm getting a sql error and I can't see why. I've attached a screen shot of the prefilters, which look pretty straight forward to me, and this is the error:

500 - Fabrik has generated an incorrect query for the list Weekly hours pivot 2: <br /><br /><pre>Unknown column '' in 'group statement' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `dms_hours`.`id` AS `dms_hours___id`, `dms_hours`.`id` AS `dms_hours___id_raw`, `dms_hours`.`date_time` AS `dms_hours___date_time`, `dms_hours`.`date_time` AS `dms_hours___date_time_raw`, `dms_hours`.`area_ref` AS `dms_hours___area_ref_raw`, `dms_areas`.`area_name` AS `dms_hours___area_ref`, `dms_hours`.`week_ending` AS `dms_hours___week_ending`, `dms_hours`.`week_ending` AS `dms_hours___week_ending_raw`, `dms_hours`.`hours` AS `dms_hours___hours`, `dms_hours`.`hours` AS `dms_hours___hours_raw`, `dms_hours`.`id` AS slug , `dms_hours`.`id` AS `__pk_val` ,SUM(`dms_hours`.`hours`) AS `dms_hours___hours`, SUM(`dms_hours`.`hours`) AS `dms_hours___hours_raw` FROM `dms_hours` LEFT JOIN `dms_areas` AS `dms_areas` ON `dms_areas`.`id` = `dms_hours`.`area_ref` WHERE ( dms_hours.week_ending > (NOW()- INTERVAL 26 WEEK) AND dms_hours.area_ref = '1' ) GROUP BY `` ORDER BY `dms_hours`.`week_ending` ASC,`dms_hours`.`area_ref` ASC</pre>

Probably something obvious I'm missing as usual but any pointers appreciated as I'm lost at the moment.

 

[cheesegrits]

When do you get this error? When displaying the list?

Typically it helps to spell out the steps you are taking that generate the error, so we can ore easily replicate the issue on our test setups. That looks like one of our main list getData queries, but I'm not sure.

-- hugh