We have reset all users FileMaker related profile fields. Please take the opportunity to update your information,  this will provide background to members whom read your posts. Click here.

Jump to content
Ron Neville

Rental Inventory Availability

Recommended Posts

Ron Neville    1

HI Guys and all the best for 2017…

Could I please ask for advice on a SQL query  i require as part of an availabilities system I am building for a rental database where rental businesses rent inventory to customers for a time period. 

When a rental job is created it has a start & end date also a start & end time. When I go to my rental item list to add a rental item, next to each inventory item I want to see how many in total I have (own) and how many are available for rent for the date/time range added to the job.

Tables
Rentals
Line_items
Inventory

Each lineitem added to a Rental has a start/end timestamp so I need a SQL query that will count the QTY in the line_items table for each item for the date/time range of the job.   

E.g 
SELECT COUNT qty (Can SELECT COUNT be used in FM?)
FROM Line_Items
WHERE id_Product = X AND date > X AND dateEnd < Y

Any ideas or suggestions would be greatly appreciated.. 

Edited by Ron Neville

Share this post


Link to post
Share on other sites
Riley Casey    0

Glad you posted this question as I'm rebuilding a Filemaker 6 database that serves a rental based business and this functionality seems difficult to explain to FM developers.  I'm rebuilding it to use in FM 11 however.  Is there anything of value in those links for a version of FM that doesn't use SQL ( as I'm pretty sure FM 11 does not )?

Thanks

Share this post


Link to post
Share on other sites
Wim Decorte    443

You could use one of the free plugins that support SQL if you really want to use SQL...

Why rebuild in 11 though?  Can't help but feel that is such a waste of effort not going to the most recent version...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×