SeedCode Support Forum

I am trying to integrate DayBack into a very complex and large legacy database. This database is all about booking hotel rooms (I am using grid view). Because of when it was built, the database is creating records for each day that a room is occupied. So, a visitor arriving on Monday and leaving on Wednesday would have 3 records in the events table.

This is obviously quite different from DayBack.

So, I am thinking this:
-I make start and end dates calc fields looking over into the existing data table.
-I rewrite the code that changes the start and end dates to go and create / delete the required records.

Is there any reason why this won't work? Thanks.

That seems like a lot of complexity to maintain. Moving, deleting, and editing records would all have to know that there are a series of related records to be acted upon, not just the record being modified. And while DayBack knows to redraw your changes to the edited event in the calendar's display, it doesn't know that it needs to redraw all these related records as well so I think you'd be concluding each edit with a full redraw of the whole calendar to make sure you catch changes to these related records: while that would work I do not think it would feel elegant as the whole screen painted each time you changed an event. Could be easy for users to lose the event they'd just edited.

I might take another approach. I'd create a new reservation record that spans the three days (one record). Then, if you need the three individual records (one per day) for billing or reporting purposes, create them as children of the parent reservation record (if you need these three records they'd stay in the table you have now, and you'd be adding the "reservations" table). This way things like status are properly an attribute of the reservation.

Now if you don't really need the three records for billing or some other purpose, I'd take this opportunity to get rid of that structure and collapse them into a single record as part of this development.

Thanks John. I need the record because there is a graphical "day chart" which also shows available rooms. Whatever I do, I need to avoid breaking things that already exist.

The problem is really one of sync. I need a new table which has a start date, end date etc (all the things that DayBack expects). I also think, on further reflection, that the start date / end date cannot be unstored calcs as things are likely to get very slow (as I assume DayBack is doing a find to draw the screen in the first place). So, it is tricky. Basically, whenever a new booking comes in from the bookings screen (making the multiple records), I then need to create a DayBack record. Whenever a DayBack record is created/edited, I need to create/edit the related records which the other part of the system uses. And, of course, there are numerous places in the existing system where edits can be made...

In a perfect world, you would get rid of the old and replace with the new but the system is so huge and complex that doing this is not a practical proposition. Hmmm.

Maybe there is some potential with auto enter calcs... on the start date / end date in the DayBack table. Or relookup...

The key problem is triggering the changes to the DayBack record in an automatic way because there are many ways the data can be changed and they are not all known. So something automatic in that direction would be fantastic. The other direction is not so bad. DayBack runs a script when an event is changed, the record is changed as normal and as part of the script that makes that change, I go off to the "child" records and fix them.

It is tricky. This database is huge (100 tables, 6700 fields, 800 TOs) and very old (from FM7 or FM8). The problem is that it is very hard to work on.

I think I may have a solution! I solution I see is to maintain two tables:
-INDIVIDUALROOMBOOKINGS (already exists - has one record per day).
-Events (new table for DayBack - just like your sample table). This is a parent to INDIVIDUALROOMBOOKINGS with one record per group of INDIVIDUALROOMBOOKINGS records.

In events, we change the start date and end date to be unstored calcs finding the min and max on the related records in INDIVIDUALROOMBOOKINGS.

This raises two problems:
1. -DayBack cannot edit the unstored calc
2. -Finds to operate the calendar are working across unstored calcs.

To solve 1, we just rewrite the script so that it creates/edits/deletes the related records in INDIVIDUALROOMBOOKINGS. For changes made outside of DayBack (which we want reflected on the calendar when they occur), we just have the unstored calc which will change and trigger a redraw (I guess - maybe wrong). Actually, this does not really matter as we don’t need a super live calendar like this. It is OK for the user to have to press refresh. Actually, I just did a test and this is the default behaviour of the calendar anyway. So, bit rambling that

I suppose it boils down to this: i) Dayback draws on the unstored calcs when it refreshes ii) When day back writes an edit (for example, a new end date) it writes it to a new date field “newEndDate” which is used by the script to deal with the related records. So, DayBack writes to one field and reads from another. So, question, will this trigger the DayBack redraw of just the affected event?

Problem 2 is easy to solve when you think about it the right way. You don’t want to do a find on the unstored calcs but that is OK, we don’t have to. You do the find in INDIVIDUALROOMBOOKINGS and then do a GTRR on the found set to the Events table (so finding the found set the calendar needs). Drawing the calendar will be slowed by the necessity of doing the unstored calcs but there won’t be that many since we are only dealing with the ones showing on the calendar at the moment.

Does that make sense?

There is one more problem. I need a record in the Events table whenever INDIVIDUALROOMBOOKINGS are created. So, I will have to find everywhere in the database where this occurs which should be manageable.

Hi ZPD,

This is an interesting integration challenge!

While it is possible to read from one date field and write to another in DayBack, I'm not sure at this point that this will make things simpler in the end. It seems that you will still need to capture all those places in the current system where IndividualRoomBookings are created, edited, or deleted. If you can find all places where Bookings are created, could you also find all places where Bookings are edited or deleted?

Also, if you edit one Booking the current system, are related Bookings edited as well? Or is each one manually modified as needed?

All the best,
Dan

I think it is a bit simpler:

-Your sample events is really pretty simple - start date, end date, start time, end time. I don't care about times so I can just leave those blank.

-So, converting the start date and end date to be calc based on Min(relationship to date field) and Max (relationship to date field) I can get the start date and end date. I have already done this and everything seems OK somewhat so far.

-In terms of capturing all edits/deletes/creates, this is only somewhat necessary. Edits are covered since start date and end date are calcs so they take care of themselves. Deltes are automatic too as you just get a Sample Event record with no start or end date (which I presume will just be ignored by DayBack) so the only real problem is creates. When records are created in INDIVIDUALROOMBOOKINGS I do need to create a record in Sample Events. So, two ways to solve i) find all the places ii) run a server side script that finds INDIVIDUALROOMBOOKINGS without Sample Event records and then just creates them. I actually have a script running doing that at the moment (to give me some real data). This does, however, have one problem which is that to run it needs to do an unstored calc find on the whole table and there are 103k records. So, may be a problem. Though I can set a flag for the ones I know are done and exclude those first which will greatly reduce the job.

I'll let you know how it goes. So far I haven't broken it completely!

One issue I can see is that the summary and description in DayBack are editable. These really aren't editable in my solution. I can work around by showing a error message and then discarding any change but would be better if this was "view only". Is there a setting for that?

You can't make these fields read-only via CSS, sadly. You can, however, map these two other fields the user *can* enter. You then change the field labels using translation: http://www.seedcode.com/pmwiki/index.ph ... ranslation

You could also switch from using the in-webviewer popover to using your own FileMaker layout for editing events: http://www.seedcode.com/pmwiki/index.ph ... entLayouts

And you're right that doing it with access privileges works but it's misleading to let the user into the field to begin with. =(