How do I create a running total on a form?

[Derek Handy]

Hi, just started using Sailforms and have been busy porting my db's from Handbase.

The problem I have is that I don't seem to be able to recreate a running total on a form.

One form for example is very simple ie,

A field that logs mileage travelled and a field that give a running total mileage.

What I can't seem to do is be able to add the mileage of the current entry to the total mileage of the previous entry.

Thanks

Derek

[Chris]

Hi Derek:

Take a look at the gas mileage sample in the Math groups. It shows how you can load the last mileage entered.

[Derek Handy]

Hi Chris,

I've looked at this gas mileage db and see how to bring forward previous entries but cannot see how to apply it to my mileage db.

The gas mileage gives a total miles for that entry, End miles-Start miles.

What I want to do is carry this total forward and add it to the total miles of the new entry.

Then carry this new total to the next and so on.

Derek

[Chris]

The Gas Mileage sample kind of does what you are looking for, unless I don't understand exactly what you are looking for.

Each new entry has the Start Miles = to End Miles of the last record.

It is a bit kludgy. The next release will let you put in a math command the is similar but you can pick the field.

Start Miles = last(End_Miles)

If you are looking for more of a balance sheet looking report, then doing it as a transaction based system with a ledger of each gas purchase would be more of a SailformsPro type system.

[K Michael]

What he's asking is the same thing I'm trying to do.

Trip 1
End Miles - Start miles = 5 miles

Trip 2
End Miles - Start miles = 5 miles

What we're looking for is a way to keep a running total of the trips.
Trip 1 = 5miles
Trip 2 = 5miles
Total = 10 miles

Thanks for a great app.

[Octazid]

I believe you need sailforms pro for that. There is a Fieldtype called TotalRemote. With this you can total some entrys easy.
Or you upload a sample of your form (only with a PC not from mobile device) and we can take a look on it for a better understanding.
Many greetings

[Jan-Erik Matz]

Hi!
Just add fields StartTrip, EndTrip, ThisTrip and TotalTrip. All fields number. I used whole number.
In ThisTrip add math. EndTrip-StartTrip
In TotalTrip add math. Last()+ThisTrip
In StartTrip add math. LastF(EndTrip)

And first record add your miles from car in both StartTrip and EndTrip.
If you have done right you will have totals in every record. Hope it helps.
Happy coding from Jemen

[Derek Handy]

Hi Jan-Erik

I prepared a form exactly as you said and yes it calculates a running total on each record,so far so good.

If I select a previous record the 'Start Trip' field takes the value from the last 'End Trip' entry and the whole form is messed up.

If I set the 'Start Trip' field to 'Disable after Save' then this prevents any future editing.

Also, as I said in my first post, I have imported records from another database and there seems to be no way to update previous records.

Unless this is something that can be put right I will probably have to stick with the HanDBase app. Its not perfect but seems to be able to do the things I want.

The feature that attracted me to Sailforms Pro was the ease at which forms could be laid out.

Tungsten

[Derek Handy]

Sorry, should have signed off my previous post with my user name 'Derek' not my alias 'Tungsten'

Derek

[K Michael]

Octazid , and Jan Erik Matz, thanks for the input.

Jan Eric Matz.
Thanks for the suggestions.
I had already done a database like you layed out before you replied.
The problem is now, like Derek stated, with this setup if you reopen a record in the form view it recalculates and doubles the value in the Total field.

Derek.
Like you I've used Handbase since my Palm Pilot days, and changed to Sailforms for the same reason as you.

I had the same problem when importing a database, I couldn't get Sailforms to recalculate the entire database after setting up the formulas.
I found that if I opened each record in the form view it would recalculate that record.
So while it looked odd in list view, at least if I needed a particular record it would recalculate when viewed.

Keep updates coming if you find a solution you like.

[Jan-Erik Matz]

