Schema for relationships based on information from the parent table

[Angus McKinnon]

I’m responsible for a Filemaker solution that manages rental equipment. It has slowly evolved over the years, as the requirements have grown and I’ve tried to keep up. 

We’re beginning to hit some limitations that I’m thinking may be down to the original structure of the database, so I’m looking for some advice and input on how best to address the issues. 

 

The fundamental layout of the database is fairly simple. We have an Orders table which is related to an Items table. Users setting up an order first select the customer, and the dates for the rental. There’s a Date Out and Date In. 

Rental dates frequently get “nudged” when customers’ plans change and they request to pick up earlier, or return later. So much so that I’ve added some specific buttons for this that just use a Set Field command to increment by one. 

 

This arrangement has served us well for a good number of years. However, I’m beginning to explore doing more with the Items table, and need to be able to include the Date In and Date Out in relationships. (For example, spotting clashes where the same item is booked out on overlapping dates.)

I’ve added “Date Out” and “Date In” fields to the Items table.  Simplest way seemed to be to define them as calculations, (Item:Date Out = Order:Date Out) However, if I do this I can’t use them in relationships because they cannot be indexed. So instead the fields are populated using a lookup when records are created in the Item table. 

Of course, the problem then is that the date fields in the Item records are static, and don’t change when the date fields in the Order record are modified. 

 

The best workaround I’ve come up with so far is to have a looping script which spots the inconsistencies. However it takes a while to chew through all 60,000+ item records. I’ve also added a few pieces of conditional formatting to highlight problems. But as things stand, it’s possible for a change of date to go completely unnoticed by users when looking at item availability. 

The next step would probably be to change the “Nudge” process, from a simple Set Field command to a script that adjusts the fields in the Items table as well. But the buttons are scattered throughout the solution, in many different contexts, so this would be a substantial job. Speed is also quite crucial here, since they’re often adjusted whilst staff are on the phone to customers. 

 

I’m not sure if there’s a better angle to approach this problem from, or something obvious that I’ve overlooked entirely? 
 

[comment]

13 hours ago, Angus McKinnon said:

spotting clashes where the same item is booked out on overlapping dates

What kind of warning are you looking for when that happens, and at what exact point?

Maybe you could work with a relationship that looks for overlapping orders, then filters the portal to those with same items only?

 

[Angus McKinnon]

2 hours ago, comment said:

What kind of warning are you looking for when that happens, and at what exact point?

Maybe you could work with a relationship that looks for overlapping orders, then filters the portal to those with same items only?

 

Most of the records that are added to the Items table are populated via a lookup from a separate Products table, which is essentially a catalogue. There's a ProductID field on both sides of the relationship as the key. 

I can use the ProductID to find alike items, with a self-relate between two occurrences of the Items table.

The part I'm struggling with is finding the date overlap and expressing that in a relationship. I'd tried setting this up in standalone testbed solution, and couldn't get anywhere setting up a relationship using the existing fields. Another idea was populating a repeating field in the Items table with every date between Date Out and Date In. Then using this, along with the ProductID, as the key for the relationship. 

The drawback with the repeating field is that it needs a script or calculation to populate it, and of course I need a repetition for every day that an item could conceivably be on hire for. (Most tend to be short duration, but we have some outliers that are many months...)

And, of course, the Date In and Date Out have to be present in the Items table, which gets me back to my original problem of keeping those two fields up to date when there are changes to the dates on the Order itself. 

[comment]

I am afraid we are not talking about the same thing.

My suggestion is to divide the problem into two parts.

In the first part you define a self-join relationship of the Orders table that identifies orders that overlap the current order's time span. This is easy to do using the existing, stored, fields of the Orders table:

Orders::DateIn ≤ Orders 2::DateOut
and
Orders::DateOut ≥ Orders 2::DateIn
and
Orders::OrderID ≠ Orders 2::OrderID

The second part is to identify which of the overlapping orders are conflicting - i.e. have a same product. This could be done in a number of  ways, for example filtering the portal to Orders 2 by a calculation of:

