1 2 Previous Next 22 Replies Latest reply on Sep 5, 2016 8:44 PM by user19752

    HourList between two dates

    cillion

      Hi there!

      I need to somehow make an hour list in a field called booked_keys. And I have already two fields that displays when the room is booked from and to. So in booked_keys I need a list with all the hours between theese timestamps:

      There would be good if I could do with an auto-enter calculation on booked_keys, or that booked_keys is a calculation field itself.

       

      Wanted result:

      32|7/18/2016 1:00 PM

      32|7/18/2016 2:00 PM

      32|7/18/2016 3:00 PM

      32|7/18/2016 4:00 PM

        • 1. Re: HourList between two dates
          Johan Hedman

          Create a Custom Function that creates that for you or if you think that is to complicated, have a Script Trigger trigger a script that creates it for you

           

          Script:

          Set Variable $StartTime ; Your Field

          Set Variable $EndTime ; Your Field

          Set Variable $Hour; 60

          Loop

               Set field Your result field ;

                         Case(

                              IsEmpty( Your result field) ; 32 & "|" & $StartTime;

                              Your result field & "¶" & 32 & "|" & $StartTime + 60

                         )

                         Set Variable $Hour ; $Hour + 60

                             

           

               Exit Loop if ( $EndTime = ($StartTime + $Hour))

          End Loop

          1 of 1 people found this helpful
          • 2. Re: HourList between two dates
            cillion

            Nice, if I want to try to make a custom function for this how will I be able to to the looping in there?

            • 3. Re: HourList between two dates
              Johan Hedman

              You need to write a recursive Custom Function

              Kalle Samuelsson: Writing recursive Custom Functions

              1 of 1 people found this helpful
              • 4. Re: HourList between two dates
                cillion

                Okey, so I tried starting on a recursive custom function for my own. But am on thin ice here Am I almost there?

                • 5. Re: HourList between two dates
                  Johan Hedman

                  You dont need $$ in a Let() function to set variables, just the name. No $ at all.

                   

                  Then you just have to try to work it out yourself. Don't bring in all functionality at first. Start of with just understand a recursive function and then add functionality

                  • 6. Re: HourList between two dates
                    okramis

                    Here's a CF of mine, returning the the time list (only inside one day, day overlapping events would need to be separated in multiple function calls). Room key and date could then be set through Substitute()-function:

                     

                    TimeListStep ( _tfrom ; _tto ; _start ; _step )

                     

                    /*********************************************

                    Otmar Kramis ©2016

                    Call like this:

                    Let ( [

                    _tfrom = Time ( 8 ; 0 ; 0 )

                    ; _tto = Time ( 11 ; 0 ; 0 )

                    ; _step = 60 //step in minutes

                    ; _start = "" //empty for initial function call

                    ] ;

                    TimeListStep ( _tfrom ; _tto ; _start ; _step )

                    )

                    *********************************************/

                    Let ( [

                     

                    _tfrom = Substitute ( _tfrom ; "." ; ":" )

                    ; _tto = Substitute ( _tto ; "." ; ":" )

                    ; _from = Hour ( _tfrom ) * 60 / _step + Div ( Minute ( _tfrom ) ; _step )

                    ; _to = Hour ( _tto ) * 60 / _step + Div ( Minute ( _tto ) ; _step )

                    ; _n = If ( _start = "" ; _from ; _start )

                     

                    ] ;

                     

                    If ( _start = "" ; GetAsTime ( _tfrom ) ; "" ) &

                     

                    If ( _n  > _from and _n ≤ _to ; "¶" & GetAsTime ( _n * 60 * _step ) ; "" )

                     

                    &

                     

                    If ( _n < _to

                     

                    ; TimeListStep ( _tfrom ; _tto ; _n + 1 ; _step )

                     

                    ; "" ) //end if

                     

                    ) //end let

                     

                     

                    Otmar

                    2 of 2 people found this helpful
                    • 7. Re: HourList between two dates
                      user19752

                      You don't need the list to check double booking, using self relation

                       

                      pk <> pk

                      and

                      fk_rom = fk_rom

                      and

                      rom_booked_from < rom_booked_end

                      and

                      rom_booked_end > rom_booked_from

                       

                      then related record is double booked.

                      1 of 1 people found this helpful
                      • 8. Re: HourList between two dates
                        cillion

                        How do you make an:  pk<>pk ? Like this:

                         

                        Then I need to change my bedpicker layout to show records from zbedselector_KURSDELTAKER3 then? And add the two fields ImageFilledGlob and ImageAvailebleGlob from z_BedSelector to KURSDELTAKER?

                        How will the Hide Object Calc be for the ImageAvailbleGlob then, or do I not need it?

                         

                        PS! changed the name of rom_booked_from and rom_booked_to to course_start and couse_end

                        • 9. Re: HourList between two dates
                          user19752

                          Sorry I can't get how "previous version" worked.

                          I wrote the "new" relation for getting double booked record, I assumed "hours list" is used for same purpose as using in relation

                          hourList = hourList

                          • 10. Re: HourList between two dates
                            cillion

                            okey. yes the previus list did not work. Do you mean that when I get the hourlist to work I could use it in the relationship as this? :

                            id = id

                            fk_rom = fk_rom

                            course_start = course_start

                            course_end = course_end

                            hour_list = hour_list

                             

                            And then have the bedpicker layout show records from zbedpicker_KURSDELTAKER3 ?

                            • 11. Re: HourList between two dates
                              user19752

                              If I got your question correctly, from another thread BedPicker for course attendees

                              you need to get list of all beds(rom?) that already booked in specified period.

                              The relation I wrote is usable for getting "one bed" is booked or not, and need to enter each bed id to get related record (FM don't allow constant without field to be relation condition). So not good for your situation.

                               

                              In your layout, I assume there are two global timestamp field that start and end, the define relationship between bedmap and booking would be

                              start <= course_end

                              and

                              end >= course_start

                              (If you allow end=another start, remove =)

                               

                              Then you can get list of beds(rom?) as

                              List ( booking::fk_rom)

                               

                              You can use this in "hide object" calculation as

                              FilterValues ( theList ; <<each fk_rom constant in the picker layout>> ) <>""

                              • 12. Re: HourList between two dates
                                cillion

                                okey I see!

                                My list called "BookedBeds" seem to be set right, but the hiding function dont work as expected, they dont get hidden if it is a part of the list. This is my hide calc now on the bed with fk = 32 :

                                FilterValues ( zbedselector_KURSDELTAKER3::booked_beds ; 32 )<>""

                                 

                                And my relationships:

                                Do I need to relate the booked_beds to make it work?

                                • 13. Re: HourList between two dates
                                  cillion

                                  Also recognized that the booked_beds List() dont grow if I book other beds within the same date and time.

                                  • 14. Re: HourList between two dates
                                    user19752

                                    Your relation is not what I wrote in the last, this time it is not self-relation. (or, left side is global so can be anywhere = can be self-relation but use different fields.)

                                    Where is the field that you enter finding criteria for "two dates"?

                                    1 2 Previous Next