Merging repeat rows into a new row as a summary

[PaulT]

Is it possible to use a Form PHP plugin that will merge all rows in a repeat group that use the same Job Code so it sums the hours and amount fields into new fields. See diagram...

 

[cheesegrits]

You might be able to create a MySQL view to do it.

create view timesheet_repeat_summary as select *, sum(job_hours) as job_hours_sum, sum(job_amount) as job_amount_sum from  geh_timesheet_58_repeat group by parent_id, job_code

Then copy your main list in Fabrik (on the main Lists page select the list and hit the Copy button, it'll offer to let you give the new list/form a different label, call it something like "Timesheet summary"). Edit the list settings for the new copy, remove the original join for the repeat group. Add a join to the new view, same setup as the original (id to parent_id, repeat)

You won't be able to edit or save anything (views are read only), but it could be used as a summary.

-- hugh

 

[PaulT]

I have done this but when I went to view the list I got this error message:
500 - Fabrik has generated an incorrect query for the list Weekly TS Summary Report: <br /><br /><pre>Unknown column 'timesheet_repeat_summary.' in 'field list' SQL=SELECT DISTINCT `geh_timesheet`.`id` AS __pk_val0, `timesheet_repeat_summary`.`` AS __pk_val1 FROM `geh_timesheet` LEFT JOIN `#__users` AS `#__users` ON `#__users`.`id` = `geh_timesheet`.`employee_name` LEFT JOIN `timesheet_repeat_summary` AS `timesheet_repeat_summary` ON `timesheet_repeat_summary`.`parent_id` = `geh_timesheet`.`id` WHERE ( `#__users`.`id` = '814' OR `#__users`.`id` > 0 )</pre>

 

[PaulT]

I changed the Display mode in the List Joins from "Merge rows and reduced data" to "Each row separately" and the list view is now loading.
The Job Codes and Work Types were coming up as just ID numbers and I found that those elements had been converted back to ordinary fields, so I reset them to a Database join and the other to a cascading dropdown the same as they are in the main Timesheet form and now they work correctly.
Everything is working fine on list view but when I go to detail view only the first row shows for each repeat group. Any idea why that might be happening?

 

[cheesegrits]

The Job Codes and Work Types were coming up as just ID numbers and I found that those elements had been converted back to ordinary fields

Well, they didn't really get "converted", it's just that when you create a view, you are creating a new "table" which Fabrik knows nothing about when you join that copy of the main list to it. So you need to set the individual elements up as joins, if that's what you need.

Everything is working fine on list view but when I go to detail view only the first row shows for each repeat group. Any idea why that might be happening?

Well, there is only one row. Remember that copy of the list is now joined to a view which just has that one row per job code with the totals.

Two options. One is to set the List's "custom details link" (for that copy of the list) to point to your original form ID ...

index.php?option=com_fabrik&view=details&formid=X&rowid={rowid}

... replace X with the numeric form ID of the original form (for the list you copied from, which ahs the "normal" join to the jobs).

Or, add another join to your list copy, joining to the original geh_timesheet_58_repeat, and hide it from list view.

-- hugh

 

[PaulT]

Neither of these methods worked for what I want for the details view. It still only shows the one summary for one job code, I need the details view to show all the summaries for all the job codes just as it does in the list view. I now realise the database view is a summary of job codes pulled from geh_timesheet_58_repeat and that just results in one row. I just have to get those rows to act like they were repeat rows somehow. See Diagram

 

[cheesegrits]

Did you set that join to repeat?

I can't really help much more in Community support.

-- hugh

 

[PaulT]

Yes the Group is set to Repeatable and the join in the list is set to repeatable. Once the custom job you are doing for me is working I will see if I can get some more money from the client.

Actually I wouldn't need the details view to work if the PDF print out from the List view could print each employee summary on a separate page. If some sort of page break could be placed after each list calculation split. (The calculation split occurs on each employees).

The problem that I am trying to solve here is the client wants to print out about 100 individual employee's weekly summaries in one (or at least amount) go. With the download plugin you can do bulk downloads of individual PDFs but in this case the details view isn't showing all the data so that doesn't cut it. The list view does show all the data and formats pretty well. You can download 100 rows in one PDF file but they cross over pages. If they could be separated one employee per page that would fit the criteria.

 

[cheesegrits]

I resolved that "PDF local" thing. As per the other thread, turned out to be a difference between your server configuration and all the others I've tested it on, in that your REMOTE_ADDR server address (which is the only way code can test if the current page load was "local') shows up as a 192.168.x.x address rather than either of the usual 127.0.0.1 or ::1 "localhost" addresses when making a CURL call to itself. I'll have to add another global setting to allow specifying additional addresses which should be considered "localhost".

We can help with this stuff, but it's going to have to be on a custom work basis. I'm already 3.5 hours out of pocket on this, and by the time I've gotten that new option in and tested, it'll be more like 5 hours.

On the bright side, I now have your site (as of Friday PM) installed here, and set up as a debuggable PHP Storm project, which will dramatically speed up any further custom work on it.

