filtering and summarize

Ninox Profile

I have this database with two tables, the first one (parent table) called "Registro" and a child called "Operacion"

Main view

On the "Operacion" table I need a script that fill field "Clase" with letter "D" when on the field "Hora" is filled between 6:00AM to 6:00PM and return letter "N" when "Hora" is filled between 6:01PM to 5:59AM

Also I need a script on the field "H_Dia" (on the table Registro) to calculate the qty bewteen first to last "Horometro" field (on the Operacion table) that have the letter "D" on the "Clase" field.

Can anyone pls help me with this?

Ninox Profile

This should answer the first question ..  Setting the "D" or "N" value. 

hora

 

Ninox Profile

Hi, tks for the hint, but I'm doing a mess. Attached screenshot shows the field 'Clase' does not correspond to the 'Hora', seems to me the "N" and "D" are filled whitout following the formula. Might I wrote the formula wrong? Or something is missing?

Captura de pantalla 2018-10-16 a la(s) 9.19.44 p. m.

Captura de pantalla 2018-10-16 a la(s) 9.09.21 p. m.

Captura de pantalla 2018-10-16 a la(s) 9.20.06 p. m.

Ninox Profile

I am a bit stumped.. I am sure we are both looking past something simple.. Mine (mac os/x and iPad) version works perfectly.   

I am not sure why you have a number format on the formulat.. It retuns a "D" or "N".. 

Maybe double check what the format statement is returning.. Create a formula only for that and see what it looks like.    I do recall reading here in the forum that dates are impacted by how you have them configured on the base platform (Mac OS/X for example).. 

Ninox Profile

I think the problem may be comparing 24 Hr time with 12 Hr AM/PM time.

Hora is AM/PM.

d2 is 24 HR

Ninox Profile

Looks like format() is always returning a AM/PM time for me.

 

You can set the format of the time to 24 HR in the Hora field. Then you won't need to use format().

Screen Shot 2018-10-17 at 8.12.37 PM

 

I also had to do text(Time) to get it to work in my environment.

let d1 := "11:00:00";
let d2 := "20:00:00";
if text(Time) >= d1 and text(Time) <= d2 then
"D"
else
"N"
end

 

Ninox Profile

I did made some changes at the formula on the field 'Clase' (see attached) pls compare this new one from the one received above. Also did change the format on the field 'Hora' which is used to calculate the 'Clase'. Well now it seems working as expected; the only thing it bother me I got this by blind trial but I have not clear idea why it is working.

Captura de pantalla 2018-10-17 a la(s) 7.08.10 p. m.

Captura de pantalla 2018-10-17 a la(s) 7.08.41 p. m.

Captura de pantalla 2018-10-17 a la(s) 7.09.11 p. m.

 

 

Ninox Profile

Update. I did update further the formula (attached here) and now is working well; I also think I'm undestianding better how this formula works.

Captura de pantalla 2018-10-17 a la(s) 7.28.20 p. m.

Ninox Profile

I was wonder how to get format() to work on my system. It looks like format() wants a date time, so I added today() to the time, and it worked.

let t := format(today() + Time, "HH:mm:ss");

Reply