display total in invoice list page

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
bass115
User
Posts: 28

display total in invoice list page

Post by bass115 »

Hello all,

I am developing invoice system and i have two tables:
1- invoice: contains ( invoiceID, Date and customerName) i added custom field called total
2- invoiceDetail: contains (invoiceID, itemName, Quantity and price) also, i added custom field for sub-total and it works fine.

how can i sum(sub-total) for each invoice in list page?

e.g.
InvoiceID | Date | Customer Name

2 | 1/1/2017 | Sarah

Item | Quantity | Price | sub-total
Pen | 4 | 10$ | 40

Paper | 6 | 20$ | 120

after i insert above invoice it will redirect to list invoice page
i want to display invoice total in the list invoice page as custom field.

what should i do?


mobhar
User
Posts: 11660

Post by mobhar »

Why didn't you use "TOTAL" from "Fields" setup -> "List Page" -> "Aggregate" to calculate the sum/total of the numeric field (including Custom Field)?

For the value in "Sub-Total" field, then you may simply refer to the demo project, see the "orderdetails" table -> "SubTotal" field. That demo project also uses Custom Field for "SubTotal" field.


bass115
User
Posts: 28

Post by bass115 »

thank mr. mobhar,

actually i did what u said in invoice_detail page and it works fine but i want to display in master table which is invoice(( invoiceID, Date and customerName) i want to add custom field called total and it will sum(sub-total).

what should i do?


mobhar
User
Posts: 11660

Post by mobhar »

You may simply use "Row_Rendered" server event that belongs to the master table, to execute the SQL to calculate the SUM of Sub-Total field of detail records based on "invoiceID" field.

Then, you may simply display the result using "ViewValue" property that belongs to that Custom Field in your master table in that server event above.


bass115
User
Posts: 28

Post by bass115 »

excellent ,
can u tell me what should i enter in "Row_Rendered" that belongs to the master table, to execute the SQL to calculate the SUM of Sub-Total ?

best regards,


mobhar
User
Posts: 11660

Post by mobhar »

For example:

// adjust "my_detail_table" to your actual detail table name, adjust SubTotal
$val = ew_ExecuteScalar("SELECT SUM(SubTotal) FROM my_detail_table WHERE invoiceID = " . $this->invoiceID->CurrentValue);
$this->Total_Detail->ViewValue = $val; // assume the Custom Field name in your master table is "Total_Detail"


bass115
User
Posts: 28

Post by bass115 »

great,

it's working now and i can render total in master page but also i get this error:

Failed to execute SQL. Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

can u please advise?


mobhar
User
Posts: 11660

Post by mobhar »

Try:

if ($this->invoiceID->CurrentValue != "") {
$val = ew_ExecuteScalar("SELECT SUM(SubTotal) FROM my_detail_table WHERE invoiceID = " . $this->invoiceID->CurrentValue);
$this->Total_Detail->ViewValue = $val; // assume the Custom Field name in your master table is "Total_Detail"
} else {
$this->Total_Detail->ViewValue = "0"; // assume the Custom Field name in your master table is "Total_Detail"
}


bass115
User
Posts: 28

Post by bass115 »

excellent it solved and now i can render the total in list invoice page.

one last question in this subject.
how can i render total in add page? we solved in list page :)


mobhar
User
Posts: 11660

Post by mobhar »

For "Add Page" and "Edit Page", you may simply use "EditValue" instead of "ViewValue" property.


bass115
User
Posts: 28

Post by bass115 »

i go to server events and i select invoice table >> then i select add/copy page >> then i select page_load
then i insert below code: just i changed as u said from "EditValue" instead of "ViewValue" property but it does not work :)

// Page Render event
function Page_Render() {
//echo "Page Render";
$val = ew_ExecuteScalar("SELECT SUM(sub_total) FROM invoice_item WHERE invoice_id =" . $this->invoice_id->CurrentValue);
$this->total->EditValue = $val; // assume the Custom Field name in your master table is "Total_Detail"
}

am i in right direction and steps?

and thank you in advance


mobhar
User
Posts: 11660

Post by mobhar »

It won't work in Add Page, because there is still no value of "invoice_id" at the moment. You should try it in Edit Page.


riverman
User
Posts: 158
Location: Stockholm/Sweden

Post by riverman »

In this case you need to use a client script like JavaScript/JQuery.

Here is an example where I read the sum of each detail row and store the total sum in field OrderSum:

#forderadd: ID of form to sum (check in browser with inspection). This is different between Add and Edit
[data-field =x_Sums]: data-field parameter in input statement ex.<input type="text" ... data-field="x_Sums"...> of detail fields
#x_OrderSum: ID of field to store sum in

////////////////////////////////////
// Calculate total sum of order...
////////////////////////////////////
var $form = $('#forderadd'), //ID of form to monitor
$summands = $form.find('[data-field =x_Sums]'), //Element: data-field should be x_Sums
$sumDisplay = $('#x_OrderSum'); //ID of input field to present the result

//Forms id: id="forderadd" ...
$form.delegate('.form-control', 'change', function ()
{
var sum = 0;

//For each datafield=x_Sums
$summands.each(function ()
{

var value = $(this).val();

//Change data type to float...
value = parseFloat(value);

//If valid number add to sum
if (!isNaN(value)) sum += value;
});

//Store sum to field
$sumDisplay.val(sum);
});

xSum is the field with sum of quantity and itemprice for each detail row. The sum of each row is summed in Client-Side Events in field Quantity and ItemPrice:

{
"change keyup": function(e) {
var $row = $(this).fields();
var st = ($row["ItemPrice"].toNumber() *
$row["Quantity"].toNumber()) -
$row["Discount"].toNumber();
$row["Sums"].value(st);
}
}

I'm novice in JavaScript/JQuery but manage to make this work. Use it as inspiration...


Post Reply