Inventory Questions

[LoveTheBomb]

Ok, I'm somewhat new to FM. I run a short-term rental business. I have two tables, one with my inventory, one with rentals. What I want to do, is create a field in my inventory table, letting me know whether that item is in stock, or out on a rental. I have a field in my rental table indicating whether that rental is still out or whether it has been returned.

I want to create a calculation that looks at all the rentals I have had for a particular item, and if any of the rental records say that the item is out, then the field in the inventory table will say "Item Out".

Any ideas?

[mr_vodka]

How is inventory taken? Is there more than 1 qty for each item or is each item unique with some kind of unique asset number.

[LoveTheBomb]

Unique. Each asset has a serial number. The serial number is located on both the rental and the inventory table.

[mr_vodka]

I think you need a rental table that tracks all the rentals that happened as well as info on the rental, a transactions table that is related to the rental table that is basically a a line_item table, but tracks each in and out for each item.

With this type of setup, you could even set it up to only display available items for rental through something like a portal. Furthermore, you could track for reporting every time an item was rented and returned, average time out, how many times rented, etc etc etc.

[LoveTheBomb]

Really dumb question: What is a line_item table?

[mr_vodka]

Uhmmm here is a description, all the items in your recipts when you make a purchase are considered line items. Basically a list that has some type of group designation.

So in a line items table, you would have something like this.

Receipt1     Sandwich      4.99

Receipt1     Soda          1.99

Receipt1     Chips          .99

Receipt2     Burger        2.99

Receipt2     Beer          3.99

Receipt2     Chips          .99



or



Receipt1     Product1      4.99

Receipt1     Product3      1.99

Receipt1     Product5       .99

Receipt2     Product2      2.99

Receipt2     Product4      3.99

Receipt2     Product5       .99

etc.

There are lots of sample files on these forums that can demonstrate this.

Edited February 21, 2007 by Guest
formatting

[AudioFreak]

Uhmmm here is a description, all the items in your recipts when you make a purchase are considered line items. Basically a list that has some type of group designation.

So in a line items table, you would have something like this.

Receipt1 Sandwich 4.99

Receipt1 Soda 1.99

Receipt1 Chips .99

Receipt2 Burger 2.99

Receipt2 Beer 3.99

Receipt2 Chips .99

etc.

I'll take receipt # 2 please :(