• 1

Read this first!

We do not monitor these forums. The forum is provided to exchange information and experience with other users ONLY. Forum responses are not guaranteed.

However, please submit a ticket if you have an active subscription and wish to receive support. Our ticketing system is the only way of getting in touch with RSJoomla! and receiving the official RSJoomla! Customer Support.

For more information, the Support Policy is located here.

Thank you!

TOPIC: Add Column Total in Submission Directory - SOLVED

Add Column Total in Submission Directory - SOLVED 10 years 3 months ago #29897

  • yaani
  • yaani's Avatar
  • OFFLINE
  • Senior Boarder
  • Posts: 41
  • Thank you received: 4
I have an event registration form here www.racekarts. com/index.php?option=com_rsform&formId=4 (remove spaces before .com))

I have a menu item linked to the submissions directory here www.racekarts. com/index.php?option=com_rsform&view=directory&Itemid=670&lang=en (remove spaces before .com))

I would like to add a total at the bottom of some of the columns i.e. the Total Fee column & Pit Pass column to give a grand total of all Fees and a grand total of all Pit Passes for all the form submissions for an event.

Can that be done in the Manage Directories>PHP Scripts> Scripts called on listing layout ?

Is there a an example of this done that I could look at or could someone give me a clue on how to get started figuring out the code to accomplish this?

Thank you for any pointers you can give!
Last Edit: 10 years 3 months ago by yaani.
The administrator has disabled public write access.

How To Add Column Total in Submission Directory? 10 years 3 months ago #30008

  • yaani
  • yaani's Avatar
  • OFFLINE
  • Senior Boarder
  • Posts: 41
  • Thank you received: 4
I think I have the SQL command to calculate a total

SELECT SUM( `FieldValue` )
FROM `gcki_rsform_submission_values`
WHERE (
`FormId` =4
AND `FieldName` = 'total'
)

Can someone tell me what I need to put in the Manage Directories>PHP Scripts to display the total (at the end of a column row in the directories listing in the front end) in the "Scripts called on listing layout" text box?
The administrator has disabled public write access.

How To Add Column Total in Submission Directory? 10 years 3 months ago #30009

  • yaani
  • yaani's Avatar
  • OFFLINE
  • Senior Boarder
  • Posts: 41
  • Thank you received: 4
I think I am getting closer.

I have this code in the Manage Directories>PHP Scripts>Scripts called on listing layout box

