I have a user input field that is time specific. The user would input a duration of time that it took him/her to complete a task. That duration of time should be in the following format - 00:00:00, Hours:Minutes:Seconds.
I then need to be able to sort this field in a database table that is display based on shortest duration to longest duration.
What code would make this happen for my dataset collection?
Hey alan 
First of all, you need to get each value at a time, so we need to split this value to (hrs, mins and secs) using JavaScript 's method split() .
// Define a variable object with the necessary properties.
let value = { raw: '', hr: '', min: '', sec: '' };
// Split the value and save the array.
value.raw = $w('#timeInput').value.split(':');
// Store the splitted values
value.hr = Number(value.raw[0]);
value.min = Number(value.raw[1]);
value.sec = Number(value.raw[2]);
Now, the second part really depends on your scenario, if all you need is just the time regardless of the dates then store the values like this.
// Convert time to seconds.
let final_value = (value.hr*60*60) + (value.min*60) + value.sec;
Now you’ll have the time as seconds, that means when sorting, the less value (seconds) is earlier that, so when using ascending sorting the entries with less value (earlier) will come up first.
But, if you want to store dates too then it’s a different story.
Hope this helps~!
Ahmad
Thanks Ahmad. Do I put this code on the User Input page or the page that displays the information? I have three separate user input fields, Hours, Minutes, Seconds. The user inputs at least a zero in each field as the minimum. After they submit the fields to the database, it will then display a table with what they just submitted.
I need the Hours, Minutes, Seconds to be in the same format and sorted in the table that is displayed. In my situation, it would not make sense to leave it as total seconds being displayed.
What I have is called a results page. Runners will enter in the time they got for running a 5k race. Let’s say they ran 31:20, 31 minutes and 20 seconds. This person wants to see it displayed as 31:20 on the table and it should be sorted correctly based on other times. Let’s say the same person ran 30:33 the first time and 31:20 the second time. It should be sorted as 30:33 to be at top and 31:20 as next and so forth.
Does that make sense?
Thanks for your time and help.
@alan33921 Yes it does make sense, I thought that you’re using one field to collect the time, and my example was written based on this assumption, but if that’s the case, yes you need to get the value in seconds, store it (in seconds) - which I already explained above - then convert it to the format you want (hh:mm). Here’s how.
In the repeater’s onItemReady() function:
$w('#repeater').onItemReady(($item, data) => {
let time = data.time;
let sec = time % 60
let min = ((time-sec) % (60 * 60)) / 60;
let hr = (time - sec - (min*60)) / (60 * 60);
// then display it as you want, for example...
$item('#time').text = `${hr}:${min}`;
// And if you want to add the seconds you can too..
$item('#time').text = `${hr}:${min}:${sec}`;
})
Hope this helps~!
Ahmad
@ahmadnasriya Thanks. I think it helps. It makes sense in my head what we are doing here. Taking Hours, converting it to seconds, Taking Minutes and converting those to seconds. Add all the seconds together to be able to sort, and then display it back as Hrs:Min:Sec.
What doesn’t make sense to me is where do I insert the code? I have one page where the user inputs the data into the fields and then they click Submit. As soon as they click submit, it takes them to another page that displays all the data that has been submitted by that user, the one they just submitted and previously submitted data all in one table. This is where we want them to be sorted by shortest duration to longest duration.
So where do I input the code you have referenced above? What pages do I put them in and do they have to be in a certain order on the page?
I would assume when the user clicks Submit, then that is where the system does the calculations behind the scenes prior to taking the user to the page where the data is displayed. Obviously it does this really fast.
Thanks again.
@alan33921 The code in my original answer must be on the dataset’s onBeforeSave() function, and if you’re using wix-data to inset then just before the insert call.
Yes it does this before even saving the form to the database, and yes it’ll calculate it very fast since it’s a simple calculation as you can see, and when sorting on the other page, the sort will use the field that we used to save the seconds, less seconds (shorter periods) will rank higher.
If you need anything just reply back.
@ahmadnasriya I have 5 Event handlers that show up for my dataset.
dataset1_afterSave
dataset1_currentIndexChanged
dataset1_error
dataset1_itemValuesChanged
dataset1_ready_1
I also get an error for the repeater function in the code. Not sure how to resolve that.
I would like to show you my page and maybe that will help me better explain so I can better understand the process here:
If you can, here is my test page
darwinrasmussen . wixsite . com / nurses
Click on login just under the ANA logo. Fill out some fake info to Sign-up and login as a member. Then click on the profile dropdown and click on Enter Virtual Results. These are the data fields the user fills out. Fill out some fake info so you can see the data table after submit.
@alan33921 I completely understand process, the calculation needs to be set on the dataset’s onBeforeSave() event handler, create a dynamic event in the page’s onReady() function like this, and place the code inside it.
‘Enter virtual results’ Page:
$w.onReady(() => {
$w('#dataset').onBeforeSave(() => {
let value = { raw: '', hr: '', min: '', sec: '' };
value.hr = Number($w('#hours').value);
value.min = Number($w('#minutes').value);
value.sec = Number($w('#seconds').value);
let result = (value.hr*60*60) + (value.min*60) + value.sec;
$w('#dataset').setFieldValue({searchBar: result});
})
})
‘My virtual results’ Page
let tableData = [];
await wixData.query('collection').find().then((result) => {
if (result.length > 0) {
result.items.forEach(item => {
let time = item.time;
let sec = time % 60
let min = ((time-sec) % (60 * 60)) / 60;
let hr = (time - sec - (min*60)) / (60 * 60);
let item = {
state: item.state,
distance: item.distance,
time: `${hr}:${min}:${sec}`
}
tableData.push(item);
})
}
})
if (tableData.length > 0) {
$w('#table').rows = tableData;
}
Unfortunately, there’s no way you can achieve this using the dataset, you’ll need to use query and data-binding as shown above.
The blue keywords are the columns’ IDs, modify them accordingly.
I am getting a parsing error unexpected token with this line: $w( ‘#dataset1’ ).setFieldValue(searchBar: result);
On this line I am getting cannot use await outside of async function:
await wixData.query( ‘collection’ ).find().then((result) => {
Sorry @alan33921 ! It was a typo …
$w('#dataset1').setFieldValue({searchBar: result});
Just add the curly braces.
Regarding the second error, you just need to change the function into an async one, I don’t know what function the query is inside of, but generally you need to add the async keyword to the function.
export async function button_onClick(event) {...}
$w('#element').onClick(async(event) => {...})
$w.onReady(async function() => {...})
$w.onReady(async() => {...})
These are examples to give you better understanding.