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 ) ]
Exit loop If [ $is_last ]
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?
My mistake! It does work. (I left in a script step that should have been omitted)
A pattern I find useful:
go to record/request/page [first]
If [ not aRecordIWant ]
go to record/request/page [next; exit after last]
Exit Loop If [ get ( foundcount ) = 0 ]
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.
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?
Post a shot of your script.
Sent from my iPhone
Sorry, accidentally clicked send to early.
Read up on GTRR here:
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.
Yes, that's it Thanks for the new trick!!
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.
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?
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
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.
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.