0

filtering and summarize

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?

8 replies

null
    • Mconneen
    • 5 yrs ago
    • Reported - view

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

    hora

    • ESM
    • 5 yrs ago
    • Reported - view

    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.

    • Mconneen
    • 5 yrs ago
    • Reported - view

    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).. 

    • blackie
    • 5 yrs ago
    • Reported - view

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

    Hora is AM/PM.

    d2 is 24 HR

    • blackie
    • 5 yrs ago
    • Reported - view

    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

    • ESM
    • 5 yrs ago
    • Reported - view

    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.

    • ESM
    • 5 yrs ago
    • Reported - view

    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.

    • blackie
    • 5 yrs ago
    • Reported - view

    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

  • 5 yrs agoLast active
  • 8Replies
  • 2666Views