Ranges of dates

[peronn]

Im stuck, again. I know there is a solution but i just can't grasp it. I've been close but close is not good enough in this case. I hope you understand despite my lack of "correct words".

In my current application a user can book items from DateOut to DateIn. An item for example a speaker can have any number in stock ex. ArtNumber1 10 pieces.

So to continue the example. A user can book 4 of "ArtNumber1" between 8/7/2005 (DateOut) to 8/10/2005 (DateIn). Then a second use can book 2 of "ArtNumber1" between 8/8/2005 (DateOut) to 8/12/2005 (DateIn).

This will lead to the following information.

8/6/2005 -> 10 (in stock)

8/7/2005 -> 10 - 4 = 6 (in stock)

8/8/2005 -> 10 - ( 4 + 2 ) = 4 (in stock)

8/9/2005 -> 10 - ( 4 + 2 ) = 4 (in stock)

8/10/2005 -> 10 - ( 4 + 2 ) = 4 (in stock)

8/11/2005 -> 10 - 2 = 8 (in stock)

8/12/2005 -> 10 - 2 = 8 (in stock)

8/13/2005 -> 10 (in stock)

Well thats easy enough to figure out like this and on paper. But how can i creat a script / function that calculates the above? That is, takes the new, proposed DateIn and DateOut and find out if the use specified amount of ArtNumber1 is available during those dates? An example would be that a third user would like to book 9 of ArtNumber1 between 8/9/2005 (DateOut) and 8/11/2005 (DateIn).

Which wouldn't be possible at all and the user should get a custom dialog saying so.

All "booked" items ar stored in a seperate table called BookingHasItems that stores Articlenumber DateIn and DateOut.

I have tried different serches but have yet to come up with one that include all the right records. Even the tricky ones that have an In or Out date in the middle of the period that i wan't to test.

My brain has halted completly on this one so any help would be helfull.

Thanks!!

P

[Søren Dyhr]

The issue here is like the bridal suite in hotels, you can't throw the newly wed out of the suite until they leave - while you on the other hand can't book another couple into it before the previous leave, although you might have plenty of other rooms to choose from. Only Basil Faulty tries to get away with moving guest in his hotel to make rooms avaliable.

Before replying how many different types of items exists (a.k.a. roomsize), not numbers covering a certain size?? The solution largely depends on the load of data to process.

You could perhaps get an idea of a way to accomplish what you might be after, by tearing this appart:

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

--sd

[peronn]

Well the user add there own items (roomsizes) as they go along. Which would mean that there could be an "unlimited" number of items. Thats nothing that i controll.

Digging in to the other solution right now. But would still like a tip or two

Thanks.

[comment]

Anything you can do on paper, can be done in Filemaker (well, almost anything). You could run a script that does exactly what you have done "on paper" - loop between all days of the requested period, check the total of available items on each day, and return the minimum value found (you'd need to add another relationship to bookings from the global date field used for the loop).

Another option would be to split each booking into a check-out and a check-in. This would enable you have a running balance - just like a bank account.

Edited August 8, 2005 by Guest

[peronn]

Thanks for the help. But is it possible to perform a find that finds every booking that have any date within the requested period? For example bookings that have an DateOut before the requested period and a DateIn after. And those that start or end in the middle of the period?

That's what i can't figure out! :

P

Update:

I figured out that when looping thru the records that contain the correct article i can, with the help of an if condition, find if the current record affects the current number of articles during the period. TempInDate/TempOutDate is the OutDate and InDate of the booking im trying to make. And ArticleOutDate/ArticleInDate is the out and in date of the current article.

It looks like this:

If (ArticleOutDate <= TempInDate AND ArticleInDate >= TempOutDate)

But now my problem is that i can't get the correct number of articles that are in stock at the moment. There is an NumberOfArticles in the booked articles tabel. But if i add them up it will show that there is a negative number in stock and if i always select the highest one it won't be true since there can be one booking of 8 articles and one with only 1 article. This would say that 10-8 articles are in stock when there actually is 10 - (8+1) in stock.

Hmm...this is tricky ;)

Edited August 8, 2005 by Guest

[Søren Dyhr]

Yes it is, either by multicriteria relations or constraining found sets.

