Creating a Trial Balance
this has probably been answered before but being new I just can't get it. I have a table called Ledger with 5 fields. The usual date etc. I have a field Account selection field and 2 fields Debit and Credit.
All I want to do is sum all account credits and debits and display them like a trial balance. I have tried a view with all accounts listed and tried this sum(select Ledger where Subs = Credit) But get Subs not found which is an account name.
Going round in circles now.
Help would be great.
Remembering I only have been using this hardly ever.
I know nothing about accounting, but you can sum columns like this:
let t := (select Ledger);
let d := sum(t.debits);
let c := sum(t.credits);
d - c
I do not understand what filter you want to apply. If Account contains customer names and you want to filter by a customer name then:
let t := (select Ledger where Account = "ABC Company");
Or in table view, you can click the column headers of debit and credit, and click "Sum" for each, and then add a
debits - creditsformular as a third column and click the header of that column and select sum.
Its just a 2 coulumn cash book.
Headers are DATE ACCOUNT DETAILS CREDIT DEBIT
SUBS 10 20
SUBS 15 30
EVENTS 20 100
There are about 12 accounts and I have to make a summary sheet with the accounts listed like
SUBS CREDIT DEBIT
EVENTS 20 100
and so on for all the accounts. Running totals for each account. This is then produced on a different sheet for charity commision and accountant.
Not sure if this is clear.
Is it worth subscribing to the basic nioxus?
Thanks again will percivere.
You can learn from the video without subscribing. Later, you can subscribe if you want to support them. The videos represent a lot of work, and now it appears they have to do some of their videos over because of the new interface.
The main reason I suggested the video is because it explains use of Views on a dashboard. However, it appears to skip over how to create a dashboard. Maybe they assumed the viewer learned that in a prior video.
I suggest you test it out with a new test database that starts out with one table named Table1. Then close the database. To create a dashboard:
1. open test database
2. click wrench button at upper right
3. click "Data model" button
4. click "New table" button
5. name new table "Dashboard"
6. click "Add layout element" on the right
7. drag "View" element to center column
8. double-click View element
9. enter "select Table1" (without the quotes)
10. click "OK"
11. save changes
12. click "Dashboard" table at left
13. click "+" button at top (beside the "(all)" tab)
14. in New View dialog select "Form" and name it "Dashboard"
15. right-click and remove the "(all)" tab
16. click "+" button at far right to create one record
Table1 should now appear in a view on the dashboard. You can have multiple Views on the dashboard, and hide/show them as needed. You can also manipulate them with select statement(s). The video does a good job of showing examples of select statements.
Hope this helps.
Revisiting your examples above, if "SUBS" is just discriptive text, then maybe something like this would work:
let t := (select Ledger where 'ACCOUNT DETAILS' = "SUBS");
let c := sum(t.credits);
So i would just add this formula into a field and it should display.
Thanks for your patience.
If 'ACCOUNTS DETAILS' is your field name and "SUBS" is text typed into that field, then it should work. Notice that field names containing spaces must have single quotes and static text must have double quotes.
That's brilliant will have a go later. Thank you will report back.
OK here's the update.
I made a field and applied your formula it does total them up OK now.
Only thing now is it displays the totals in every record. Is there a way to maybe assign the total to a fixed field or variable so I only have eg Subs 500. Rather than it repeated.
Hope this is clear.
Managed to get dashboard done and will watch videos ASAP.
Yes, you can create a new "Dashboard" table, that I mentioned earlier, with one record. Replace the default table view with a Form view (click the "+" to create the Form view), and then remove the table view by right-clicking it. Then you can place view elements and formula fields on the Form, as needed. Remember to create just one record in the table. That will allow you to see the results of your select statements in views and formulas.
Ok thanks will have a go at that tonight. Brilliant thanks
Dean brilliant thats exactly what i wanted. I was just wondering is the t just a variable and could be anything.
Yes, the t could be any letter or name. Think of it as a container of rows (ids) and columns (name/value pairs).
- 2 yrs agoLast active