14 Replies Latest reply on Mar 20, 2016 6:44 AM by dtcgnet

    Buggy script needs a tweak!


      Starting with a script that matches query reservation dates  with all existing reservations in all rooms, and returning ‘available’ for every reservation that does not conflict, the script produces an accurate list of matches ruling out those that conflict. The ones that do NOT conflict are labelled ‘available’ in a field.


      But the second script, meant to return just one instance of each room to expedite booking,  needs debugging.


      Script 2


      Perform Find(Reservation:testcurrentdate=”available’)


      //eliminates conflicts already found in first script, returning all date sets that are "available"


      Sort Records


      // places duplicate kf_propertyID found in first script records adjacent



               Set Variable [$PR: Value:Reservation::kf_propertyID]

               Go to Record/Request [Next]

               IF [Value:Reservation::kf_propertyID=$PR]

                        Omit Record

                        Go to Record/Request/Page //


               End IF       

               Go To Record/Request/Page

               [Next:exit after last]


      End Loop                    // the idea is end up with only one kf_propertyID that is also 'available'


      But  the script fails to omit one duplicate or leaves out one room entirely that is ‘available’. Otherwise it works.


      Any help is greatly appreciated.

        • 1. Re: Buggy script needs a tweak!

          Omitting a record always lands you on the next one, except when you are on the last record of the found set. You have to take care of that special case. I think this will work:


          Perform Find [ Reservation:testcurrentdate = "available" ]

          Sort Records [ kf_propertyID ]

          Go to Record [ First ]


               Set Variable [ $PR ; Reservation::kf_propertyID ]

               Go To Record [ Next , Exit after last ]


                    // omit following records with same ID:

                    Exit loop If [ Reservation::kf_propertyID ≠ $PR ]

                    // special case: stop after omitting last record:

                    Set Variable [ $is_last ; Get( RecordNumber ) = Get( FoundCount ) ]

                    Omit Record

                    Exit loop If [ $is_last ]

               End Loop

          End Loop


          HTH, Peter

          • 2. Re: Buggy script needs a tweak!

            Thanks for that. Your idea solved the main issue of returning all 'available' rooms. There are still some some dupes, possibly because the first script produces three or more records for three or more existing reservations in the same room that do not conflict with the query?

            • 3. Re: Buggy script needs a tweak!

              My mistake! It does work. (I left in a script step that should have been omitted)


              Many Thanks

              • 4. Re: Buggy script needs a tweak!

                A pattern I find useful:


                go to record/request/page [first]


                     If [ not aRecordIWant ]

                          omit record


                          go to record/request/page [next; exit after last]

                     End If

                     Exit Loop If [ get ( foundcount ) = 0 ]

                End Loop



                Chris Cain


                • 5. Re: Buggy script needs a tweak!

                  After your find, you have all of the ID's you need. The foreign key is the room ID. You don't need to loop through all of the reservations, omitting multiples. Simply switch to a layout based on the rooms, using Go to Related Record, and match all records in the found set. As long as you have a relationship where the f key you sort on relates to the primary key for the room table, then you'll instantly have all rooms that were found. There won't be any duplicates. No need to loop.

                  • 6. Re: Buggy script needs a tweak!

                    Hi and thanks. So I tried your idea using   go to related record [from table 'rental property]

                                                                                          Find Matching records[Replace;Rental Property::kf_propertyID


                    and only the first record came up, not the found set. What am I doing wrong?

                    • 7. Re: Buggy script needs a tweak!

                      Post a shot of your script.


                      Sent from my iPhone

                      • 8. Re: Buggy script needs a tweak!

                        Sorry, accidentally clicked send to early.


                        Read up on GTRR here:


                        Go to Related Record


                        Use GTRR and select the option to match the found set. Your find is finding only the room with the fk you're looking at.

                        • 9. Re: Buggy script needs a tweak!

                          Yes, that's it Thanks for the new trick!!

                          • 10. Re: Buggy script needs a tweak!

                            I have to ask a few questions:

                            1) how many users will be accessing your solution?

                            2) when are you setting all records back from "available"?

                            3) how many rooms and reservations do you anticipate having?


                            Here's why I'm asking: if more than one user is attempting to make a reservation at the same time, you will encounter problems since their searches will be attempting to mark rooms as "available" at the same time but using different dates. Also, if you are setting a field with "available", and there are a lot of records, you'll encounter slowness.

                            • 11. Re: Buggy script needs a tweak!

                              So in my first script, where query date ranges are tested against existing reservations, the status field that returns 'available' is cleared for all records on the first step.  I'm not sure if that is enough if different users use the program. Other than using the term available (or some other term) , how would the user know if the room for the queried date range was open?

                              • 12. Re: Buggy script needs a tweak!

                                One suggestion that will help initially with speed: find all records with "available" and use only clear those. That will cut down on overhead a bit.


                                If you have multiple users, you could keep that field as a calculation. Calculations take place on the client, so i might get a value of "available" on my machine whereas the calc might evaluate differently on your machine. If your requested start date and end date fields are globals, then each user can have his own values.


                                Just a couple of thoughts I had. HTH

                                • 13. Re: Buggy script needs a tweak!

                                  Thanks for  your help.  The 'available' field was set up to test the calcs in the first script, just to make sure they were correct.There are six IF's, two resolve into available, four that do not. I could just omit records from the search that fail any of the four tests instead of leaving that field blank, as is now. 


                                  Globals are used in the date query. It doesn't work without that.

                                  • 14. Re: Buggy script needs a tweak!

                                    Here's an option that might help you.


                                    Create two fields in the reservations table:

                                    1. ID_List_g. This is a calculation, result type is text. It's result is: IDList_smry.

                                    2. IDList_smry. This is a summary field using the "List of" type of summary, summarize kf_PropertyID.


                                    Unlike some summaries, List Of is very fast. As soon as you have a found set, this will give you a list of all of the Room IDs in the found set, and will place it in a global field. Create a "Room Reserver" layout, based on the RESERVATIONS table. Create one new Table Occurrence in your relationship graph, using the ROOMS table. Relate this new TO to the RESERVATIONS TABLE with ID_List_g equal to the PK in ROOMS. Add a portal to the Room Reserver layout, and base the portal on the new TO. As soon as you have a found set, the portal will show all rooms which have IDs in the list.


                                    ID_List_g is a carriage return delimited list of the rooms which have matched your criteria (the found set). It might look like:





                                    In the portal, you'd see rooms with ID 001, 002, 006. Those are the rooms which have met your criteria. Because that field is a global, it will work in a multi-user environment. Because it establishes a relationship, it'll be fast. Hope it helps.