It makes sence to investigate this piece of graphic, to see the posible combinations:

...and of course a reading in help, regarding "Constrain..."

--sd

[comment]

Finding the relevant records is not a problem: you make a relationship based on the criteria you have mentioned, and go to related record (show only related).

The problem is that bookings, as Søren's diagram shows, are like prison sentences. They can be consecutive or overlapping. Filemaker sees all found/related records as one big lump, so summing them is ambiguous.

Unless your records are split into check-outs and check-ins, you will have to go from date to date, not from record to record.

[peronn]

well i've managed to get it to prevent doublebookings, but it feels like sort of a brute-force solution. Cause you can't really find out how many are available just that during the period that the user specified there won't be enough articles left to make the users booking.

Therefore i'm interested in the check-out, check-in, let's just say i read it but didn't understand it. Would you please write a couple of lines explaining it?

Thank you!

[comment]

Here's one possible approach. I haven't had time to consider if it might be simplified, but it illustrates the basic method.

bookingTransactions.fp7.zip

[peronn]

Thank you for all the help!

[chevell]

I have torn this database apart and I've got most of it figured out. I just can't quite tweak it enough to get it to do exactly what I want.

Here's the deal. I have 10 laptops people can borrow. I want the user to be able to search a date range and it will tell the user the number of laptops available in that range and the designation numbers of those machines.

Right now, I can put in a date range and see what is available after a certain time, but not before another. In otherwords, if i check in all 10 laptops on 5/10/06 and then check out one on the 15th and another on the 17 and then search for laptops available from 5/12 to 5/20, I should see a balance of 8 laptops. Unfortunately, I 0. I have no idea why, except that of course, there are no entries in the booking items table that match > or = to 5/12 AND less than or equal to 5/20. So I think its just a minor tweak to get it to show correctly.

Comment, thanks for this database, i have learned so much from it and have applied a lot of what you did here to my own solutions. Any help would be greatly appreciated. (You can download the DB above, I have not modified the schema, just deleted and readded records to suit my situation.

Thanks all!

[comment]

I really don't know. I have downloaded my own file, deleted all booking records, entered exactly the data in your example, and it seems to work fine.

bookingTransactions2.fp7.zip

[comment]

I have taken another look at the file, and found two mistakes that would have effect in case of multiple products. Sheesh, with 15 downloads in 9 months, you'd think someone would have noticed...

Anyway, here's the corrected version.

bookingTransactions.fp7.zip

[chevell]

Ok, I see what you did there. Here's what i just did, maybe I'm missing the point.

I removed all the booking items and all the books, so essentially, there was nothing in the database except the two products. Then I added a booking: in 1/1/06, quantity one, for product alpha.

When I go to products alpha and enter today's date, i should then see a quantity 1 in that field, as that product has not been checked back out. But there is nothing there. Is that not correct?

Thanks for your help.

[comment]

Ah, I see the problem now. When there are no transactions in range, there's no result returned. Thanks for finding the bug. I hope I have fixed it, but there's another point bothering me:

The entire system of finding the available quantity rests on a cascading unstored calculation, that takes into account ALL transactions of a product. This is fine for a demo, but in real life it will get slower and slower, as the transactions pile up.

You should consider switching to a method where the "running balance" of a product is stored. For example, each transaction could look up the balance from the previous transaction of the same product . However, this would require you to manually intiate a relookup after editing a previously entered transaction.

bookingTransactions2.fp7.zip

[chevell]

I see your point. At this point, transactions can be deleted after a month or so, as its just 10 items. I can create a script to purge and save only the last, say 5 transactions on a single product. Thanks for all the help. I'll see what you did here and apply it to my solution. Hope it works! Thanks!

[chevell]

WOW, I think its almost there... I think there's just one last thing. Take a look at the records I added back into this one. Tell me if you can see why the cquantity is still one, even though I checked it in on Jan 1st, and out on May 23rd. Thanks!

bookingTransactions2.zip

[comment]

Because the Quantity field of the May 23 Booking record is empty?

[chevell]

DOY! Thank you sooo much! I was so busy looking at all the calculations and relationships that I completely missed that. Awesome! I appreciate your help very much.

[comment]

You might want to have that field auto-enter 1 on creation.