Jump to content
Ron Neville

Rental Inventory Availability

Recommended Posts

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

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

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.