-- hugh

 

[PaulT]

Excellent that you figured it out! Very Well Done. Not so good you are out of pocket but this project is only halfway through so by the end of it I would say you will have a chance of getting that back somehow.
Just so you know the test site you have a copy of is actually on a different server to the live site so it is going to be interesting to see if that one is set up the same way as this one as far as localhost goes.

 

[cheesegrits]

I figured out why that repeat group doesn't show as repeated in the detail view.

-- hugh

 

[PaulT]

I sort of gather it is to do with the fact that the Fabrik form never created the repeat groups records for the summary at anytime. So even though it has the right id and parent IDs it doesn't know that they are repeats?

 

[cheesegrits]

Nope. Works fine for repeat groups built on tables. Just not on views.

Now if only you were in a paid subscriber group, I could tell you what my hour of poking through the code revealed.

-- hugh

 

[cheesegrits]

Oh, alright.

For repeat groups in form and detail views, we rely on knowing what the PK of the joined table is, so we can track which rows we've merged into the repeat structure. But MySQL views don't have "Primary Keys". So we have a mechanism to work round that, but it relies on the joined table also being "known" to Fabrik as a List. So if we fail trying to get the actual MySQL defined PK from it, we look up that table name in our fabrik_lists table, where even Lists built on views have a "PK" assigned to them (as we have to know what element to use as the 'rowid').

(BTW, this is also why you got that MySQL error when trying to do "Merge rows" for that join, as we also need to know the PK of the join for that)

So what you have to do is ...

1) Use phpMyAdmin (or whatever), find the row for that repeat view in the #_fabrik_joins table, and in the params field you'll find an incomplete param for the pk ...

{"pk":"`timesheet_repeat_summary`.``"}

Remove that, so it's just ...

{}

2) On the Fabrik backend, create a new Fabrik List, using that repeat view as the table. Save it, edit it, and set the Primary Key to 'id', save it again.

Your details view that uses the view as the repeat should then work. I tried it here on my copy of your site, it works.

It would be nice if you would take out a subscription for support, though. I've gone way beyond Community support on this stuff.

-- hugh

 

[PaulT]

mmm sounds rather complex but I will give it a go. I just subscribed to Standard support that is all I can afford at the moment.

I just tried what you said and it did work, that is really good!

 

[cheesegrits]

sounds rather complex but I will give it a go

Note that the first step is a one off thing, and only necessary because the repeat join got created before setting that view up as a list. You can avoid that in the future, if you ever join a view again, by creating the List for it first, and assigning the PK. The second step is a very simple, standard operation. Click new, type a list name, select the table, save, select the PK, save. Usually the "select the PK, save" part wouldn't be necessary, as Fabrik would automatically assign the PK by asking MySQL what the PK field is. But as views don't have PKs, you have to select it manually.

I just subscribed to Standard support that is all I can afford at the moment.

Thanks for the sub. I understand about affording things, but we can't afford to work for free, same way you can't work for your client for free. You are saving hundreds of hours of work by using Fabrik to build your app - looking at your site, if I was building everything Fabrik is doing for you from scratch as a bespoke J! extension, I'd be quoting well into four figures, with a substantial ongoing support contract for future proofing built in. So I don't think $20 a month (or whatever it works out at) is unreasonable.

BTW, that paragraph isn't specifically aimed at you. We need to reset expectations, and modify the way we support Fabrik if we are going to keep going. I'm just kind of practising the various reasons and justifications.

-- hugh

 

[cheesegrits]

BTW, I added some more Hail Mary handling when adding joins, to improve how we deal with joining to views.

https://github.com/Fabrik/fabrik/commit/1d6efb301ec5beed5f8a566a85cc4c3c42c69324

... so in the future, we won't end up with that malformed `tablename`.`` PK param in the join if we can't figure out what the "PK" should be. And we will now warn the admin, and tell them they need to set up a Fabrik list on the view first, where they can specify what the "PK" should be.

-- hugh

 

[PaulT]

Thanks for the sub. I understand about affording things, but we can't afford to work for free, same way you can't work for your client for free. You are saving hundreds of hours of work by using Fabrik to build your app - looking at your site, if I was building everything Fabrik is doing for you from scratch as a bespoke J! extension, I'd be quoting well into four figures, with a substantial ongoing support contract for future proofing built in. So I don't think $20 a month (or whatever it works out at) is unreasonable.

BTW, that paragraph isn't specifically aimed at you. We need to reset expectations, and modify the way we support Fabrik if we are going to keep going. I'm just kind of practising the various reasons and justifications.

-- hugh

I totally get it, this is my very first project using Fabrik, I don't know how to write PHP or javascript, yet I have been able to put together a pretty useful application because of Fabrik. It is a bit of a balancing trick trying to get a product to a client that you don't really understand how it works and trying to meet their expectations. I think the more I get results for them the more they will be willing to pay more and I will certainly flow this onto you also. The Fabrik component is too good to let it fail due to lack of support.