difference between dates.

For HanDBase related topics that are not specific to one platform.

difference between dates.

Postby glenwf » Tue Dec 16, 2014 9:41 pm

I know the difference between dates and time has been well discussed and I understand and use them.

In using them, I find every new entry has to be at the bottom of a long list because the calculations use the difference between now and the last PREVIOUS entry. I don't see any option to use in the calculation NEXT instead of "previous". If we could use that the calculations would allow us to order the list so the last entry was at the top of the list of records, reading down for earlier records. That keeps the most current data visable at the top when you open the database instead of having first to scroll way down to the end of the records to see the last entry and data from that.

Probably can't do that, eh? Just a thought or a wish anyway.

Thanks.
glenwf
 
Posts: 170
Joined: Wed May 20, 2009 5:34 pm
Location: Roseville, CA

Re: difference between dates.

Postby mjhanna » Wed Dec 17, 2014 12:35 am

You say you know about dates and times, so you know they are just represented internally as numbers with minimums and maximums. So I would think you could have a calculated field which was a number based on the date/time which was hidden but used for a descending sort?
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 156
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico

Re: difference between dates.

Postby glenwf » Wed Dec 17, 2014 2:43 am

Oh my. I can't reorder my current database so the last date is on top and have the numbers make sense. Not sure why that is the case?

But, If I make a copy of my database and reorder the view for a descending order, it does work. A simple thing like that I should have tried but didn't think it would work. Then every new record with a date that is newer than the last shows up on top and the difference in days works.

Thanks for making me look at it differently. Perspective is everything sometimes.
glenwf
 
Posts: 170
Joined: Wed May 20, 2009 5:34 pm
Location: Roseville, CA

Re: difference between dates.

Postby glenwf » Wed Dec 17, 2014 4:43 am

Sorting them in decreasing order does work, as long as you don't look at an older record. Then the numbers get all recalculated I guess and all numbers get messed up. You have to start at the first one and work through each one to recalculate it again to get the numbers to be correct again.

Is there a way to avoid this recalculation problem when you look at an old record?
glenwf
 
Posts: 170
Joined: Wed May 20, 2009 5:34 pm
Location: Roseville, CA

Re: difference between dates.

Postby Brian_Houghton » Wed Dec 17, 2014 6:47 am

Hi,

Thanks for posting.

You should be able to add your sort condition to a custom view, and set that view to update automatically, to have the fields always sorted for you. Give it a try and let us know if this doesn't help.
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: difference between dates.

Postby glenwf » Wed Dec 17, 2014 1:02 pm

Creating a reverse sort does work, with problems. Everything is ok until I have to edit (without changing anything or adding a note) a previous record. Then the days difference is calculated in reverse order also. I have to go back to the default forward sort view, edit (without changing anything) each record from that point forward to recalculate the values again between days.

The calculation is done between days in the reverse order is correct, just in the reverse direction which I don't want. I want the days going forward, not going backward. Otherwise, how many days ago did the I last fix the problem. Forward being from a week ago to today for example.

Remember, the only reason for the reverse order is so the last records come up when you enter the database and not way at the bottom. That was why I was wondering about the possibility of "Next" in the calculator along with the "Previous" record.

Thanks.
glenwf
 
Posts: 170
Joined: Wed May 20, 2009 5:34 pm
Location: Roseville, CA

Re: difference between dates.

Postby mjhanna » Wed Dec 17, 2014 1:03 pm

Sorry, I did not internalize that you were doing a calculation based on a "previous record". As you have discovered, the Sort will affect the calculation as it may cause a different record to be "previous". As Brian mentioned you can have multiple Views with their own Sorts, but you want to Add records when in the appropriately sorted View.

For this case I might consider using Forms and a separate related database for viewing the data. The separate database would essentially be a duplicate of the "real" database, but all fields would be Relationship fields (probably using the Group facility) showing the values of those fields from the "real" database. Then you could sort and filter that separate database however you desired with multiple Views. On the custom Form when viewing a Record in that view-only database, you would have a button for "Edit" whose Action would actually open that related record in the "real" database which is sorted correctly. Likewise you could have an "Add" button whose Action would add a record to the "real" database where the sort order was set to be appropriate for the calculations. That probably should be done by a Form for the "real" database, and a Finish button on that Form might be able to cause the companion "view" record to be added.

Might be a bit of work to set up, but once created it should do what you want. [Edited to include the need for a new record in the view-only database when you add a record.]

Hope this gives you ideas,
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 156
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico

Re: difference between dates.

Postby glenwf » Wed Dec 17, 2014 1:11 pm

I see the idea. I'm sure it will work and easier to use for an older non-tec person also.

Like you say, a little work, but fairly straight forward and not convoluted as some solutions seem to be.

Gives me an opportunity to learn the "Relationship fields (probably using the Group facility) " capabilities of HandBase.

Thanks.
glenwf
 
Posts: 170
Joined: Wed May 20, 2009 5:34 pm
Location: Roseville, CA

Re: difference between dates.

Postby gadebmo » Sun Dec 21, 2014 7:44 am

glenwf wrote:That keeps the most current data visable at the top when you open the database instead of having first to scroll way down to the end of the records to see the last entry and data from that.

Probably can't do that, eh? Just a thought or a wish anyway.

Thanks.


Wouldn't a way round this be to keep the original, increasing sort order and then just set the startup option to show the last record in list view (DB Properties -> Startup -> Show Last Record In List View). At least this works for me on my Android phone.
gadebmo
 
Posts: 10
Joined: Thu May 21, 2009 3:10 am

Re: difference between dates.

Postby glenwf » Sun Dec 21, 2014 5:43 pm

I think that is an elegant solution. I didn't know about that option or that section of the program.

Thanks.
glenwf
 
Posts: 170
Joined: Wed May 20, 2009 5:34 pm
Location: Roseville, CA

Next

Return to HanDBase General Discussion

Who is online

Users browsing this forum: No registered users and 1 guest