[ SOLVED ] How to create a running total list based on lines from 2 lists

[nusilmar]

Confusing to explain, hopefully not hard to understand.

I have the following :

A list where i create a support pack in hours for a client ( client A buys me a pack of 10 support hours) , this is in my contracts list ( id, date_time, customer ( databasejoin to my customer list) , date of contract, expiration date, type of package ( databasejoin to my package list), hours (cascading dropdown on package list), expires in ( calc field).

A list where i create service sheets made on a client ( Tech A goes to Client A and makes a tech job during 2 hours) , this is in my service sheet list ( id, date_time, tech ( dbjoin to tech list) , customer ( dbjoin to customer list) , hours spent.

What i intend to do is to have a running total to where i can track hours bought and hours spent , just like this ( but done automaticaly) :

Customer A , Contract XXX , 30 hours , Total 30
Customer A, Service XXX, 2 hours , Total 28
Customer A, Service XXY, 3 hours , Total 25
Customer A, Service XYY, 18 hours, Total 7
Customer A Contract XXY, 10 hours, Total 17

If this could be done, it would be wonderfull, on alternative it could be added a calc field on customers list with that value ( for witch i need help also)

Thanks in advance

 

[genyded]

What I would do is have contract_hours, service_hours, and total_hours (or available_hours or whatever you want to call it) in it's own table/list joined to the others on customer_id. Set total to contract-service (contracts value minus service value).

Then is a customer buys service, increase that value. If service is performed for that customer, decrease that one.

 

[nusilmar]

Will try that, hopefully i understood everything. Will get back soon reporting my attempts and explaning everything as usual on the wiki in HERE

 

[nusilmar]

So sorry , been scratching my head trying to figure it out on how to do it but still haven't found the solution. Any help ??

For better understanding will post my current lists/forms and elements, so that anyone could help me on how to do this.

Have 2 Lists with some kind of hour recording, Contracts and Service Sheets.

On Contract List : Elements : ( id and date), contract number ( field ) , Contract Type ( dbjoin to a Contract Type List), Customer ( dbjoin to Customer List), StartDate ( date ) , EndDate ( date) , Hours ( this field is autofill in the form from the value existing in Contract Type List), Expires in ( Calc field ( EndDate-today)

On Service Sheets : (id and date) , SheetDate ( date , for manualy put real date of sheet, as it may be diferent of today date), SheetNumber ( field ), Technician ( dbjoin on Technicians List), HoursSpent ( Field), Customer ( dbjoin on Customer List), Report ( textarea).

Following your advice i've made an adicional List with the following :

Hours List : ( id and date) , Customer ( field) , Contract Hours ( field ) , Service Hours ( field ) , Total Hours ( Calc on Contract Hours - Service Hours )

This is what i have now, but how do i feed data into Hours List from the data in Contract List and Service Sheets List ?

On a side note : every list/form or element in here and more are being put in the wiki , on real life examples ( Click here to go there )

 

[genyded]

Hi,

Sound like you are on the right track... you want to create joins from the customer id in the customer table to the customer id in the contract table AND on the service id in the service table to the service id in the contact table. There is a tutorial on one to many relationships/joins here

Then as the hours are updated either by a customer buying a contract or a service tech providing service, if you make the total field a calc element in the contract table and set it's value to contract-service... it should auto-update as the hours change.

The joins should allow you to link the service hours element in your service form and the contract in your customer form similar to what is detailed in the tutorial above (just different data).

 

[nusilmar]

I do understand what you are saying but the problem, for me at least, is that there is NO conection between Contracts and Services, They are completly seperated because there can be Services made to Customers without contracts. Can you please thoroughly explain on how this could be done ?? Just because if i add Joins then i'll add stuff to my current lists that i don't want. My idea was to create a PHP code that could insert the hours value into the hours list.

My idea was to make something like this :
Someone creates a new contract to a customer ( eg : Contract n?1 , with a 10 hour pack for services),
then an php code would do this : if (customer is null in HOURS_list) then (insert in hours list set hours_customer = contract_customer, hours_contract=contract_hours) else (update hours_list on hours_customer=contract_customer set hours_contract=hours_contract+contract_hours)
Someone creates a new Service Sheet to a customer ( eg : Service n?1, Tech Suport, 2 Hours spent) then a php code would do this : if (customer is null in HOURS_list) then (insert in hours list set hours_customer = contract_customer, hours_service=service_hours) else (update hours_list on hours_customer=contract_customer set hours_service=hours_service+service_hours)

This make believe php/sql sums up my idea and in my head it makes sense, don't know if it could be done. On the other hand there could be another way to make this that i'm not seeing clearly , and thats because i would want for you to explain me a little better ( sorry, good on sql , not so good on fabrik or php)

 

[genyded]

If any join does not have an associated "key" it should return NULL (nothing)... so it is feasible/possible to have a table joined to 2 other tables and only have a row associated with one of the others. Sorry - can't teach you PHP, SQL, and Fabrik/Joomla in a forum post... but if you need specific things answered... we'll try

 

[nusilmar]

I did what you told and it worked partialy. I did get the contract hours and service hours to show in hours list but when i add another contract or another service it just overides the previous value. What i set up , and correct me if i did something wrong was :

On my contract list add a relation between contract_customer and hours_customer ( Left join ) No Repeat , and a relation between contract_hours and hours_contract ( also left join with no repeat) and the view mode was single line. On my services list did the same but with service_hours instead.

If i allow repeat i get errors 500 and 1640, if i set view to fuse lines or fuse lines and shrink data i get errors 500.

So if i stick with the partialy working setup i get the following :

New Contract , 10 hours on customer 1 : It shows on Hours Table : Customer 1, Contract_hours 10 , Service_hours null , Total Hours 10
New Service , 2 hours spent on customer 1 : It shows on Hours Table : Customer 1, Contract_hours 10 , Service_hours 2 , Total Hours 8
New Service, 1 hour spent on customer 1 : It shows on Hours Table : Customer 1, Contract_hours 10 , Service_hours 1 , Total Hours 9
New Contract, 15 hours on customer 1 : It shows on Hours Table : Customer 1, Contract_hours 15 , Service_hours 1 , Total Hours 14

I hope you get it , and i also hope you ( or anyone else) can help me figure this out.

 

[genyded]

Cool...

Now just add some "dummy" elements called "current hours" or whatever (you can set them to not save in the db if you want). Make those visible on the form... then set the one(s) you are storing the total values to hidden and make them calcs with values of current value plus or minus the ones from your new "dummy" elements. Hope that makes sense...

 

[nusilmar]

Will try that. Although i'm not feelling to confortable with this approach because it forces me to add hours groups in contract and services forms, making aditional information to show in those forms, and for presentation for the user it doesn't appears right.

 

[nusilmar]

Still not working but getting somewhere ...

This is what i'ved done :

Created Dummy_Contract and Dummy_Service on Hours List, changed the relation on Contracts List and Services List. created a Calc on Hours_contract and on Hours_Service.

Now i get the calculation right. The problem is that every time i go to a new contract or to a new Service the Hours_contract and the Hours_Service keeps getting duplicated. if i set Dummy values to disabled ( to don't save in db) the value is set to zero

And there is still the problem with the group showing in Contracts and Services Forms

 

[nusilmar]

i'm trying a diferent approach, i almost have what i want but don't know how to implement it in fabrik.
Instead of doing all in fabrik i'ved created a view with a sum for contracts , a view with a sum for services and a view with a calc of total hours ( sumcontracts - sumservices). In mysql is working fine but when i add it to fabrik list i get a 500 error ( doesn't know table name). What could be wrong ??
First i thought it would be the sum, so i'ved changed the view to show the values without the sum and then try to group by customer inside fabrik but i still get 500 errors. And i did assign a PK on the view list in fabrik.

I've created 2 views in mysql, one for contracts ( customer, contracthours) and another for services ( customer, hours spent) and i did managed to put them in fabrik through a list. I'm now trying to figure out how can i use this information so that i could join both tables and make a calculation ( contract hours minus service hours ) for the available hours.

Any help ??

On a side note : genyded, have you found any awnsers on what you try and tested on my test site ??

 

[nusilmar]

A very big Welcome to genyded , who helped me and found a solution to my problem. I had also found a solution but genyded's solution is far better and cleaner than mine ( my way needed to create views in mysql and create a schedule).

So the solution found by genyded was to add a php plugin on the forms that added or subtracted hours to the hours available for a customer.

Added a hours field to customer, then whenever a contract is made or a service is made that value is changed by a php plugin on the form of those options.

So, on contract form it was added this ( php plugin) :

$customer = $formModel->formData['fab_contract___customer_raw'][0];
$hours = $formModel->formData['fab_contract___hours'];
$db = JFactory::getDbo();
$query = $db->getQuery(true);
// Fields to update.
$fields = array(
    $db->quoteName('current_hours') . ' = current_hours + ' . $db->quote($hours)
);
 
// Conditions for which records should be updated.
$conditions = array(
    $db->quoteName('id') . ' = ' . $db->quote($customer)
);
$query->update($db->quoteName('fab_customer'))->set($fields)->where($conditions);
$db->setQuery($query);
$result = $db->query();

And on service form this :

$customer = $formModel->formData['fab_service___customer_raw'][0];
$hours = $formModel->formData[fab_service___hours'];
$db = JFactory::getDbo();
$query = $db->getQuery(true);
// Fields to update.
$fields = array(
    $db->quoteName('current_hours') . ' = current_hours + ' . $db->quote($hours)
);
 
// Conditions for which records should be updated.
$conditions = array(
    $db->quoteName('id') . ' = ' . $db->quote($customer)
);
$query->update($db->quoteName('fab_customer'))->set($fields)->where($conditions);
$db->setQuery($query);
$result = $db->query();

Needless to say that list's names are corresponding to my setup ( fab_customer, fab_contract and fab_service)

Since i wouldn't want that a customer gets negative available hours i need to make a small change to the service PHP so that if the fab_customer___hours drops bellow zero , the value stay as zero. When i'm able to change the PHP code ( or if genyded is willing to help me once again ) i will post it here.