Hi!
It works as it should, but messes up if some fields are not disabled after save.
It is a database and not excel.
I think it will be fixed in 1.99 with list processing.
It is possible to edit, add a button for edit value like init or so. But boring to fix all records after the edit.:-)
So just W8 4 1.99
I used handbade also. Yes lot of things are better but overall Sailforms run out handbase.
If I start with a new database I use ideas from old database but I do a total new and different way to arrange my database. I use excel to trim and join old and new fields. All new calculations is done in excel before import to the new database. All new fields is filled with new data fake or real.
May way of thinking about a database is it is only for new data,all old data must be processed like it is new data.
It is so fantastic with databases
it is no right way, all ways lead to a better way.
Happy coding from Jemen

[K Michael]

Jan-Erik

Valid points.
I especially like the idea of using Excel as a step before importing.

After spending a few hours with Sailforms I've decided it's a much better overall experience than Handbase.

I'll wait for the upgrades ☺

Thanks for your input and time.

[K Michael]

One last thing.
When you say "It is a database and not excel", I have to disagree.
Even a simple database should be capable of keeping a running, or cumulative Total.

Look at the sample databases that come with Sailforms
The Time Card database, it keeps a daily record of hours worked or pay, but you can't keep a total

The same with the Gas Mileage math example.
If you want to expand it to keep a record of gas purchased and price per gallon, with a running total of price paid and total gallons. It doesn't seem to be possible.
Or what was originally asked about a running total of miles driven.

This should be a simple procedure for any database software, and I was surprised to see that it wasn't in Sailforms

I still think Sailforms is a nice database and look forward to all new updates, I will also upgrade to Pro this week.

Again thanks for your input and time.
Granted I only have the Plus version, and it may be something I can do with the Pro version.

[Jan-Erik Matz]

Hi!
I don't understand why you need to edit a record. A drive from place a to b is done and could not be undone.
List processing is what excel is built around. Easy to calculate in but calculations between pages is not so easy done. I wish typecast was as easy as in real life.
Some day intellisens has come to that point.
So hopefully listprocessing in 1.99 will be excel inspired :-)
Possible to add virtual fields saved in lists and possibly to add fields to db in lists.
Totals in lists works not good enough for real use but hopefully in 1.99.
Lists is good for search and look and find, presentations as pdf. With real use I mean possible to get calculations for any number field by add breakpoints.
To control the calculation fields.
Add a formula in calculation fields.
More powerful control. But more control will make it more complicated to use.
So a power user mode:)
I recommend you to buy pro,many more hours front of your phone in hopeless struggling with all the fine ways to make your ideas to get live on the screen.
So until next time
Happy coding from Jemen

[K Michael]

Absolutely on the Pro Version.

To your question of why I would open a form again, the answer is both simple and complex.
A Database is not used as simple list making software.
A database allows me to to store, analyze, and manipulate information.

I used the Gas and Mileage examples as a way of explaining what the problem was.

An example of what I was trying to do with the Gas Mileage sample, was build what I had in Handbase.
I could enter:
Gas both gallons and price
Mileage
Service information and price
Destination and reason for trip (tax purposes)
I also kept a running total of all those and other things

I could enter a gas purchase close the form
Reopen the form to enter a service item and price
Enter End mileage at the end of the day.

I can't do this in SailformsPlus each time I reopen the form the total fields recalculate and duplicate the last entries. It does this when the form is opened and closed.
So if the total field is100 and the last amount added to it was 10.
Each time I open and close the form it adds 20 to the total☺
100, reopen the form, 110, close the form 120.


You're right about the Pro version being a better choice,
Handbase was a Relational database program and SailformsPro is a better match than Plus.

Again thanks

[K Michael]

Ok, just upgraded to Pro.
Just don't expect the stupid questions to stop ☺

Thanks for your help and input
Michael

[Derek Handy]

I've been using Pro since I started and still cannot get it to do basic adding up.

Don't expect all your problems been solved with the upgrade to Pro.

Until Sailforms Pro can do these basic things I won't be going further with this.

Don't want to risk loosing valuable data.

As for starting from scratch, no chance.

