1 2 Previous Next 29 Replies Latest reply on Apr 22, 2009 4:29 PM by claddam

    Advanced search functions

    claddam

      Title

      Advanced search functions

      Post

      Hi there,

       

      I have a question for any experts here, as I am stuck.

       

      I have 2 date fields (arrival and departure). I want to make a report or layout that shows all bookings for a specific date.

       

      How can I make a report or form displaying all information from arrivals and departures using today's date? I guess what I need is to make a calculation that searches in both date fields for a date I put in.

      Any help would be greatly appreciated :smileyhappy:

       

       

        • 1. Re: Advanced search functions
          philmodjunk
            

          Use a script:

           

          Enter Find Mode []

          Set field [tablename::arrivaldatefield, get(currentdate)]

          new record/request

          Set Field[tablename::departuredatefield, get(currentdate)]

          Set error capture [on]

          Perform Find []

          Set error capture [off]

           

          You'll probably want to include a sort to organize the records into two groups (arrivals and departures) and maybe some special handling for the case when there are no records found.

          • 2. Re: Advanced search functions
            claddam
              

            Thanks for that, that helps.

             

            Yeah about the sorting, because I have the time they depart and arrive, how can I tell it to put it in time order from the 2 fields?

             

            Any ideas?

            • 3. Re: Advanced search functions
              philmodjunk
                

              claddam wrote:

              Yeah about the sorting, because I have the time they depart and arrive, how can I tell it to put it in time order from the 2 fields?


              There may be a better way if I knew how your data is structured, but you could sort on both your arrival and departure date fields in the same sort:

               

              Sort by:

              Table::arrival date

              Table::departure date

               

              The above sort should put the departing today records ahead of the arriving today records.

               

              If you have some other field in your record that distinguishes your arrivals and departures, you could sort on that field and then set up sub-summary layout parts that will serve as sub-headings for your two groups of records.


              • 4. Re: Advanced search functions
                comment_1
                  

                If I understand the question correctly, you want to sort the events by the time they happen TODAY. Which is difficult, because for some of them it is the arrival that happens today, and for others it's departure.

                 

                The optimal solution would be to have two separate event records (in a related table) for each booking. Then you could simply find the events of the day, and sort them by time. You could do this even "after the fact" by finding the arrivals of today and importing them into an events table, then repeating the process for the departures.

                 

                 

                Alternatively, you could hack it by defining an unstored calculation field (result is Time) =

                 

                Case ( ArriveDate = Get (CurrentDate) ; ArriveTime ; DepartTime )

                 

                and sort by this field (you can use a global date field instead of Get (CurrentDate), so it will work for any selected date).

                • 5. Re: Advanced search functions
                  claddam
                    

                  Ok, so I kinda get where you are going with it comment.

                   

                  I also found I can put 2 portals on the one page to show this info. Only problem is that when I put a portal in, it displays only one piece of information. Anyways to tell the portal to display all entries?

                   

                  Cheers

                  • 6. Re: Advanced search functions
                    philmodjunk
                      

                    Comment is on the right track.

                     

                    If you can display arrivals in one portal an departures in another, there's a difference you are using in your relationship to distinguish between them. You can use that difference to group your records in a single report with a subheading for departures and a sub heading for arrivals. Describe your portal relationships and either Comment or myself can tell you how to do it.

                    • 7. Re: Advanced search functions
                      claddam
                        

                      Ok, so the relationships involved are none I think. There is one layout called bookings which has a tabbed area - one for arrivals, one for departures and one for payments with the client's information above the tabbed area.

                       

                      The only relationship I have is Bookings:Accommodations to Accommodations:Accommodations (a seperate table listing all possible destinations they go to).

                       

                      So, where do I need to start please Phil and comment?

                      • 8. Re: Advanced search functions
                        philmodjunk
                          

                        In a previous post you said "I also found I can put 2 portals on the one page to show this info." You can't have portals without relationships. If you have two portals, one for arrivals and one for departures, then you have to have a different relationship for each as each are displaying different records.

                         

                        Can you set up two portals?

                        • 9. Re: Advanced search functions
                          claddam
                            

                          So thats why its not working lol.

                           

                          Ok then, so I can create 2 new relationships. Do I need to duplicate the Bookings table twice and link it up with depature date and arrival date?

                          • 10. Re: Advanced search functions
                            philmodjunk
                              

                            Not necessarily. I'm just trying to identify a simple way to distinguish between arrivals and departures.

                             

                            Let's do the really obvious just for an example:

                             

                            Put a text field "type" in your table. Store "Arrival" in this field for arrivals and store "Departure" in this field for departures. (I'm not acutally suggesting you do this. I know that any given record is both an arrival and a departure.)

                             

                            Find your records.

                             

                            Sort them by "type"

                             

                            View the records on a list type layout where you've created a sub summary part that appears when sorted by "type". Set it to print above.

                             

                            Put the type field into the subsumary part and browse your records (fmp 10) or preview them (Fmp 9 or older).

                             

                            You'll see a report with the entries grouped under Arrivals and Departures sub headings.

                             

                            The challenge here is setting up a way to sort the records into "Arrivals" and "Departures".

                            • 11. Re: Advanced search functions
                              comment_1
                                 I think this is going around in a circle. There is no way to distinguish between "arrival bookings" and "departure bookings", when every Booking record has an arrival AND a departure date/time.
                              • 12. Re: Advanced search functions
                                claddam
                                  

                                Sorry Phil, one question, how do you "Store Arrival"? What does that mean?

                                 

                                comment, in that case, do I need to seperate the 2 and make 2 tables; one for arrival and one for departure? 


                                Thanks
                                • 13. Re: Advanced search functions
                                  comment_1
                                     Ahm.. one more thing: if you put a global date field somewhere (doesn't really matter where), and define two relationships to the Bookings table, one matching the global to the ArrivalDate, the other to DepartureDate, you can have two portals, one showing the global date's arrivals, the other departures.
                                  • 14. Re: Advanced search functions
                                    claddam
                                       Thanks comment, I will try that and let you guys know if it worked.

                                    Thanks for your help, greatly appreciated.
                                    Claddam 
                                    1 2 Previous Next