Achieving Advanced Data Insights: One-to-Many Table Relationships and Field Calculations

[@imranminhas1947]

Thanks! you made my day; I was looking for such approach. what a gr8 video and very useful information for a beginner someone like me. Thanks!

[@PHPRunnerTipsAndTricks]

Thank you very much. You are most welcome.

[@TheAlgonmusic]

I hope you and your family are ok as no new videos lately Thanks for giving us this “amazing “ tutorials thanks very much

[@PHPRunnerTipsAndTricks]

Yes, I know. Tutorials are not the only thing I do. I wish it were. Nevertheless, I am working on more content.

[@TheAlgonmusic]

Good to know you are ok 👍

[@rizwanmtc]

dear thank for this video, now you are moving from basic to advance level topics in PHPRUNNER.

[@PHPRunnerTipsAndTricks]

You are welcome

[@robgee7149]

Thank you, I really appreciate your video. I implemented it into a new project. A problem I've found though is that, sometimes the sums are wrong. I have a record with a unit price of 25.83. I set a qty of 4. The sums on screen are perfect there in the unsubmitted record (103.32). When I open up the record again, the total is 103.30. Any idea as to why it works round down the total in the saved record? Thanks Rob

[@PHPRunnerTipsAndTricks]

It could be that the database field is created for one decimal only. Or something similar. Perhaps a setting in PHPRunner. The number of decimals.

[@robgee7149]

Solved it. My fields were only 5 character varchar . Upped them to 8 character and voila, worked perfectly.

[@PHPRunnerTipsAndTricks]

@@robgee7149 I would go for double or decimal 11,2. But if it's working then it's probably fine.

[@mitro922]

Very good 👍

[@PHPRunnerTipsAndTricks]

Thank you very much

[@swansidepaul]

Thank you so much. I can get it working by copying the tables and code as in the video, but can you put some light on why when I add another field I cant get it to update? For example, I have added an extra field called VATAmount. Now when I update the order_details, the original Order Total does nothing, but my new field VATAmount updates. So I am guessing what do I need to find to ensure the correct values go into the correct field? // Update order_totals var orderDetailsTable = 'order_details'; function setOrderTotal ( value, pageObj ) { console.log(value); //update totals. Field in Master Table. if(value > 0){ document.getElementById("value_order_total_1").value = parseFloat(value).toFixed(2); }else{ document.getElementById("value_order_total_1").value = 0.00; } } function updateOrderTotal( pageObj ) { var detailsPage = pageObj.getDetailsPage(orderDetailsTable); if( !detailsPage ) return; var records = detailsPage.getAllRecords(); // actual calculation. Multiply Quantity by UnitPrice and sum the results. // total = sum( Quantity * UnitPrice ) var total = 0; records.forEach( function( r ) { total += ( parseFloat( r.getFieldValue('quantity') ) || 0 ) * ( parseFloat( r.getFieldValue('unit_price') ) || 0 ); }); setOrderTotal( total, pageObj ); } // Update VAT var orderDetailsTable = 'order_details'; function setOrderTotal ( value, pageObj ) { console.log(value); //update totals. Field in Master Table. if(value > 0){ document.getElementById("value_VAT_1").value = parseFloat(value).toFixed(2); }else{ document.getElementById("value_VAT_1").value = 0.00; } } function updateOrderTotal( pageObj ) { var detailsPage = pageObj.getDetailsPage(orderDetailsTable); if( !detailsPage ) return; var records = detailsPage.getAllRecords(); // actual calculation. Multiply Quantity by UnitPrice and sum the results. // total = sum( Quantity * UnitPrice ) var total = 0; records.forEach( function( r ) { total += ( parseFloat( r.getFieldValue('quantity') ) || 0 ) * ( parseFloat( r.getFieldValue('unit_price') ) || 0 ) * ( parseFloat( r.getFieldValue('VATAmount') ) || 0 ); }); setOrderTotal( total, pageObj ); } Thanks

[@PHPRunnerTipsAndTricks]

I can see you changed the updateOrderTotal function to include VAT amount. I don't see anything wrong here. The function setOrderTotal should have worked without any changes. Perhaps you are trying something else. I will add the same field to my project. I will let you know.

[@paulgrimes8556]

@@PHPRunnerTipsAndTricks Thank you very much.

[@paulgrimes8556]

@@PHPRunnerTipsAndTricks Hi. Did you get it to work with the extra field? I have tried a few different things but no such luck at the present

[@PHPRunnerTipsAndTricks]

@@paulgrimes8556 I have not tried it as of yet. I have a bit of a family crisis. My mom has been in a coma for three weeks already. I will get back to this ASAP

[@paulgrimes8556]

@@PHPRunnerTipsAndTricks oh I so very sorry. I hope and pray she pull through for you. Sending hugs and prayers from us

[@MiezoT]

It's great but I personally still don't like to store the total like this, to paranoid of it going out of sync when something breaks, lol. I'd probably work with more complicated queries (subqueries etc) on the master and calculate the total there on the fly and not storing this derived value, but that's just me;-)

[@PHPRunnerTipsAndTricks]

Perhaps. This example is straight from the manual. It surely cannot be that bad. xlinesoft.com/phprunner/docs/show_order_total_on_the_edit_page_as_the_details_table_is_updated.htm

[@MiezoT]

@@PHPRunnerTipsAndTricks yes of course. I'm just old school, and would - in such cases - paranoically implement some batch job for periodical consistency checks;-)

[@PHPRunnerTipsAndTricks]

@@MiezoTYes, I hear you. In a way, I am old school too. The batch job solution is a promising idea. You know it will work every time.

[@MiezoT]

@@PHPRunnerTipsAndTricks I'm not working on a phpr project at the moment, but was just wondering (you don't need to answer): can you just requery/refresh a field in the master table upon change in a child's record? Then one could do something like that with a db-trigger. Problem is always UI ;-)

[@PHPRunnerTipsAndTricks]

@@MiezoTHonestly, I don't know. I don't think you can. You will have to refresh the entire record. And that is a bad idea as you may lose input already given by the user. I will have to investigate more.

[@rizwanmtc]

i have a issue on add page a lookup fields show but hide at once please guide,

[@PHPRunnerTipsAndTricks]

Apologies. I don't understand the question.

[@rizwanmtc]

@@PHPRunnerTipsAndTricks thank do reply I just remove the table build and again add the table its now ok

[@PHPRunnerTipsAndTricks]

@@rizwanmtc Perfect.

[@mitro922]

Hi Corrie How to generate Microsoft word file using phprunner..You can make a video explaining this

[@PHPRunnerTipsAndTricks]

You can do this with jQuery. see link to plugin: www.jqueryscript.net/other/Export-Html-To-Word-Document-With-Images-Using-jQuery-Word-Export-Plugin.html

[@PHPRunnerTipsAndTricks]

Do you need more help?

[@mitro922]

@@PHPRunnerTipsAndTricks thank you very much

[@mitro922]

@@PHPRunnerTipsAndTricks It is possible to make a video showing how to work in an environment phprunner

[@leviatanMX]

EXCELENT.... BUT, i have a question.!🧐🧐🥸 How does PHPRunner control the update of related tables and wrap them in a transaction, so that everything is updated or if there are failures nothing is updated.