I've converted data between MS Access, ThinkDB on Palm, Smartlisttogo on Palm, HanDBase for ipad and HanDBase for Android, never come across a database app that couldn't give running totals.

Derek

[K Michael]

Pro didn't solve the issue.
It will let you lock a field so you don't get the duplication problem but it creates problems of its own.

Yes this is the first database software I've used that didn't let you manage totals.
I always thought this was one of the basic tasks that all DBMS programs did.

I don't know if it's because I don't have the proper background, (I'm a database novice) but I've used multiple DBMS programs on DOS, Windows, Palm Pilot, Android, and never had an issue getting things to work the way I want to.

I'm surprised that the forums aren't full of questions about this.
I assumed it was because I didn't understand how to do it and everyone was rolling their eyes and shaking their head in amusement when I asked about it☺

[Derek Handy]

My databases without calculated fields work just fine.

As I said before I like the ease with which forms can be laid out and I also particularly like been able to embed remote tables into forms.

Something I was able to do in Access but never on a portable device like a tablet.

However at the moment calculated fields play a significant part in several of my databases and until this is fixed I wont risk several years worth of data.

Thanks to all for their input

Derek

[K Michael]

It doesn't look promising, I did a Google search for, running total in SQL.
First sentence stated :
"Displaying running totals is a common request, and there is no straightforward way to do so in SQL"
It then proceeded to show some work arounds, nothing simple or easy.

So since Sailforms is a SQL based DB, it's probably not going to be a feature any time soon.

For now I'll use the lock field method.
This still doesn't allow you to modify an entry and have the DB recalculate the totals.

I'll look at the Tea Shop sample and see what's been done there to record sales and totals owed

[K Michael]

Ok, this is looking more promising, I haven't quite worked out the details yet.

But as a work around I found I can Total each column in the list view by long pressing the column tab, and selecting the Total box.
This puts the total of all the numbers in the column at the bottom of the column.
It also allows changes to saved records that are recalculated.

So it's just a matter of time and reading to find how to Total the columns into a field.

Being a complete idiot and stumbling upon the obvious, is very rewarding sometimes. ☺

[Chris]

List totals and sub totals will work fine.

The problem with a running total in an entry is it really should be calculated and saved, but the value is dynamic. What if you go back and correct a previous entry.

The Database solution is to do transactions, then total the transactions.

Create a form for your cars. Give each car a unique Id.

Create another Trip Transaction form that does a Search-List-Get on the Car Unique ID and also has Project Id, Timestamp, Start and End miles, calculate total miles.

Sort and break the Trip Transaction form by Project Id and subsort by date. Add subtotal and total on miles. (Hide the Car Unique Id) and save this layout.

Go back to the Car form and add an embedded table connected to Trip Transaction and select the report you saved in the trips form.

See the attached system.

[K Michael]

Thanks Chris,
I've been going through each of the samples, and tutorials, and referencing back to the Pro Guide.

I've already started to put together a diagram of the DB and functions/controls/forms I need.

Your sample is a great start.
I'm still looking to see what's needed for multiple cars, to keep info from one car populating fields when entering info for other cars.

Something to keep me busy ☺

Thanks for the help
Michael

[David Hooper]

How about a command that would allow you to go to the previous record (running balance) in the sort order instead of last saved? That way you could retrieve the last running balance. If you changed a record, you could use the process list to correctly recalculate.

[Pat Lane]

Hello. Yes, i have a similar problem with running totals. I have just purchased Sailforms Pro and do like the look and ease of it, that is when i get my head round the hard bits of course. Was previously using Memento, and that does have a running total process by way of a 'mass calculation' field, but fails short of the tools provided by Sailforms. I am trying to design a bank consolidation database (of which the tea shop and mileage forms have many of the processes i need between them) Unfortunately consolidation gets very messy with a real need to alter/re-alter records, add fields, change data and design..and of course not just one running total, but several? Any help would be appreciated. :)

[Chris]

Attached is a running total example.

