0

How to calc sum of date specific entries in one table from another

Hi, I’m new to Ninox, having come from HanDBase for iOS (which I found had a simpler method of calculation). I’m trying to create a table that summarises entries in another table for a given date. (I’m a taxi driver, needing to keep records of jobs, and want totals of fares for a given day)

I have a master table that lists details of all trips on a given date (Date, Name, from, to, fare, cash or account and amount). I have created another table that needs to have entries for a given date that sums account and cash work for that specified date, however I’m completely at loss as to where to start. I have found >sum((select TaxiFares).Cashtally) works as a general for all entries but I don’t understand how to make an entry date specific, ie sum only the entries for the date of the sub-table entry.

I also want to be able to apply this to another table of expenses for a given date, so hope you can help. Thanks

16 replies

null
    • Sean
    • 5 yrs ago
    • Reported - view

    You shouldn't need another table, just a different table view. If you use your existing view, click on the table header for your date field and choose "Group". Then, click on the field headers you want to sum and choose "Sum".

    • chrisif
    • 5 yrs ago
    • Reported - view

    Thanks but no, that's not what I'm after. I'm looking to have a permanent record for each day's taking, split cash/account. Using Sum on a different view is temporary and labour intensive (having to adjust the date range each time).

    I need to work out some way of filtering for date specific entries, totalling those (split between each category) and write that as new entries.

    • Sean
    • 5 yrs ago
    • Reported - view

    I'm a truck driver not a programmer or database administrator so maybe that's why your intended meaning of permanent in this context is lost on me. A record will be no more or less permanent in one table or 100 tables. It's there until you delete it. Same with a view. A view is not labor intensive, you set it and forget it. It will work on current data and data you enter later on.

    Are you wanting to create an archive table and delete records in your master table after a certain period of time has passed?

    • chrisif
    • 5 yrs ago
    • Reported - view

    It may be the way I'm trying to explain (or even use Ninox) that's wrong, sorry, but my interpretation of Views is a bit like tabs on Excel, and I don't want to have to set a new tab each day and configure its view and then end up with hundreds of tabs.

    My current setup (with HanDBase) is have one table, which I have with forms, for data entry, with a new record entered each trip. I then have another table that I use to automatically summarise each day for cash / account work. This effectively becomes my accounting record, against which I can cross check account payments.

    I appreciate Ninox has a powerful language behind it and that's possibly where I'm falling over, as I try and come to grips with it.  

    I also want to create other relationship tables, for things like expenses per day by category but until I've figured out the first part that'll have to wait, though I imagine it'll slot into place fairly easily once I've got the first one sussed.

    • Sean
    • 5 yrs ago
    • Reported - view

    I think they mention the KISS principle in the documentation ;). Don't overthink it. That can seem trite when you're trying to figure something out though.

    You have your main table view in the first tab. You can create new tabs to refine what you want to see of that main table. I don't think you would need more that 4 tabs in total, but that's what you need to decide. You could have a tab for Cash Receipts, Accounts Settled and Accounts Receivable. You can choose which fields/columns you want to have in each view so, for example, you could Group and Sort on the Date column of the Cash Receipts tab and Sum the Cash column. This tab will Group and Sum by day. This tab will update itself when you enter new records so you do not have to create a new tab for each new day.

    For what it's worth, I still use HanDBase to compare net diesel prices along my route across different states in the US.

    • chrisif
    • 5 yrs ago
    • Reported - view

    This tab will update itself when you enter new records so you do not have to create a new tab for each new day.

    And there’s the nub...I don’t want the total to update each day, I want an individual total for each day, written as a new record. Hence the requirement for a new table.

    (unless I’m missing your point)

    I think I’ve seen something that does what I want but I’m always tackling this when tired, so never really figure it out. I need to review the forums/documentation and sit down with a pen and paper.

    • Leonid_Semik
    • 5 yrs ago
    • Reported - view

    Here is an example. If you enter a date in the Taxt Fares table, a new date is created in the Dates table and the records are linked. If the date already exists, this date is simply linked. This is as trigger on the Date field in the Taxi Fares table.

    https://www.dropbox.com/s/3qdqyaa4765vv6b/Taxi.ninox?dl=0

    Leo

    • chrisif
    • 5 yrs ago
    • Reported - view

    I think I’m looking for something like 

    sum((select TaxiFares).Cashtally) where TaxiFares.DateT1 = DateT2

    but I can’t get this to work.

    • chrisif
    • 5 yrs ago
    • Reported - view

    Sorry Leo, posted before realising you’d replied. I’ll have a look, thanks

    • Sean
    • 5 yrs ago
    • Reported - view

    I neglected to mention that each group in the grouped field is totaled.

    • chrisif
    • 5 yrs ago
    • Reported - view

    Leo, many thanks, that seems just what I wanted. Now I’ll have to see if I can expand on it for the other parts I need.

    • chrisif
    • 5 yrs ago
    • Reported - view

    Slowwagon, didn’t realise you could group like that. That’s given me something else to play with, thanks.

    • chrisif
    • 5 yrs ago
    • Reported - view

    Sorry, back again :)

    Leo, I see the example you gave works well for new entries but I’m unable to apply it retrospectively. As I’ve already a large dB of entries, for which the only way I seem able to get the process to work is manually select each entry using the search function one by one.

    What needs to be done to be able to enable a new entry in the “sum” table to be able to read entries in the “fares” table if selecting previous entries?

    • chrisif
    • 5 yrs ago
    • Reported - view

    Actually, @slowwagon, I think it’s just a lot easier to use your method of the views. Now I’ve seen the grouping by date I’ve got what I want that way. 

    I’d like to be able to understand Leo’s method, as I’m really interested in Ninox and its power and want to be able to use the method for other things I have in mind. But without a decent reference manual I’m poking around in the dark.

    • Leonid_Semik
    • 5 yrs ago
    • Reported - view

    You can write a funktion as trigget at button:

    ---

    for ii in select 'fares table' do

    let myDate:=ii.Date;

    if cnt(select 'sum table' where Date=myDate)=0 then 

    (let newSum:=create 'sum table';

    newSum.Date:=myDate;

    ii.'sum table':=newSum)

    else

    ii.'sum table':=first(select 'sum table' where Date=myDate)

    end

    end

    ---

    Leo

    • Sean
    • 5 yrs ago
    • Reported - view

    @chrisif, I'm glad you gave it a try. It's one of the great features of Ninox.

    I don't know if you have a unique field for each transaction type or if you need to keep a receivable record after it's been paid, but here's how I set it up...

    Date field

    Choice field for "Fare Type" (Cash, Settled, Receivable)

    Amount field

    Cash field - formula => if FareType = 1 then Amount end

    Settled field - formula => if FareType = 2 then Amount end

    Receivable field - formula => if FareType = 3 then Amount end

    This way, when a receivable is paid, you just change the date and Fare Type and the view automatically updates.

    If someone knows of a better way to implement the formula, please point it out. This was the only way I could get it to work.

Content aside

  • 5 yrs agoLast active
  • 16Replies
  • 7607Views