Hi all,

We are recent ZC converts and we’re trying to build a Job Management application for a creative agency.

The application will manage:

-          Job workflow (Jobs contain different Tasks and Status’)

-          Track Hours per Task

-          Track total Hours per Job

-          Calculate Invoice amounts

-          Track Expenses

-          Generate Profit + Loss per Job

The Jobs Form/View has these fields: Client, Job Name, Status, Hourly Rate, Actual Hours.

The Logged Time Form/View has these fields: Job Name, Task, Hours (shows all the Tasks worked, their related Job Name and the Hours worked per Task).

We are trying to get the Jobs View to look up the Logged Time View and return the total Hours worked for each Job Name. This means the script will have to look up all Tasks in the Logged Time View for the relevant Job Name and return the total number of Actual Hours worked for that Job Name. A formula will then generate the Invoice amount (Hours x Hourly Rate).

As there appears to be no SUM function in Zoho’s lookup/formula/deluge, and the other forum posts haven’t covered this fully, we’d like to ask:

How do you get a field on one Form/View to look up and return a SUM total of related fields in another Form/View?

For example, we have an example job in the Jobs View (Job Name is Example Job). And in the Logged Time View we have Task 1, Task 2 and Task 3 logged for Example Job. Each Task has 1hour logged against it.

How do we get the Jobs View – Actual Hours field to look up all Tasks (Tasks 1, 2, 3) for Example Job in the Logged Time View and populate the Actual Hours field with the SUM total of the Hours of Tasks 1, 2, 3 for Example Job in the Logged Time View? So the Actual Hours will populate itself with 3 Hours.

Any advice or assistance with setting up the correct scripting/formulas would be very helpful.

Thanks,

Luke