0

Creating a Trial Balance

Good evening.

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.😭

Thanks again.

14 replies

null
    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    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 - credits formular as a third column and click the header of that column and select sum.

    • Faldo
    • 3 yrs ago
    • Reported - view

    Good Morning

    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

                                            25           50

                              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.

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    See this video "Dashboards & Views With Hidden Tables":

    https://www.youtube.com/watch?v=nUqGl84gh08

    • Faldo
    • 3 yrs ago
    • Reported - view

    Is it worth subscribing to the basic nioxus?

    Thanks again will percivere.

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    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.

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    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);

    • Faldo
    • 3 yrs ago
    • Reported - view

    Hi

    So i would just add this formula into a field and it should display.

    Thanks for your patience.

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    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.

    • Faldo
    • 3 yrs ago
    • Reported - view

    That's brilliant will have a go later.  Thank you will report back.

    • Faldo
    • 3 yrs ago
    • Reported - view

    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. 

    So close. 😁

    Thank you. 

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    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.

    • Faldo
    • 3 yrs ago
    • Reported - view

    Ok thanks will have a go at that tonight.  Brilliant thanks

    • Faldo
    • 3 yrs ago
    • Reported - view

    Dean brilliant thats exactly what i wanted.  I was just wondering is the t just a variable and could be anything.

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    Yes, the t could be any letter or name. Think of it as a container of rows (ids) and columns (name/value pairs).

Content aside

  • 3 yrs agoLast active
  • 14Replies
  • 1518Views