It uses the Search Remote function to load the running total from the last entry.

It does require a Counter for the entry id so the math equation can calculate the previous ID.

The beauty of this is that you can go back and change older values, then run the process "Update-Replace List Entries" to update the values. (You need to have the list sorted by id for this to work)

[Piet Kulik]

Chris, works great as long as you add or change a record, but inserting a record somewhere in the list is a problem because of the counter.

[Anil Felix]

Hi Derek, Chris and Dear Sailors (Sail form Users :-))

 

Running Total Implemented ; Some Important Required Features: "DO READ THEM"

 

Running Total:

The running total to show dynamically once the user enters the end mileage (in this case) would be possible by using Remote Total.

In sailforms as per my observation, the Remote totals are updated when we leave the form. If the remote totals are refreshed when the form is saved the total can be dynamically shown. I think this feature need to be implemented by Chris. 

 

For now, i have played around for a while and came up with a solution. Let me know if i have overlooked any of the conditions. Do Check the attachment and let me know your thoughts.

After entering or editing, press the 'save and close' button.

 

In Sailforms Pro, though some* important features are missing, it gives us an opportunity to play around for the solution :D :)

 

Developer Attention:

 

Dear Chris:

Some important features that require immediate attention* to make the app more robust and helpful to Sailors.

 

1. to be able to perform some default actions 

    a)when the form is either loaded or exited (on load or on exit event)

    b)when a field is entered (on enter event)

    c)when a field is updated (on update event)

 

2. remote totals(totals, minimum, maximum....) to be refreshed when form saved

 

3. For remote fields, we are now having remote count and remote total. Please include remote minimum, remote maximum, remote average. These are very important features. 

 

4. For a field to show only filtered values in dropdown (for connected fields, search lookup or search if found...). A condition option while making the connections to show only required records in dropdown.

 

5. When showing remote table we are connecting remote table with existing table(form) with one common field. 

     a) is it possible to connect with more number of fields matching?

     b) is it possible to include conditions(filters)?

This is important feature missing in the current version as this would help in querying the data efficiently. For now I am defining my own keys by creating new fields and initializing them to join two or more fields to form compound key for getting remote totals satisfying the condition.

 

6. Default alarm sound and Vibration option. Currently whenever i enable alarm time, I need to select the sound/vibrate option. Other wise it does not ring or vibrate.

 

7. Putvalue. Can we have a filter condition in put value, where we would be able to update a specified record?

8. Button Click - Backup Database: I have developed an app for my father to track the schedule planning of his business. I just gave him a widget on homescreen to launch this schedule planning app. After few days, he asked me how to backup the data. I was blank. If there was an option to assign this action to a button, then it would have been easier to backup the database by just one click or may be send the backup to default mail ID

 

  

Other Features:

 

1.     1. Zoom option in list view

2.     2. Inline edit when long clicking. Currently we are able to adjust sizes by long clicking. May be you can give us a button on top to adjust sizes or inline edit if possible.

3.     3. When a new remote field is added, it shows in the first position in the list view, disturbing all existing and stored list views. The field must be shown at the end of the list. A more innovative way to arrange fields to sort the fields in order by showing only the field names and sorting them accordingly would be great.

4.     4. Remote table to be over other displayed fields. When we assign a button action to show remote table in the existing form, the other fields must not be visible i.e, the remote table should be over all the other fields which it is covering. This would save lot of time in coding of hiding and showing of fields.

         5. Exporting the application as APK. I think if this is done, then you people would be just Unbeatable !! 

     

 I've deviated from the topic, but if few of the above options were present, the development of this simple app would have been a lot faster and easier.

Some features requested are advanced. Well what is advanced? After checking the other database applications in android playstore and comparing with Sailforms pro, Sailform has implemented and presented lot many complex features in very much user friendly and understandable way.

Hope these missing features are in development and we would see them soon in future versions

(Long post... I think i deserve a break after playing around with group totals and this much of  typing :D )

Happy Coding…

Felix.