3 Replies Latest reply on Jan 24, 2016 5:24 AM by AdamHorne

    Trying to find the correct approach on tracking rental gear...


      I’m having an issue trying to decide an approach to take with a database I’m using to track equipment RENTED out.


      For a simple and ROUGH explanation, lets say I have (4) tables.  EQUIPMENT, INVENTORY, ORDER, ORDER_LINEITEMS



      My ERD is as follows: https://thedropshare.s3.amazonaws.com/Screen-Shot-2016-01-22-12-37-30.png



      Lets say I have (4) records in INVENTORY, (3) using the same EQUIPMENT ID: https://thedropshare.s3.amazonaws.com/Screen-Shot-2016-01-22-12-16-01.png



      Now I create an order to check them out… Here is where I might have gone wrong.  I’ve set up my OrderLines to group by EquipmentID. When I scan out an item to CHECK OUT it will do the following script steps:

           - Captures the inventoryID via barcode on item to a variable called $barcode and goes to Inventory table and searches for the record

           - It captures the EQUIPMENT ID of the inventory in a variable called $eqID ( in this example EQP-100)

           - It goes back to OrderLinesLayout and searches for my specific order and id_equipment field via $eqID

           - When it finds the record, I then set a field called barcodesOnOrder to itself & ¶ & $barcode

           - I then have a calculation field that will display the equipment name and substitute out the ¶ for “, “ and produces the following example:  In this picture I have (3) items checked out, all the same eqID. (1) OTHER Item also checked out… https://thedropshare.s3.amazonaws.com/Screen-Shot-2016-01-22-12-16-35.png


      This all worked well until I tried to create Print layouts. I keep running into problems trying to create RETURNS.


      Lets say a customer returns INV-01, but keeps the other items. I’d like to create a Print Page just showing that INV-01 was returned: https://thedropshare.s3.amazonaws.com/Screen-Shot-2016-01-22-12-09-27.png


      In an attempt to fix this problem, I did something very similar that just captured the returning items barcode to another field called BarcodeReturned and created another displayCalculation field on a RETURN layout. This did not work because if I have other items checked out, it would show their line items also and no barcode because BarcodeReturned field for their record was null. It looks like this… https://thedropshare.s3.amazonaws.com/Screen-Shot-2016-01-22-12-17-47.png


      My answer, I believe is to create a Transaction table to track this, but I’m not sure how to relate the other information.


      Would this sort of relationship work? https://thedropshare.s3.amazonaws.com/Screen-Shot-2016-01-22-12-25-48.png


      I'm grateful for any suggestions you have to offer.


      Thanks and sorry if my explanation is confusing..