I have two related databases that allow for items to be checked in and out. One database holds the item inventory and the other is a user list. As items are checked out they are added to the users list of current items out and theusers name is added to the item inventory. They work great but when an item is Checked In it is deleted from the users account. I would like to create a history file that would list all of the items that the user has ever taken out. I cannot seem to do this without the information disappearing when items are checked in. I have tried various relationships and editing the script to set a field in a different database to a value when the item is checked out. I am not sure if I should be using a lookup. I have a portal to keep the history but that is as far as I can make it work. Any suggestions?

How about adding another field in the users file and concatenate the invertory item ID onto the end whenever the user borrows something. Like so:

Set Field (PastItems, PastItems & "

Not sure if you caught up with my previous posts on this item, but if not, here is the gist of the approach I suggested.

1. Create a global number field called 'gLogSerial' in your 'Users list' file.

2. Create a new file called "Loan_History.fp5" with four fields in it:

- a number field called "LoanNumber"

- a text field called "UserName"

- a text field called "ItemName"

- a date field called "LoanDate"

3. From within your 'Users List' file, create a relationship to the new file called 'Loan_History' which matches the 'gLogSerial' field with the 'LoanNumber' field.

4. Edit the script which places an item in the list of current items out for the user, to add the following steps:

Set Field ["gLogSerial","gLogSerial + 1"]

Set Field ["LoanHistory::UserName", "User_Name"]

Set Field ["LoanHistory::ItemName", "Item_Name"]

Set Field ["LoanHistory::LoanDate", "Status(CurrentDate"]

---

NB: You'll have to make sure that the second and third steps in this sequence correctly capture the user name and item being loaned from your database. Also, I'm assuming that your script for adding items to a user's list of items currently out is within the 'Users List' file. If it is within the 'Item Inventory' file, you'll need to place the relationship and the global field within that file so that they are accessible to the script.

Once the above steps are in place, every time an item is borrowed it will be logged in the loan history file.

If the solution is to operate in multi-user mode, a couple of extra steps are required. In this case it will be necessary to store the global value in a related file rather than in a global field in the main file. Then it can be updated by scripts run from the clients as well as the host in multi-user implementation. To implement this:

1. Create a calculating number constant field the main file called Global_[key] with a formula specified as "1".

2. Create a file called prefs.fp5 with a corresponding calculating field plus a number field called 'LogSerial'.

3. Create a relationship from the main file to Prefs.fp5 called 'Global" which links the 'Global_[key]' fields in both files.

Then simply use the field Global::LogSerial in place of the gLogSerial field referred to in my previous post.

If the database will not be accessed in multi-user mode, these additional steps won't be needed.