AnsweredAssumed Answered

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

Question asked by AdamHorne on Jan 22, 2016
Latest reply on Jan 24, 2016 by AdamHorne

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:



Lets say I have (4) records in INVENTORY, (3) using the same EQUIPMENT ID:



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…


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:


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…


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?


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


Thanks and sorry if my explanation is confusing..