Setting up a formula field across records in a single table

Hi,

did you try out the grouping function?

Use a formula for year/month on the date to display a new column. And group by this new column. Now you can get the sum for each month.

Is this going in the right direction?

Please be invited to our webinar:

https://zoom.us/webinar/register/WN_zoSk2qyNSz6vLRFF1zlakg

Best regards, Alex

I have got this far but I need to do the same but have it update a field on a form.

i can’t get similar code to work

let myWeekWorked := 'Date Worked';
sum((select '11frm-Timesheet-01-01' where myWeekWorked >= 'Date Worked').'Balance for this page')

Summary: I need to display the total number of hours worked in a week as it proceeds on a daily basis.  I have all the data and trying to by code. I don’t want to have to refer to view of the data.

You should be able to set up a view listing all of the records from the Expenses table and then use the option to SUM the column that is the trip expenses field.  You can also using grouping and filters within the view to limit the records included in the view.  There really is no reason that you would have to write code to get a sum total of record-based calcuatlions.

Please, you have not answered the question I asked. I have done that and it is not the solution I am after. All you have done is trotted out an answer without understanding what I want. There is really no reason why you should have bypassed my question.

I have a form where I want the answer to appear. I do not want to change views to review the answer, scribble it down and do a comparison.

OK - Having now re-read your question, you are correct in that my original answer will not suffice.

What you are going to have to do is create a Parent/Child relationship between two tables.  In this case, the Parent Table is 'Trips' and the child table is 'Expenses'.  You can create this relationship by making the child a component of the parent (to learn more about this, refer to the Users Manual at www.ninoxus.com)

Once you have created the correct architecture as defined above, you can use the sum function to add up all the expeneses in each record related to each trip.  You will do this by creating a formula field in the Parent table where the formula is sum('Expenses'.'Total Expense Amount').  You will not need to restrict the records included in the sum function as the Parent/Child relationship will ensure that only those expenses for a particular trip are included in the trip total.

The process I have detailed here will enable you to look at a record for a single trip in form view, see all of the expenses detailed for that trip in the child table view that will appear in the parent record with a single field in the parent record view that presents the sum total of all expenses for that trip.

Now if you want to see the sum total of all expenses per trip, but don't need a field in the parent record, you can simply use the sum function on the expense total field that appears in the embedded child listiing.

Brill, and thank you greatly.  I will need to rework what I have done, but that is fine.