0

Converting min/mile

Is there a formula in Ninox that can convert a time of say "32:14" for 2 miles into Min:Sec per mile? In other words, I don't want 15.50 min/mile, rather I which to generate a text of "15:30" Thank you.

18 replies

null
    • Birger_H
    • 4 yrs ago
    • Reported - view

    (number('Time field') / 60000) / 'Miles'

    Birger

    • Retired
    • Pete
    • 4 yrs ago
    • Reported - view

    Thank you.

    • Retired
    • Pete
    • 4 yrs ago
    • Reported - view

    This doesn't work or more likely I don't understand how basic formulas function in Ninox.  Seems a bit complicated for something that other apps can do quite simply, that is format a text field to read minutes and seconds. Is there a way for the Time interval to be formatted in such a way to output as I desire: 15:30 or as Numbers does it 15m 30s?  I have a formula field that takes my overall time and converts it to seconds, so the conversion back to minutes and seconds could be done via total seconds of the walk or activity.  

    By the way, I did buy the desktop app to help stimulate my old brain, but I don't want to fry it.  I have used database apps since 1992, so I know enough to get into trouble, but I find as others have found that documentation for this app is wanting.  It would be nice to maybe have a wiki dictionary of all the formulas and examples instead of having to rely on some very nice people to post code that can only be found if one knows the key search words.

    Thanks.

    Pete Schuder (Apprentice, Second rate) :-)

    • Sean
    • 4 yrs ago
    • Reported - view

    I'm sure there's another way, but this works...

     

    if Miles != (null or 0) then
    let myInterval := number(TimeInterval) / 60000 / Miles;
    let myMin := round(myInterval);
    let mySec := substr(text(myInterval - myMin), 2);
    myMin + "m " + mySec + "s"
    else
    "0m 0s"
    end

     

    I used a TimeInterval field instead of a Time field and a Number field for miles. I think the Ninox Team should include links to frequently accessed parts of the manual on the forum page so that users who are unfamiliar with the site can navigate to those sections first if they are inclined. Here is a link to the language reference page...

     

    https://ninoxdb.de/en/manual/calculations/reference-of-functions-and-language

    • Retired
    • Pete
    • 4 yrs ago
    • Reported - view

    Thank you for your input, it is very much appreciated.  Can I assume I put that formula (code) into a TimeInterval field, as you seem to indicate?  The coding is a bit new to me, so it will take some time to learn, but I am patient and can spend hours trying to figure things trial and error.  But it is like trying to type with one finger instead of 10 fingers and having the keys blank...ha, ha.  I will test out your code and see if I can get a better understanding of how Ninox code does things.  

    Again, thank you for your help in advance, much appreciated.  I am off for my daily walk so I have more data to use.....

    • Sean
    • 4 yrs ago
    • Reported - view

    No, you put the code in a Formula field. I put the time in a TimeInterval field. It wasn't clear to me from the previous posts which type of field was used to store the time value and I wanted to be clear about which type of field I used to store it.

     

    Most of my understanding Ninox and other languages is by taking the time to read and practice. Coding is my avocation not my occupation. ;)

    • Sean
    • 4 yrs ago
    • Reported - view

    I see there are issues with my formula and that is partly due to the inconsistent treatment of the TimeInterval field. If I use the "3867.37m" Number format for the TimeInterval field, seconds are (base 10) and not the expected (base 60). I'll look at it more when I can.

    • Sean
    • 4 yrs ago
    • Reported - view

    Which field type are you using for the raw time data?

    • Retired
    • Pete
    • 4 yrs ago
    • Reported - view

    Thanks for responding.  I agree that it takes time and practice (and patience) to learn a new computer language, but I am finding documentation to be quite limited compared to documentation for other database products I have used in the past.  Not really complaining, just feeling I should not need to rely on other users for simple coding.  So, as I said, I really appreciate your input.  

    To answer your question.  I tried two methods for input of time.  One was as text putting in time as "32:15" in a text field.  The other was to use number fields and put 32 in one field (min) and 15 in the second field (sec).  In my previous experiences of converting overall time, I would use parsing code to separate the minutes and seconds and then do the math before bringing it back together again.  I did get close a few times as I was able to get the minutes, but the seconds would come up as .33 or some other number. I couldn't figure out how to get the seconds converted.

    I probably shouldn't be doing any coding yet, as I should look at the videos and read up more on the basics.  All I was trying to do was convert my old database app to Ninox to see how it would work and see if I can learn some of the formulas.   

    I looked at a video on Numbers where converting to min and sec is done in a few uncomplicated steps.  I was hoping that Ninox was fairly simple to use in creating fairly simple databases.  I have already done a few non-relational database samples to get my feet wet, but I come from an app that uses formulas and procedures to automat math and steps and uses a proprietary language, which maybe Ninox does as well?

    As I indicated I wanted to keep my mind sharp, if possible, and try a simple laptop app for tracking my walking, among other things (tracking stocks, writing running workouts and saving the workouts as html to post on the web, as well as keeping track of a church's bookkeeping of offerings credits and donations and expenses as debits.  

    I am hoping the learning curve isn't too steep, as I don't want to be discouraged.  Biggest problem is trying to remember what I just learned 10 minutes ago....ha, ha.

    Thanks for your help.

    • Sean
    • 4 yrs ago
    • Reported - view

    Hi Pete, I think I've gotten the kinks worked out. This takes the time from a Text field and mine is named TextTime. You can use a different field name, just replace TextTime in the code and use your field name.

     

    "// Get the minute/second separator index";
    let mssIndex := index(TextTime, ":");
    "// Convert minutes to seconds and add to seconds";
    let ttlSec := number(substr(TextTime, 0, mssIndex)) * 60 + number(substr(TextTime, mssIndex + 1));
    "// Divide total seconds by miles if miles isn't 0, otherwise, return TextTime";
    if Miles != 0 then
    let divTtlSec := ttlSec / Miles;
    "// Calculate divided minutes";
    let divMin := floor(divTtlSec / 60);
    "// Calculate remaining seconds. Need to test for single digit seconds, e.g., :2 and multiply by 10";
    let rmnSec := round(divTtlSec - divMin * 60, 2);
    let dgtCount := length(substr(TextTime, mssIndex + 1));
    if dgtCount < 2 then
    rmnSec := rmnSec * 10
    end;
    "// Build the interval time string";
    divMin + ":" + if rmnSec <= 9 then "0" + rmnSec else text(rmnSec) end
    else
    TextTime
    end

    • Retired
    • Pete
    • 4 yrs ago
    • Reported - view

    Sean:

    thank you so much for taking the time and effort to help me out.  I am going to spend some time going through this and see if my elderly brain can grasp hold of what you have written up.  I am optimistic, as I can spend hours tinkering around as then I don't have to do other work...ha, ha.

    One thing I noticed is that it seems you don't have to create variables ahead of time, rather you just create them as you go?  There is no setting of a local, or global variable at the beginning. 

    I am happy to see that pretty complicated formulas can be written for unique situations.  Now, learning how to do that will be an adventure.

     

    I really appreciate your help. 

    • Retired
    • Pete
    • 4 yrs ago
    • Reported - view

    I just tried your formula out and it works as offered.  Thank you again.  Off to a good start and maybe some fun working with this app....

    • Sean
    • 4 yrs ago
    • Reported - view

    Pete,

    You're welcome. I really enjoy coding in Ninox and I think it's easy to be productive quickly with the scripting language. Yes, variables can be declared as you go but they do have scope. I'm pretty sure a variable declared inside a loop can't be accessed outside the loop. I suggest you replace this line...

     

    if Miles != 0 then

    with this...

    if Miles != null and Miles != 0 then

     

    You can also add validation and formatting to fields by clicking on "More options" and entering code in "Trigger after update". Here's some code I added to TextTime...

     

    "// Test for colon and add :00 to end of TextTime if only minutes are entered";
    if index(TextTime, ":") < 0 then
    TextTime := TextTime + ":00"
    end;
    "// Test colon index for position 0 and add '0' to beginning of TextTime if true";
    if index(TextTime, ":") = 0 then
    TextTime := "0" + TextTime
    end;
    "// Test length of string after colon - truncate if greater than 2, add 1 '0' if 1, add 2 '0' if 0";
    let secLength := length(substr(TextTime, index(TextTime, ":") + 1));
    if secLength > 2 then
    TextTime := substr(TextTime, 0, index(TextTime, ":") + 3)
    else
    switch secLength do
    case 0:
    TextTime := TextTime + "00"
    case 1:
    TextTime := TextTime + "0"
    end
    end

     

    Good luck and have fun :)

    • Retired
    • Pete
    • 4 yrs ago
    • Reported - view

    Thanks for the extras.  I hope in time, I can be half as good....:-)

    • Marc_Di_Francia
    • 4 yrs ago
    • Reported - view

    Hi Pete,

    it took me 3 months to convert old Filemaker (4D, Ms Access...) skills into Ninox ones...

    Hold on !!

    Marc

    • dewittejacques
    • 4 yrs ago
    • Reported - view

    To Pete & Marc,

    I'm an old computing brain, as well, with, I'm afraid, bad habits from previous 4D ACI developments…

    We did use a lot variables and personalised functions to build a true RDB till we got a end-user interface with menus.

    I first thought my experience with 4D would help in Ninox. But I'm feeling more and more lost… 

    For instance, the FORMAT function is not working the same and I can't get what I was expecting.

    And I am still looking for a text function which gives us the length of a caracters' text chain! 

    I can't either find the explaination of the function INDICE in the 122 pages documentation ?!

    We don't even know to which version of the Ninox app this documentation is refering. There is not even publication date on top of it.

    And I do agree that it is a big loss of time to ask other people here these simple questions which should be accessible through a good documentation or wiki source.

    I admit you, people of Ninox are reactive, and very kind in the way you try to bring your help and skills to us. 

    But we are more than one to wait for a really structured and documented… documentation !!

    Please listen to our demand.

    Hold on ! and kind regards

    D'Jack

    • John_Halls
    • 3 yrs ago
    • Reported - view

    @Sean

     

    I've just tested something... Instead of

    if Miles != null and Miles != 0 then

    try

    if number(Miles) != 0 then

     

    Regards John

    • Sean
    • 3 yrs ago
    • Reported - view

    Hi John,

     

    Thanks, I wasn't aware of that. It works for me, but I don't think jessica will get it since she is more interested in glamour advice. I'm surprised more spam posters don't use this method since Ninox doesn't usually delete this type of post.

     

    Regards Sean

Content aside

  • 3 yrs agoLast active
  • 18Replies
  • 3533Views