filtering and summarize
I have this database with two tables, the first one (parent table) called "Registro" and a child called "Operacion"
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?
8 replies
-
This should answer the first question .. Setting the "D" or "N" value.
-
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?
-
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)..
-
I think the problem may be comparing 24 Hr time with 12 Hr AM/PM time.
Hora is AM/PM.
d2 is 24 HR
-
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().
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 -
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.
-
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.
-
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");
Content aside
- 6 yrs agoLast active
- 8Replies
- 2668Views