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..