You're going about time entries the incorrect way. What happens when someone creates a third entry for 1/2/2016?
In your portal table, you should just have three fields, Date (of time), Time In, Time Out.
You can then create an auto-enter calc field that subtracts Time In from Time Out on the same line:
PortalTable::TimeSpent = PortalTable::Time Out - PortalTable::Time In
You could then transform the result of that into hours for your display:
Hour (PortalTable::Time Out - PortalTable::Time In)
Note that calculation will round down to the nearest full hour, for something more accurate, the calculation would be more difficult.
Thank you Mike.
Actually, I am working on the attendance database. I took the data from the software. Once export the data in excel format, the data are like,
so now comes the question, could I import these raw data directly to filemaker and do the analysis? or
I should analyse the raw data first before importing the data to filemaker?
If you can import the data as a timestamp, or separate date/time fields into filemaker and they are formatted correctly from the excel source, then you can format it correctly.
However, it still is in the wrong format, with no guarantee as to validity that one "check in" = one "check out". So you may have to manually correct many records in your data.
Have you analyzed your data to see if it is indeed only TWO records per day? That's what I would correct and be 100% sure of in excel before you try and work with the data in FileMaker. You can create a script that will change the data into the correct format in filemaker, but the script could potentially break and throw off all remaining records if just one "set" of two records is off (only has one record, or three records).
Thank you Mike. So I need to do the analysis first before importing data to filemaker, right?
There is no way to import data directly to filemaker and then analysis the data.
Please read my response fully, I already answered that question...
You can create a script that will change the data into the correct format in filemaker, but the script could potentially break and throw off all remaining records if just one "set" of two records is off (only has one record, or three records).
+1 for MIke, but if you really want to show 2 lines for every day in portal, there may be ways for it.
I feel same "Time used" in 2 lines is something redundant, isn't this better?
1/1/2016 9:00 18:00 9
Ok. Thank you
Thank you. user19752