Aggregating data based on date overlap

[thowell]

I'm using FileMaker Pro 11.

My application manages equipment rentals. The items available for rental are represented by table InventoryItems. The rental invoices are represented by table Invoices. The line items for each rental are represented by table InvoiceItems.

A simplified view of the structure:

InventoryItems:

InventoryItemID

Name

Quantity

Invoices

InvoiceID

CustomerName

StartDate and Time

EndDate and Time

InvoiceItems

InvoiceItemID

InvoiceID

InventoryItemID

QuantityNeeded

The problem I have is that I need to know, for a given date, what inventory is available for rental. In other words, given a date, I want to be able to list all of the records in InventoryItems and be able to show Quantity - Sum(InvoiceItems::QuantityNeeded for the matching InventoryItemID) where there is ANY overlap between the StartDate and Time and EndDate and Time for the two invoices.

I'm very new to FileMaker, so hopefully this problem isn't too sticky. If this isn't the correct forum for this question, please let me know.

Thanks in advance, =)

--TWH

[Lee Smith]

Take a look at the file Link called Bookings by Ray J. Cologon and see if it helps.

Lee

[thowell]

Interesting idea. Unfortunately, I don't have Advanced, so I can't create custom functions. Is there any other way to do this?

--TWH

[Lee Smith]

I haven't snooped under the hood of that file in many years, but it doesn't surprise me that Ray had used a Custom Function.

This file is often pointed to as a possible solution to different needs. I haven't explored his site for a while, so I'm not sure if he has left the earlier versions on his site, but the original file was done in v5, and was posted here Link.

BTW, even though I linked you directly to the post (file), be sure and read the whole Thread, as it has some great information on how this file came to be.

HTH

Lee

[Søren Dyhr]

When I saw this and the version of filemaker here luckily were 11, came I to think of this template:

http://www.fmforums.com/forum/showtopic.php?tid/215217/post/358321/fromactivity/myposts/#358321

...and instead of custom functions or comprehensive relational graphing must it be relative easy to convert it to this particular purpose?

Since the new portal filtering more or less replaces what we used Ugo's method for earlier!

--sd

[comment]

I don't see the connection myself. Perhaps this one might be closer:

http://fmforums.com/forum/showtopic.php?tid/168633/

[Søren Dyhr]

Alright let me make a template tomorrow showing how close the relational structure is to your template!

--sd

[thowell]

Okay, I've incorporated the method used in linked thread. I've added a Transactions table to my database. When an item is checked in a check-in transaction, the date, and the quantity are entered. When it is checked out a check-out transaction, the date, and the quantity are entered. So far, so good.

Here's my problem now:

In a typical workflow a user will select a Contact to create a new invoice. The user will then set the StartDate and EndDate for that invoice. Any line items on the invoice will be added as InvoiceItems (keys InvoiceItemID, InvoiceID, InventoryItemID). Additionally, Transaction records need to be created so that the reservation system can track the inventory available at any point in time. That works well enough for creating a new invoice, but suppose the invoice StartDate and EndDate need to change? What is the best way to update the Transaction dates? Initially I thought about changing the Date field in the transaction table to be a calculation based on the Invoice StartDate and EndDate, but that doesn't work because it also needs to be indexed.

Thoughts? I'm sure I'm asking elementary questions, but I just don't have much experience in FM Pro. I keep thinking of solutions I would use in MySQL, but a lot of them don't transfer. =)

Thanks in advance,

--TWH

[comment]

You'll need to script this (you can trigger the script on modifying the invoice's dates).

[Søren Dyhr]

Right I promised to make a template avoiding the use of custom functions - using fm11 mechanincs!

The attached template have yet unresolved issues with record locking in an multiuser environment.

But basically is this build upon an approach I learned from Denny Schlessinger some 15 years ago, with the rationale that the aspect must be properties/resources since these are the most unlikely to change in quantity, so the looping through these is next to nothing, compared with infinite measures of leases - Even though I was hoping for the calculation engine could have provided me with what I here script in the "looping" script....

But as is, would it solve B&B's and small hotels as well as Equipment rental shops issues with allocating stuff or rooms booked by clients for a given measure of time.

Now since I have shown all the inner mechanics of the solution on one page, doesn't it mean that the graph shouldn't be broken up into TOG's for each aspect, and by each it's dedicated layout.

I would welcome any suggestions to the riddance of the looping script, in order to make it more multi user'ish ...

Enjoy!

--sd

leases.zip

[comment]