//<code>
$db = JFactory::getDbo();
$db->setQuery("
SELECT SUM( `FieldValue` ) AS TotalIncome
FROM `t2zt1_rsform_submission_values`
WHERE (
`FormId` =4
AND `FieldName` = \'total\'
)");
echo 'Total Income';
//</code>

Now I just need to know what makes the total value display in teh submissions directory. Right now it is displaying "Total Income" and I don't know how to make it display the TotalIncome field that is calculated.
The administrator has disabled public write access.

How To Add Column Total in Submission Directory? 10 years 3 months ago #30080

  • yaani
  • yaani's Avatar
  • OFFLINE
  • Senior Boarder
  • Posts: 41
  • Thank you received: 4
Tech Support helped me with this one.

The following code worked for me in the Manage Directories>PHP Script>Scripts called on listing layout
$db = JFactory::getDBO();
 
$db->setQuery("SELECT SUM((`FieldValue`) * 5) FROM prefix_rsform_submission_values WHERE `FieldName`='passes' AND `FormId`='4'");
$directoryLayout.="  Total Passes: " . "$" . $db->loadResult() . "<br/>";
 
$db->setQuery("SELECT SUM((`FieldValue`)* 10)FROM prefix_rsform_submission_values WHERE `FieldName`='Trans' AND `FormId`='4'");
$directoryLayout.="    Total Transponder Rentals: " . "$" . $db->loadResult() . "<br/>";
 
$db->setQuery("SELECT SUM(`FieldValue`) FROM prefix_rsform_submission_values WHERE `FieldName`='total' AND `FormId`='4'");
$directoryLayout.="Grand Total Fees: " . "$" . $db->loadResult() . "<br/>";

It placed the calculated totals at the bottom of the submission directory. If you want a feature built in to give column totals be sure to vote for the new feature here
www.rsjoomla.com/feedback/single-feedbac...ssion-directory.html
Last Edit: 10 years 3 months ago by yaani.
The administrator has disabled public write access.

How To Add Column Total in Submission Directory? 9 years 11 months ago #31248

  • Gerard.Verheij
  • Gerard.Verheij's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 12
  • Thank you received: 4
Nice solution!

I have eight value-fields (they are called "pat_xxxx") for which I calculated the totals below the directory.

I added some styling and put the totals in a <table> with a <th>-heading. Off course you have to replace my fields with your field-names and the prefix of your database-table. You can adjust the width of the most-left and most-right heading-cells to position the totals nicely below the columns...

$db = JFactory::getDBO();
 
$db->setQuery("SELECT SUM(`FieldValue`) FROM <PREFIX>_rsform_submission_values WHERE `FieldName`='pat_pczwnl' AND `FormId`='27'");
$directoryLayout.='<table class="table table-condensed table-striped category directoryTable"><thead><tr>
							<th align="left" class="left directoryHead" width="305px">Totals : </th>
							<th align="center" class="center directoryHead"><a href="#">PCzwNL</a></th>
							<th align="center" class="center directoryHead"><a href="#">LUMC</a></th>
							<th align="center" class="center directoryHead"><a href="#">Amphia</a></th>
							<th align="center" class="center directoryHead"><a href="#">JBZ</a></th>
							<th align="center" class="center directoryHead"><a href="#">EMC</a></th>
							<th align="center" class="center directoryHead"><a href="#">Verbeeten</a></th>
							<th align="center" class="center directoryHead"><a href="#">SFG</a></th>
							<th align="center" class="center directoryHead"><a href="#">Overige centra</a></th>
							<th align="center" class="center directoryHead"><a href="#">Totaal</a></th>
						<th align="center" class="center directoryHead" width="70px">&nbsp;</th>
		</tr>
	</thead>
<tbody><tr><td align="left" class="left directoryCol">Deelnemende patienten : </td><td align="center" class="center directoryCol">' . $db->loadResult() . "</td>";
 
$db->setQuery("SELECT SUM(`FieldValue`) FROM <PREFIX>_rsform_submission_values WHERE `FieldName`='pat_lumc' AND `FormId`='27'");
$directoryLayout.='<td align="center" class="center directoryCol">' . $db->loadResult() . "</td>";
 
$db->setQuery("SELECT SUM(`FieldValue`) FROM <PREFIX>_rsform_submission_values WHERE `FieldName`='pat_amphia' AND `FormId`='27'");
$directoryLayout.='<td align="center" class="center directoryCol">' . $db->loadResult() . "</td>";
 
$db->setQuery("SELECT SUM(`FieldValue`) FROM <PREFIX>_rsform_submission_values WHERE `FieldName`='pat_jbz' AND `FormId`='27'");
$directoryLayout.='<td align="center" class="center directoryCol">' . $db->loadResult() . "</td>";
 
$db->setQuery("SELECT SUM(`FieldValue`) FROM <PREFIX>_rsform_submission_values WHERE `FieldName`='pat_emc' AND `FormId`='27'");
$directoryLayout.='<td align="center" class="center directoryCol">' . $db->loadResult() . "</td>";
 
$db->setQuery("SELECT SUM(`FieldValue`) FROM <PREFIX>_rsform_submission_values WHERE `FieldName`='pat_verbeeten' AND `FormId`='27'");
$directoryLayout.='<td align="center" class="center directoryCol">' . $db->loadResult() . "</td>";
 
$db->setQuery("SELECT SUM(`FieldValue`) FROM <PREFIX>_rsform_submission_values WHERE `FieldName`='pat_sfg' AND `FormId`='27'");
$directoryLayout.='<td align="center" class="center directoryCol">' . $db->loadResult() . "</td>";
 
$db->setQuery("SELECT SUM(`FieldValue`) FROM <PREFIX>_rsform_submission_values WHERE `FieldName`='pat_other' AND `FormId`='27'");
$directoryLayout.='<td align="center" class="center directoryCol">' . $db->loadResult() . "</td>";
 
$db->setQuery("SELECT SUM(`FieldValue`) FROM <PREFIX>_rsform_submission_values WHERE `FieldName`='pat_totaal' AND `FormId`='27'");
$directoryLayout.='<td align="center" class="center directoryCol">' . $db->loadResult() . '</td><td align="center" class="center directoryCol"></td></tr></tbody></table>';


Thanks, Gerard
The administrator has disabled public write access.
The following user(s) said Thank You: yaani, bluesconcert

How To Add Column Total in Submission Directory? 6 years 11 months ago #38212

  • bluesconcert
  • bluesconcert's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 6
  • Thank you received: 1
I tried using the above as an example, but a blank page is returned, this is my code.... It works in PHPMyAdmin. Anyone know what's wrong with it?

I want to count, number of submissions (Voornaam) and calculate the total amount to be paid (Bedrag)

$db = JFactory::getDBO();

$db->setQuery("SELECT COUNT(`FieldValue`) FROM prefix_rsform_submission_values WHERE `FieldName`='Voornaam' AND `FormId`= 13");
$directoryLayout.=" Totaal inschrijvingen: " . "$" . $db->loadResult() . "<br/>";

$db->setQuery("SELECT SUM(`FieldValue`)FROM prefix_rsform_submission_values WHERE `FieldName`='Bedrag' AND `FormId`= 13");
$directoryLayout.=" Totaal Bedrag: " . "$" . $db->loadResult() . "<br/>";
The administrator has disabled public write access.

How To Add Column Total in Submission Directory? 6 years 11 months ago #38213

  • bluesconcert
  • bluesconcert's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 6
  • Thank you received: 1
I would actually also like to show the totals per User ID. Not sure if that can be achieved here
The administrator has disabled public write access.

How To Add Column Total in Submission Directory? 6 years 11 months ago #38214

  • bluesconcert
  • bluesconcert's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 6
  • Thank you received: 1
I would actually also like to show the totals per User ID. Not sure if that can be achieved here
The administrator has disabled public write access.

How To Add Column Total in Submission Directory? 6 years 10 months ago #38300

  • bluesconcert
  • bluesconcert's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 6
  • Thank you received: 1
This is the wonderful solution support sent me:

$db = JFactory::getDBO();
$user = JFactory::getUser();
//echo $user->id;
$db->setQuery("SELECT SUM(rsv.`FieldValue`) FROM #__rsform_submission_values AS rsv LEFT JOIN #__rsform_submissions AS rs ON rsv.SubmissionId = rs.SubmissionId WHERE rsv.`FieldName`='Total' AND rs.`UserId`='".$user->id."' AND rsv.`FormId`='5'");
$directoryLayout .= 'Subtotal: '.$db->loadResult();

This script uses a LEFT JOIN to specify another table where the UserId is actually available.

Note that you will have to replace 5 with the id of your form.
This works!!
The administrator has disabled public write access.
The following user(s) said Thank You: yaani

How To Add Column Total in Submission Directory? 6 years 1 month ago #38926

  • jabaleva
  • jabaleva's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 3
Hi! I have a similar situation and applied the solution suggested here.

My form asks employees for the total number of kilometers they have traveled = Kilometers.
In the Form Properties > Calculations, it makes a computation of the total travel reimbursement:
TOTAAL = Kilometers * €0,19

In the Directory listing, I tried to create a SUM of the TOTAALs, inserting the follwoing in the Scripts called on Listing Layout
$db = JFactory::getDBO();
$db->setQuery("SELECT SUM(`FieldValue`) FROM PREFIX_rsform_submission_values WHERE `FieldName`='TOTAAL' AND `FormId`='14'");
$directoryLayout.="TOTAAL reiskosten vergoeding: " . "€ " . $db->loadResult() . "<br/>";
The result however adds only the whole numbers, it does not add the decimal numbers. For example, for the following values:
FormIdFiedlNameFiedlValue
14TOTAAL4,75
14TOTAAL7,98
14TOTAAL5,13
14TOTAAL9,88

I would get a "TOTAAL reiskosten vergoeding" of 25 instead of 27,74

Did you also get the same results from your totals?

Thanks in advance for any help!
Last Edit: 6 years 1 month ago by jabaleva.
The administrator has disabled public write access.

Add Column Total in Submission Directory - SOLVED 4 years 11 months ago #40181

  • rubenl
  • rubenl's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 13
Hi,

In current corona time we need our visitors to make a reservation on date. I made a form with a date field (Datum) and a dropdown (Aantal) with how many people they want to visit us. I would like to count the people in the reservations on each date, would also be fine if it's a calculation of search result on date. Anybody a suggestion how to to this?
The administrator has disabled public write access.
  • 1

Read this first!

We do not monitor these forums. The forum is provided to exchange information and experience with other users ONLY. Forum responses are not guaranteed.

However, please submit a ticket if you have an active subscription and wish to receive support. Our ticketing system is the only way of getting in touch with RSJoomla! and receiving the official RSJoomla! Customer Support.

For more information, the Support Policy is located here.

Thank you!