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?