More than 1 calculation
-
- Veteran Member
- Posts: 101
- Joined: 2019-05-26 18:38
More than 1 calculation
Could someone give a short example of how to do a calculation on more than one field in the before update / after insert? For instance, the following works fine, but how can I add to it for another field? Or can I? part_weight is from another table called quoting, and lbs_per_m is in the existing results table.
function results_before_update(&$data, $memberInfo, &$args){
$part_weight = sqlValue(
"SELECT part_weight FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['lbs_per_m'] = $part_weight * 1000 / 16;
Thank you,
David
function results_before_update(&$data, $memberInfo, &$args){
$part_weight = sqlValue(
"SELECT part_weight FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['lbs_per_m'] = $part_weight * 1000 / 16;
Thank you,
David
Re: More than 1 calculation
Hi,
you can do as many calculation as complex as you wish. If you need some values from the DB, retrieve them via sql or sqlvalue.
The $data array hold all your field that will be updated in the DB for this table.
But you could even update via SQL any other data in your database. It's your choice. If you browse through the forum you will find lot's of examples.
you can do as many calculation as complex as you wish. If you need some values from the DB, retrieve them via sql or sqlvalue.
The $data array hold all your field that will be updated in the DB for this table.
But you could even update via SQL any other data in your database. It's your choice. If you browse through the forum you will find lot's of examples.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
-
- Veteran Member
- Posts: 101
- Joined: 2019-05-26 18:38
Re: More than 1 calculation
I tried this below, but it doesn't work. Trying to find examples, but having problems finding...
function results_before_insert(&$data, $memberInfo, &$args){
$matl_price = sqlValue(
"SELECT matl_price FROM quoting where quotingID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $matl_price;
$part_weight = sqlValue(
"SELECT part_weight FROM quoting where quotingID='{$data['quoting_ID']}'"
);
$data['lbs_per_m'] = $part_weight * 1000 / 16;
return TRUE;
}
Thank you.
function results_before_insert(&$data, $memberInfo, &$args){
$matl_price = sqlValue(
"SELECT matl_price FROM quoting where quotingID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $matl_price;
$part_weight = sqlValue(
"SELECT part_weight FROM quoting where quotingID='{$data['quoting_ID']}'"
);
$data['lbs_per_m'] = $part_weight * 1000 / 16;
return TRUE;
}
Thank you.
Re: More than 1 calculation
What is not working? Are you getting wrong results, no results, ???
Can you share your table definitions with the relations?
Can you share your table definitions with the relations?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
-
- Veteran Member
- Posts: 101
- Joined: 2019-05-26 18:38
Re: More than 1 calculation
I certainly will. Thank you! (Putting in a link to pictures) Tried to put in message, but said they are too large? Less than 500K
https://drive.google.com/drive/folders/ ... sp=sharing
David
https://drive.google.com/drive/folders/ ... sp=sharing
David
-
- Veteran Member
- Posts: 101
- Joined: 2019-05-26 18:38
Re: More than 1 calculation
Added some more pics to above link for explanation, and to clarify the above, getting no results, or 0.00. Not wrong results.
Thank you,
David
Thank you,
David
- landinialejandro
- AppGini Super Hero
- Posts: 126
- Joined: 2016-03-06 00:59
- Location: Argentina
- Contact:
Re: More than 1 calculation
Hi!, i think do you have a problem with a varibledharbitindy wrote: ↑2019-06-18 17:46Added some more pics to above link for explanation, and to clarify the above, getting no results, or 0.00. Not wrong results.
Thank you,
David
try this code after return, debug and check the result in browser
Code: Select all
ob_start();
print_r($data);
print_r($matl_price);
print_r($part_weight);
$c=ob_get_clean();
echo "<pre>".htmlspecialchars($c)."</pre>";
Alejandro.
AppGini 5.98 - Linux OpenSuse Tumblewweed.
Some of my posts that may interest you:
Landini Admin Template: Template for Appgini like AdminLTE
Profile image plugin: add and changue image user profile
Field editor in table view: Configurable fast edit fields in TV
my personal page
AppGini 5.98 - Linux OpenSuse Tumblewweed.
Some of my posts that may interest you:
Landini Admin Template: Template for Appgini like AdminLTE
Profile image plugin: add and changue image user profile
Field editor in table view: Configurable fast edit fields in TV
my personal page
-
- Veteran Member
- Posts: 101
- Joined: 2019-05-26 18:38
Re: More than 1 calculation
Hi, I put this in, but not seeing anything new in browser?
function results_before_insert(&$data, $memberInfo, &$args){
$part_weight = sqlValue(
"SELECT part_weight FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['lbs_per_m'] = $part_weight * 1000 / 16;
$matl_price = sqlValue(
"SELECT matl_price FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $matl_Price;
return TRUE;
ob_start();
print_r($data);
print_r($matl_price);
print_r($part_weight);
$c=ob_get_clean();
echo "<pre>".htmlspecialchars($c)."</pre>";
}
Thank you,
David
function results_before_insert(&$data, $memberInfo, &$args){
$part_weight = sqlValue(
"SELECT part_weight FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['lbs_per_m'] = $part_weight * 1000 / 16;
$matl_price = sqlValue(
"SELECT matl_price FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $matl_Price;
return TRUE;
ob_start();
print_r($data);
print_r($matl_price);
print_r($part_weight);
$c=ob_get_clean();
echo "<pre>".htmlspecialchars($c)."</pre>";
}
Thank you,
David
Re: More than 1 calculation
Hi,
I think the issue is because you try to get the matl_price from the quoting table, but instead the matl_price in the quoting table is a lookup to the materials table. So you need actually to pick the matl_price from the materials table.
so you may try this
I think the issue is because you try to get the matl_price from the quoting table, but instead the matl_price in the quoting table is a lookup to the materials table. So you need actually to pick the matl_price from the materials table.
so you may try this
Code: Select all
$from = get_sql_from('quoting');
$matl_price = sqlvalue("SELECT price_lb from ".$from." and quoting.quoting_ID='{$data['quoting_ID']}'");
$data['matl_cost'] = $data['lbs_per_m'] * $matl_Price;
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
-
- Veteran Member
- Posts: 101
- Joined: 2019-05-26 18:38
Re: More than 1 calculation
I was wondering if that may be what the problem was, but wasn't sure how to structure the code. Thanks so much, and I'll give it a whirl and see what happens. I really appreciate you taking the time to look at this for me, and I have no problem paying for your time at some point if you are interested. I think that once I get familiar with how the code should be structured, I'll have way fewer questions. I come from a VB.net world and I'm just not familiar with the PHP, Java, Ajax, etc., but again, thank you!
David
David
- landinialejandro
- AppGini Super Hero
- Posts: 126
- Joined: 2016-03-06 00:59
- Location: Argentina
- Contact:
Re: More than 1 calculation
i'm sorry is before return...dharbitindy wrote: ↑2019-06-19 00:06Hi, I put this in, but not seeing anything new in browser?
function results_before_insert(&$data, $memberInfo, &$args){
$part_weight = sqlValue(
"SELECT part_weight FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['lbs_per_m'] = $part_weight * 1000 / 16;
$matl_price = sqlValue(
"SELECT matl_price FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $matl_Price;
return TRUE;
ob_start();
print_r($data);
print_r($matl_price);
print_r($part_weight);
$c=ob_get_clean();
echo "<pre>".htmlspecialchars($c)."</pre>";
}
Thank you,
David
Alejandro.
AppGini 5.98 - Linux OpenSuse Tumblewweed.
Some of my posts that may interest you:
Landini Admin Template: Template for Appgini like AdminLTE
Profile image plugin: add and changue image user profile
Field editor in table view: Configurable fast edit fields in TV
my personal page
AppGini 5.98 - Linux OpenSuse Tumblewweed.
Some of my posts that may interest you:
Landini Admin Template: Template for Appgini like AdminLTE
Profile image plugin: add and changue image user profile
Field editor in table view: Configurable fast edit fields in TV
my personal page
-
- Veteran Member
- Posts: 101
- Joined: 2019-05-26 18:38
Re: More than 1 calculation
Unfortunately that didn't work either. What I did do was to rename the matl_price field to price_lb like the materials table and surprisingly, I did get at least a number in the matl_cost field in the results table. Here is the weird part. The calculated number that shows in the results is the price_lb * the record number??? I'm now wondering that maybe I need to erase the mysql database and re-input the data? I'm not sure what is going on with the data. This is only after I changed the code back to this below. The above code with the $matl_price showed $0.00 like before, is when I decided to try "like fields" between the materials table and the quoting table. I did also delete all data (in localhost) and re-enter before saving to see if that was causing issues. I'm sure that I'm missing something obvious, but I just can't see it at the moment?pböttcher wrote: ↑2019-06-19 07:35Hi,
I think the issue is because you try to get the matl_price from the quoting table, but instead the matl_price in the quoting table is a lookup to the materials table. So you need actually to pick the matl_price from the materials table.
so you may try this
Code: Select all
$from = get_sql_from('quoting'); $matl_price = sqlvalue("SELECT price_lb from ".$from." and quoting.quoting_ID='{$data['quoting_ID']}'"); $data['matl_cost'] = $data['lbs_per_m'] * $matl_Price;
$price_lb = sqlValue(
"SELECT price_lb FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $price_lb;
Thanks again,
David
-
- Veteran Member
- Posts: 101
- Joined: 2019-05-26 18:38
Re: More than 1 calculation
I've attached a picture showing the new results to elaborate on my last post.
Thank you,
David
Thank you,
David
- Attachments
-
- ResultsCalc.JPG (128.36 KiB) Viewed 6964 times
-
- Veteran Member
- Posts: 101
- Joined: 2019-05-26 18:38
Re: More than 1 calculation
The above picture didn't show the "SELECT" statement.
$price_lb = sqlValue(
"SELECT price_lb FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $price_lb;
$price_lb = sqlValue(
"SELECT price_lb FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $price_lb;
-
- Veteran Member
- Posts: 101
- Joined: 2019-05-26 18:38
Re: More than 1 calculation
I cannot say enough good about pböttcher. Super helpful, and knowledgeable. Fortunate to have him here in the forums!
Best regards,
David
Best regards,
David
Re: More than 1 calculation
Hi there, with appgini 5.51
I've done this on the hooks file, for your refference I have an invoice with several lines which are filled on each new invoice or modification
function invoices_after_update($data, $memberInfo, &$args){
$data['T1'] = $data['C1'] * $data['V1'];
$data['T2'] = $data['C2'] * $data['V2'];
$data['T12'] = $data['T1'] + $data['T2'];
$data['T3'] = $data['C3'] * $data['V3'];
$data['T4'] = $data['C4'] * $data['V4'];
$data['T34'] = $data['T3'] + $data['T4'];
$data['I4Val'] = ($data['T34'] * $data['I4Porc'] /100);
$data['T5IVA'] = ($data['T34'] + $data['I4Val']);
$data['TotalFac'] = $data['T12'] + $data['T5IVA'];
$data['InvMarg100'] = (($data['T12'] + $data['T34'] -$data['InvMargen']) / ($data['T12']+$data['T34'])) * 100 ;
if (('td.invoices-FechaPago') == '01/01/1900'){
UpdateAgingNotPaid ("");
}
else{
UpdateAgingPaid ("");
};
Year("");
Month("");
Quarter("");
return TRUE;
}
Hope this helps and work for you!
I've done this on the hooks file, for your refference I have an invoice with several lines which are filled on each new invoice or modification
function invoices_after_update($data, $memberInfo, &$args){
$data['T1'] = $data['C1'] * $data['V1'];
$data['T2'] = $data['C2'] * $data['V2'];
$data['T12'] = $data['T1'] + $data['T2'];
$data['T3'] = $data['C3'] * $data['V3'];
$data['T4'] = $data['C4'] * $data['V4'];
$data['T34'] = $data['T3'] + $data['T4'];
$data['I4Val'] = ($data['T34'] * $data['I4Porc'] /100);
$data['T5IVA'] = ($data['T34'] + $data['I4Val']);
$data['TotalFac'] = $data['T12'] + $data['T5IVA'];
$data['InvMarg100'] = (($data['T12'] + $data['T34'] -$data['InvMargen']) / ($data['T12']+$data['T34'])) * 100 ;
if (('td.invoices-FechaPago') == '01/01/1900'){
UpdateAgingNotPaid ("");
}
else{
UpdateAgingPaid ("");
};
Year("");
Month("");
Quarter("");
return TRUE;
}
Hope this helps and work for you!