1. I don't think your file addresses the issue of this thread, which IIUC is an issue of quantity: for example, you may have 20 microphones, 8 are booked from Monday till Wednesday and 7 from Tuesday till Thursday; the question which the solution should answer is how many are available for a new booking on Wednesday (5).

2. Why must it be so complex?

BookingSimple.zip

[Søren Dyhr]

1. I don't think your file addresses the issue of this thread, which IIUC is an issue of quantity: for example, you may have 20 microphones, 8 are booked from Monday till Wednesday and 7 from Tuesday till Thursday; the question which the solution should answer is how many are available for a new booking on Wednesday (5).

...provided they're returned in a timely manner - I know this and have been there for the for a decade or so - long enough to know that only dealing atomicly with each component ... have the dangerous side effect that you in a tight market can't afford to say no to something virtually possible, when the system tells you that it can't be done.

The danger is lumping the pulls and returns into one single sum, won't make you distinguish 3 different returns from each other which is somewhat overlapping ... this makes sometimes openings not catered for and similar ignoring openings. Displaying the matter is merely reporting in categories or here around ItemID's

2. Why must it be so complex?

Ha ha... Ugos method isn't particular network friendly ... layouts holding anything such as a portals based on unstored fields evaluation, takes for ever to render. I seems to recall you said something in the gist of this ... not particular long ago.

Now I feel I do see the some kind of latency with filtered portals rendering ... but wonders which is worse. I havn't investigated this thoroughly though - have you? Debi Fuchs and Blackwell might have an answer to this ... it's a matter of how many roundtrips getting the chunks each method takes over a network.

--sd

[comment]

wonders which is worse. I havn't investigated this thoroughly though - have you?

No, I haven't - but I suspect filtered portals would be slower, because they aren't drawing on the index. In any case, as with the Ugo method, I believe it's best to reduce the initial related set as much as possible before filtering it.

[Tim Rosencrans]

Not sure if this would work but in theory what your looking for is the number of items available in a time frame. I think the best attack would be to find the number of items rented at that time and calculate from there.

My plan would be to setup an instance of the invoice items table where the relationship is

item=item

and

Start date

and

End date>date

That relationship should hold all the items rented during that period and adding a summary field to total them gives you the number rented during that period.

Should work. not sure how well it would scale though.

[Søren Dyhr]

Well it scales pretty well, but when dealing with returns and submissions of several sets, will you not see a correct figure by simply adding up by type ... but it will give you a hint, but which could be flawed. It's a little complicated to explain ... I would try to think up an example.

In short would you need to have either a record for each day it "typing" or go down on component level to pull it off.

--sd

[comment]

The danger is lumping the pulls and returns into one single sum, won't make you distinguish 3 different returns from each other which is somewhat overlapping

I think that's a matter of building a display that would satisfy these concerns. Once you have split the in and out movements into individual transactions, any kind of display/report should be possible.

[Søren Dyhr]

You probably mean this well, but eyeballing something isn't hardly the realm of databases ... hence the use of data-mining as discipline. All efforts in normalizing the structure etc. is to get consistence and reliability in the data, somewhat the opposite of:

http://en.wikipedia.org/wiki/Allegory_of_the_Cave

--sd

[comment]

I didn't necessarily mean eyeballing. I think I understand your issue but I am not sure what would be a good way to display the data in order to reveal all possible manipulations. However, I am convinced that - since the data is there and is structured properly - any kind of display you can come up with should be possible.

[Søren Dyhr]

Well you almost said the same as I - either atomicy on components level or subdividing the time span into the shortest possible lease or rent ...

It's a chase in seeing where the shortest looping time might reside in a scenario ... there is obviously some trickery which could be pulled off via custom functions repeating fields and import between tables to provide splitting, which can be worth considering if each line in a huge inventory as well as large time spans is the name of the game.

I have thought of a metaphorish way to show what the problem here is. If you have a hotel would not fully booked show as say two avaliable for the entire period an enquiry is made ... the only problem is what is availiable one day is the bridal suite as well as another fairly decent and next day only two rock bottom rooms - should you ask the customer move around each day? You can almost see Basil Fawlty in action.

http://video.google.com/videoplay?docid=5009336058979425790#

Well this happens at my bowling place, but we're not eagerly condoling this ... since it distracts. It's exactly the same if you are providing gear to concerts, you can't interrupt a tour and give them something else.

Huge provisions of gear certainly helps on this problem, but usually is some kind of financing involved as well.

--sd