not IsEmpty ( FilterValues ( Orders::ProductIDs ; Orders 2::ProductIDs ) )

where ProductIDs is an unstored calculation field =

List ( LineItems::ProductID )

Any records displayed in such filtered portal would be conflicting. You will have to make an additional effort to see exactly why they're conflicting, but perhaps it does not matter?

Anyway, the idea is that the number of overlapping orders should be fairly small, so using an unstored calculation to find the conflicting ones among them should be sufficiently quick. Otherwise I see no choice but to move to a denormalized solution where the dates need to be replicated in the LineItems table, and you must take great care that this happens on every relevant layout, in every relevant scenario.

---
Caveat: untested code.

 

[comment]

3 hours ago, comment said:

The second part is to identify which of the overlapping orders are conflicting - i.e. have a same product. This could be done in a number of  ways

Here is another way you could do this. It uses conditional formatting to identify the exact items that cause the conflict with other orders.

Again, more work is required if you want to see exactly which overlapping order has the offending item, but it might not be worth the trouble.

 

BookingItemsConflict.fmp12

[Angus McKinnon]

Thanks to Comment's help, I'm making some headway with this. 

The most basic problem was that I'd got my logic mixed up with the initial relationship, that's now working quite effectively. 

When users are adding items to an order, it's done in a portal. I've now been able to add a couple of fields here, showing the number of overlapping orders that use this item, and the total number of items already booked out that are overlapping. 

