Field to Show if Kit on Hire

[nutthick]

I'm trying to display a field that tells the user if a piece of Kit is on hire, or in stock. I have a kit table and a hires table. At the moment a script checks to see if there is an entry in the hires table for the piece of kit. It then checks all found hires to see if one doesn't have a return date (each hire has a start and return date). If it doesn't have a return date it must be on hire, if it does, it is in stock. Currently the user has to click an update button for each piece of kit, to check it's availability. Is there any way to put all the script action into a single field, so it updates on it's own?

[-Queue-]

Create a relationship between kits::serial to hires::kit_serial and define it to sort by hires::date_return. Then create a text calculation in kits like

Case( not IsEmpty(hires::serial) and IsEmpty(hires::date_return); "Not " ) & "In Stock"

where 'hires' refers to the new relationship.

[Søren Dyhr]

But does it solve if a hire of a single item collide with it's existence in a kit, shouldn't it be a recursive table structure instead??? I trying to find previous examples in these debates covering this topic ...I seem to be remebering a Bob Weaver template like the many2many recursion from Moyer and Bowers book "Advanced Filemaker..."

At least do I need to read that chapter again!!!

--sd

[-Queue-]

I'm not sure what you're asking, Soren, but since the relationship is sorted by date_return (ascending), if there is an empty related date_return field, it will be the first one referenced and IsEmpty(hires::date_return) will be true. Otherwise, it should be in stock.

[Søren Dyhr]

Ah! but some items might migrate from kit to kit or even hired as it is alone, making it imposible to gather the other kits ...my expirience with hire/rentals is that having wagonloads of every item defeats the object of having a tool to manage it. Systems are introduced because some of the resources often makes a tight match to circumvent ...while the gathering in kits makes the booker remember all the less obvious objects the kit also include.

So collitions might rule some of the kits out, due to a missing part booked elsewhere. But the user must be able to suggest the customer alternatives, or a price offer for a subrental!!

But your suggestion might already include such a recursive structure, without me being abel to spot it yet?? But thats because the model used here is considerably away from the framework I use to think inside. I participate in this thread of eager willingness to question the models i use ...I might be in for a gathering new knowledge. So please carry on explaining!

--sd

[-Queue-]

I think the definition of 'kit' needs to be clarified here. I understood it to mean clothes since nutthick is from London. If it means set, and each piece of the kit could be transferred between sets, then yes, there is a bigger problem to solve here.

We'll have to wait for a response before we know if it's a simple solution or a more complex, possibly recursive one. Thanks for your input though. I always enjoy butting heads with a wizard like yourself.

[nutthick]

I only added my location details a couple of days ago. I didn't think it was going to cause problems

When I say Kit, I mean various computer equipment. I think you have the general idea of what I'm trying to achieve, nothing that unusual. If you have a particular structure that you think would be a better system, then I'm willing to change things. The dB only has test data in it at the moment. Obviously if someone forgets to put a return date for the kit into the field, then the whole thing falls over, but you can only hold someone's hand so much.

HTH

PS So what does 'kit' mean in the States? I had a friend working over there who said he was going outside for a fag. I still don't think they fully understand what he meant.

[Søren Dyhr]

I mean various computer equipment. I think you have the general idea of what I'm trying to achieve, nothing that unusual.

But every item is uniquely belonging to say kit #114 and no single component in the kit comes in pairs???

--sd

[Søren Dyhr]

However if the approach suits your purpose, might this come in handy as well:

http://www.fmforums.com/threads/download.php?Number=133778

From the thread:

[nutthick]

Thanks S

[-Queue-]

Did you try my relationship and calc suggestion already?

[nutthick]

Not yet -Queue-, I got pulled onto another project for a bit. I hope to get a look at it later on today.

[nutthick]

-Queue-, that worked perfectly, thanks alot.