Logic with date and time for payroll

[sunil_prajapati]

Dear All
Wish all Are well

I m Trying to Developed a payroll app. which can be operated by any organization and their employee with QR and NFC and this system should be as easy that a less-educated security person can also operate and it should be less-step app like only one OR two-click operation.

i have already created structure and logic. but from the last day I stop at the date and time column.

many organization has two or three-shift, and their date is also changed after the last shift finished.

TABLE 1- “IN” , TABLE 2- “OUT”

1] last shift ends at 8.00 am, I want the initial date value trigger as per a specific time like today() date is today()-1 day after 12.00am to 8.00 am. or before this time value is as regular today(),
2] in the OUT table some time employees also duty out late then shift closed time but the date should be the same as per duty in date so that time I want a manual change date in same the above column that’s why I keep it as editable and initial. for this I add one more enum as the [previous day] when they hit this button date will ignore 1] formula and add today()-1.

thanks, everyone

@tsuji_koichi @Steve @LeventK

[Aleksi]

May I ask why do you want to use two different tables for this purpose?

[sunil_prajapati]

@Aleksi Thanks For Reply.

first of all, I tried a lot to set in Single Table from the last two days. but logically we have many scopes of in-out entries and sometimes combine entries can come at the same time as one person is coming and another is going. if the bellow point is possible then I also want a one-table entry.

most important that the handler of the app is not much familiar with any kind of system and the educated person so we need a single step entry just scan and auto-save and reopen. like maximum optimization of UI app will fully work with QR CODE.

we Have a Total of 6 Entries of in out in one shift.

---

if I use a single table then

---

the first duty in time will be as initial value DateTime(now())

and just after this, all other entry times are coming the same as first duty time in initial value.
like 1] lunch out 2] lunch in 3] break out 4]break in 5] final leave time

so if I used a formula that will auto change the value so that will goes wrong because one row has 6 entries which are updated frequently when any of the time updated.

if I use reset if with initial value formula DateTime(now()) for that I need two switches of enum / yes/no. and <> formula so in this case, I don’t want to put another switch in UI.

I want to make it very clean and easy like scan and auto-save DateTime.
so in this, I keep all entry table different so mostly they need to tap the menu then choose entry type and scan.

when first in entry add-in the [in] table it will trigger workflow and action of add row in another table which is our [single table] and data is [employee id],[date] and it has a virtual column with lookup/sheet formula so with employee id and the same date we can get all related 6 entry of date-time from our child table.

Right now I found a temporary one expression for my issue shift time is 8:00 to 8:00 so every 8:00 am our date will change.

If(and(time(now())>= time(12:00:00),time(now())<= time(7:40:00)),today()-1,today())

and a single enum button [previus day] so we can the formula contain 7:40 because of some employee comes early then start time 8:00 am.

this is my logic and solution but if anyone has a better solution & idea to optimize then I’m happy to use it.

[Aleksi]

Please check a sample app called “In duty” from www.appsheet.com/portfolio/531778 if it could be possible in your case.

[sunil_prajapati]

@thanks very much for providing a reference. will check just now.

especially I am trying this app with non-technical people so getting some deficalty

one more way I got via data change action in dack view. later I will check this also.

[sunil_prajapati]

got a two new ideas “time log”, and single button of “enum” this will help me in many apps.

again lot of thanks sir.

[Aleksi]

You’re welcome