10 Replies Latest reply on Aug 11, 2010 3:47 PM by AlexGodines

    Rent and Return Items



      Rent and Return Items


      I used the started solution invocing for my rental company. im trying to get the inventory to work. things are always going in and out everyday. i want a way to just add and subtract items from the inventory when i put them in the invoicing i got a return box in the invoice list and a rented box i just want a way to put in the qty of items rented and returned in that invoice to keep accurate track of the inventory so when a new order comes up i know what exactly is still in the shop.

        • 1. Re: Rent and Return Items

          The Invoicing starter solution isn't really set up the way I prefer for managing inventory. You'll need to modify the design or set up your own database solution for this.

          Do you want to track them by specific Item? (Ditch digger #12345 is rented)

          Or by type of equipment? (3 ditch diggers are rented, 1 is available to be rented...)

          The options are not mutually exclusive.

          • 2. Re: Rent and Return Items

            I want to just track the Item totals not specific items. like got 100 ditch diggers and i rent 50 so 50 ditch diggers are left. but they return 25 so now 75 ditch diggers are available.

            • 3. Re: Rent and Return Items
              Let me give you a Scenario. i got 10 tents and 20 chairs in my inventory. i get a order for 5 tents and 10 chairs. i make a invoice and get a Quote send it to the client. i dont want the inventory to update untill the order is confirmed(some kind of button or check box) once i get confirmation(use check box or button) to update the inventory. so now i ahve only 5 tents and 10 chairs available in stock when i need to check. now they return it i want to be able to put in my invoice that the items were returned and be able to update the inventory once again(button or check box)
              • 4. Re: Rent and Return Items


                I've been examining the starter solution to try to figure out how it currently manages inventory to see if we can insert a simple change rather than tossing everything out and starting over.

                While the basic table structure for creating and printing invoices looks pretty decent. The scripted approach leaves a lot to be desired and contains code that doesn't really make any sense to me unless I'm missing something obvious.


                There doesn't appear to be any safeguards to keep someone from clicking "update inventory" twice so the items listed in the inventory can be deducted from inventory totals just by clicking the button twice. A conditional format tells you that the inventory update was run, but nothing in the script keeps you from clicking the button a second time and screwing up your inventory. Also, there's no way to update the inventory correctly if you make changes to the inventory after printing. That's not a big deal for sales since sales invoices aren't normally subject to change once the sale is final, but for what you want to do with your invoices to show that items are returned--this is a significant issue. There also doesn't seem to be any way to void an invoice.

                It also has this gem in the update script.

                Set Field [ Line Items::Inventory Pull_Temp ; Line Items::Inventory Pull ]
                Set Field [ Line Items::Amount in Stock ; Line Items::Amount in Stock - (Line Items::Inventory Pull - Line Items::InventoryPull_Temp)

                The first line is useful in that the conditional format compares the total of these two fields to grey out the button text to tell that this script has already been run. Though why the script doesn't use the same test to keep the script from actually being run twice escapes me.

                The second line makes no sense at all: Inventory Pull uses an auto-enter calculation to copy over the Quantity field (why not just refer to the Quantity field?) So if the line is for 5 items purchased. The second line will evaluate as: Line Items::Amount in Stock - ( 5 - 5 ) or Line Items::Amount in Stock - 0!

                And Line Items::Amount in Stock is a separate field from Products::Amount in Stock and I haven't yet discovered a link between the two...

                Now throw in the fact that you can update inventory and then click a button in the portal row to delete the row--creating an inventory error that can't easily be fixed without even a warning to that effect and this becomes a recipe for disaster...

                Bottom line, I think we need to start from a blank piece of paper here as I find many issues with this starter solution's attempt to handle inventory.

                LaRetta, if you are reading this, now I see why you described using this Starter Solution as "starting off on the wrong foot"!

                • 5. Re: Rent and Return Items

                  General description of how I recommend using FileMaker to manage inventory:

                  Set up the Line Items table to work like an accounting ledger where Items received into inventory are recorded in a "debit" field and items removed from inventory are recorded in a "credit" field.

                  Typical fields for this table:

                  ProductID (links item to Products table)
                  InvoiceID (links items sold/returned to Invoices table)
                  TransDate (Auto-enter creation date, records when item(s) were added/removed)
                  TransDesc (text, describes the type of change (rented, return, out for maintenance, retired, etc.)
                  In (number field for items received)
                  Out (number field for items removed)
                  cBal (In - Out, shows total change to inventory for a given record)
                  sBalance ( Summary, Running Total of cBal, restart totals when grouped by ProductID)
                  UnitCost (number, looked up from Products)
                  cCost (cBal * Cost)
                  sTotalCost (Summary, Total of Cost)

                  A typical portal to this table would show the fields, ProductID, In, UnitCost, cCost with additional fields added directly from Products to add a Product Description (or add a text field to the above table that looks up the description.)

                  A second layout to this table would be set up in list or table view to function as a ledger showing all inventory changes and where changes not linked to an invoice can be logged.

                  To log the return of rented items, you can create a separate layout that lists the invoiced items with a button in the portal rows to click in order to return an item. The button's script can use the data in the current portal row to create a new line in the table, but with the quantity entered into the Out field instead of In. Separate Filtered Portals can be used to list the items rented in one and Items returned in another.

                  • 6. Re: Rent and Return Items

                    So i Scrap whatever i did in that starter solution and start a new blank layout? which one do i scrap the Product Layout or the Invoice Layout? or just the whole starter solution and just start fresh with nothing? and build from there

                    • 7. Re: Rent and Return Items

                      I'm recommending starting from a completely new file. I realize that's a bit much, but the more I dug into the starting point file, the more problems I saw with using it to manage rental inventory.

                      Many of the design elements can be kept and you can even import tables Such as Customers, Invoices and Products with their field definitions into a new file using Import Records with New Table selected as the target table for the import. You can then build the new Line Items table and import data into it, copying data from the original file and table if you have data there you need to keep.

                      It's still a lot of work to do, and maybe someone else has a better sugestion or a template they can share or point you to...

                      • 8. Re: Rent and Return Items

                        Cant i keep the same Template and just adjust the Lineitems? and product Table to fit each others needs?

                        • 9. Re: Rent and Return Items

                          It's a trade off, you can do that, but you may find scripts and other design features breaking on you as you modify the design in ways never envisioned for this file. Just make lots of backups and test existing features frequently.

                          • 10. Re: Rent and Return Items

                            Ok i got it to work. pretty much i set it so there a check box that if its pending the inventory can not be updated. i have to set it to confirmed to update it and it subtracts the items in the line items qty. But now i want to setup up a button  that gets the qty of a certain field to add to the amount in stock. pretty much the opposite of the other script. any help with this?