(And, as suggested, I've been able to add a bit of conditional formatting which highlights the field in red if the total number of items exceeds the number we own, which is stored in the Product table)


This is all great, but it's got me up to the next obstacle. Whilst orders overlap, they don't necessarily all coincide with each other. This means that the total items required is off. I've attached a screenshot of a separate planner layout to illustrate this. 

On the planner, orange means the Date Out, when the item leaves our possession. Yellow is the day it returns, and green is the days in between when it is out on hire. 
There are three orders in the screenshot. Whilst both the top and middle orders overlap with the one in the middle, they don't all coincide. So if each order had two items on it, we'd need four items in total to fulfil all three orders. However the current relationship would suggest that six are required. 

What I've got in place at the moment serves as a useful warning to users that there might be capacity issues, however it would be far better if I could give them an accurate figure. 

One workaround would be to provide a button that opens the planner layout constrained to that product over the dates required, but since it runs entirely on conditional formatting it is rather slow. Plus it's preferable if users see an accurate count immediately, without moving away from the task that they're on. 

I suppose what I'm needing to do is calculate the max items required for each day for the duration of the order, then returns the largest number out of all the days. But I can't think of a way of doing that without using repeating fields, which is awkward because hires can be literally any length of time. One month isn't uncommon and some outliers extend to six months. That would mean a lot of repetitions, but on the other hand these longer hires will really mess with the numbers on the current solution...

I'm not sure if what I'm looking for here is even possible in Filemaker. There are some cloud-based Rental Management systems that have similar functionality, but I'm guessing they're build on SQL or their own proprietary code. 

[comment]

Just a few random thoughts:

I think there are two separate issues here: one is detection of conflict (or shortage) and the other is display.

For display I would definitely look at repeating fields. A repeating field can have up to 32k repetitions. But you cannot build a layout showing a table with 32k columns, and even if you could, no human eye would be able to take in such enormous amount of data. What you want here is a way to scroll the display - and that means you can use a lot less repetitions, for example just enough to display a month at a time.

But that would also mean that you wouldn't detect a potential problem before you scrolled through all relevant months. If you prefer that "users see an accurate count immediately, without moving away from the task that they're on" then you'll need another method, whether in addition to or instead of the display.

Of course, once you move to a scripted procedure there are a lot more options. Even using SQL. 

 

[Angus McKinnon]

6 hours ago, comment said:

I think there are two separate issues here: one is detection of conflict (or shortage) and the other is display.

Spot on. Display seems to be working fine at the moment, the planner layout seems to work well with the users. 

It's the quick/obvious detection that is the problem. It's not unusual to have someone entering items onto an order whilst a customer is reeling them off down the phone, so there is no time to open up a planner for every item. 

The numbers that I've got appearing just now are enough to alert a user to a potential problem, but they'd still need to open up a planner layout to be able to confidently tell the customer whether we've actually got availability or not. 

 

6 hours ago, comment said:

But that would also mean that you wouldn't detect a potential problem before you scrolled through all relevant months. If you prefer that "users see an accurate count immediately, without moving away from the task that they're on" then you'll need another method, whether in addition to or instead of the display.

I imagine, if I were to use repeating fields, what I'd need is a way of getting the maximum value from all the repetitions. That's going to be the critical figure (highest number of overlapping quantities) that would need to be presented to the user. Coming off that, I could use conditional formatting or some other method to make it clear that there's a potential issue with that item. 

On the other hand, I'm a little concerned that I'd be loading down the solution with a lot of unstored calculations which could slow things down for all users...

[comment]

1 hour ago, Angus McKinnon said:

there is no time to open up a planner for every item

Well, you can either run a check on each item as it is entered or wait until all items are entered and then find out that the very first item is not available...

There is also the problem you mentioned in your original post: if you change the dates of an order, then all items need to be checked anew. 

I am afraid I cannot think of an efficient method to handle the checking without scripting something. It could be the process of propagating the dates from the order to the order's line items (as discussed earlier), or the process of checking the availability of each item of the order. But it seems that the critical issue is here is to design the user's workflow (and force them to follow it).

 

1 hour ago, Angus McKinnon said:

That's going to be the critical figure (highest number of overlapping quantities) that would need to be presented to the user.

Let me throw out an idea: suppose you have a table of dates with a record for each date of the order's duration. And every such record is related to every order that overlaps that date. Using a global field or a variable to hold the item's ID you could have each record calculate the quantity of that item that is booked for that date and log the max value among them. The exact method depends on whether an order can book more than one unit of the same product (IOW, does your line items table have a Quantity field). But before getting into the details let's see if it fits into the overall scheme.

 

[Angus McKinnon]

20 hours ago, comment said:

Well, you can either run a check on each item as it is entered or wait until all items are entered and then find out that the very first item is not available...

A second or two of a pause whilst a script runs in the background would probably be acceptable, but if I reuse the existing planner layout it would need to open in a new window, and requires the user to look over it and discern any potential problem... difficult whilst a customer is still yabbering in their ear.

20 hours ago, comment said:

There is also the problem you mentioned in your original post: if you change the dates of an order, then all items need to be checked anew. 

Very true - I've been ignoring that issue for now. I suspect I can probably work around the problem by running a scheduled script to catch any discrepancies, and possibly reinforce that with a bit of conditional formatting to highlight any changes that have occurred since the script last ran. 

For the order entry scenario, the dates shouldn't be a problem on the items that the users are picking, as the fields lookup directly from the Order record when each Item record is created. 

20 hours ago, comment said:

Let me throw out an idea: suppose you have a table of dates with a record for each date of the order's duration. And every such record is related to every order that overlaps that date. Using a global field or a variable to hold the item's ID you could have each record calculate the quantity of that item that is booked for that date and log the max value among them. 

This sounds intriguing, almost like a join table... 

21 hours ago, comment said:

(IOW, does your line items table have a Quantity field).

Yes, it does. We've got a Quantity field in the Items Table, and it defaults to 1 if nothing is entered, so every line item will have a quantity value. 

Each record in the Product table has a field for the quantity that we own, which again has 1 as the default. 

[comment]

Here's a sketch of my idea using a dates table. For now it just produces a JSON showing the maximum total booked quantity of each resource over the relevant period. Hopefully you can see it could be expanded to provide any kind of display you find convenient.

Unfortunately, I found it didn't work reliably without a pause between each resource count, so there is a minor flash. Possibly this could be fixed by moving the logic to SQL? For now I am happy just proving the concept.

 

 

 

BookingItemsMaxQtys.fmp12

Edited January 30 by comment