1. NOTICE: If you are using Fabrik and update to Joomla 3.10, you will need to update to Fabrik 3.10. And, if you are using Fabrik, do not upgrade to Joomla 4, we do not have a supported version ready for release. More information on a release date coming soon. Also, please note that Fabrik 3.10 will not install on any Joomla sites less than 3.8.
    Dismiss Notice

Help with Custom Calc

Discussion in 'Professional Support' started by bggann, Jul 31, 2018.

  1. bggann

    bggann Member

    Level: Community
    I'm trying to use a custom calculation on a field to show just the 1st or last record value in a grouped list display.
    It doesn't matter if it is first or last, because all the values in that group are the same.

    The reason is I want the "calculations" line to have the value of these fields along with the "Sum" values from other fields. I'm showing just the "calculations" line from each group in a custom template.

    I realize I can use %s to get the 'value' of the field in a custom query, but it appears to return just the first record value for all groups. I tried to build a query to return the value based on the grouped by element, but could not get past sql errors.

    To reiterate:
    - I have a repeated group in a list.
    - I display that list 'grouped by' one of the fields (elements) in the group.
    - All the numeric values are totaled using the SUM calculation.
    - There are 4 elements (text fields and one date) that are the same for all records in the group.
    - I want to display them on the Calculations line in the list by adding a Custom Calc that returns the 1st (or really) any value in the group.
    - If I just use %s in the custom calculation - I get the value from the first record of the list, not the first record from the group.

  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    No ideas at all, and it's a long time since I've looked at the custom calc stuff - in fact you are the first person who has asked about it since I added it, I think.

    I'll dig around in the code and remind myself how it works.

    Which site / list / element of yours is this, so i can peek at it.

    -- hugh
  3. bggann

    bggann Member

    Level: Community
    Okay - as usual with my sites, this is a bit complex

    Access the training- site that is in my 'sites'
    - Log into the front end
    - Select "Reports/SEAT Reports/Seat Daily Cost Summary - Incident.
    - On the list display
    -- Select N number N804NZ/T862
    -- Select a date range in early July (July 1-15 for instance) You need multiple days to show the issue
    -- Go.

    Scroll down - you'll see the date displayed below Total in the first column.
    Notice that the date will be the first date in the list.
    That was added by putting DATE_FORMAT(%s, '%m/%d/%Y') in the custom calc of that element.
    --> It appears you simply put what I entered here in the value list of the query for the list and replace %s with the field name.

    If you now group by "Flight Date" you will see that each group shows the same (1st) flight date of the list, not of the group.
    What I want to show is the flight date of that GROUP of record.

    In the back end the element is:
    Flight Date in group Daily Log SEAT. (It is a child, of another group - you can edit it or unlink it)

    -- If you use "Reports/SEAT Reports/Seat Daily Cost Summary - Incident - Collapsed". Follow the same filter/group steps. Nothing will display till you "group"

    You will see what I'm trying to achieve. I'm trying to display just the "calcs" row of this of each group on this list, but I need the 'non-calculated' elements to show as well. I got it working by making a custom template and hardcoding element names in there. It is a nasty hack of a solution - but it works.

    So if you are doing what I'm thinking, I'm not sure of any SQL formula I can put in the "value" list that will do what I want, but I haven't looked hard.


Share This Page