Validating two fields for uniqueness at the same time

[gfisher]

Developing registration/inventory system for college media equipment.

Includes the following;

Database of students each with unique ID number.

Database of equipment each with unique number.

Database of checkouts each with unique number that includes a portal to equipment reserved database. The portal groups together equipment by checkout.

Database of equipment reserved where each record primarily contains the equipment number, reservation date and student user. These records are created in the portal in the checkout database.

Problem

How can automatically check to see if a certain piece of equipment is already reserved for a date? I need to check that the equipment number field AND the reservation date field for a record in the reservations database is unique. If it is not unique (meaning someone already wants the same piece of equipment on the same date) a message appears.

Partial Solutions So Far

Script- Created a concatenated field of equipment number and reservation date in the equipment reservations database. I can find on this field and if results = 1 put up a message that equipment is already reserved. The problem is that this find needs to be performed manually and student workers will forget to do this and we end up with double bookings.

Validation Unique on either field- This works on just one field and is to limited because once a piece of equipment is checked out once, all other access is denied.

Thanks for any pointers on solving this. I did not think it would be this hard. I hope I am just missing something simple and don't need a major redesign.

Ideally when an equipment number is entered there would be immediate feedback if the equipment is not available for the date.

[LiveOak]

You are almost there. Create a calculation field combining equipment number and reservation data and use it in a self relationship (call it "Self"). You can then show the related records in a portal (all the records for this date) or create a calculation field

Display Text (calculation, text) =

If (Count(Self::Anyfield > 1, "Already Booked!!")

-bd

[gfisher]

This solution is really close. The calculation will work but not as transparently as I would like. I think it is because of how I have set up the data.

Is there a way to lookup data from a field in one database by all the records created in another database through the use of a portal in the first database?

For instance, if I auto enter the creation date into the Checkout Date field for a new record in the equipment reserved database and use the Checkout Date field in the calculation I get the result I am looking for.

If instead I try to have the Checkout Date field in equipment reserved database lookup the Checkout Date in the Checkout database, nothing appears in the field so the calculation doesn't work.

If instead I try to have the calculation go to the checkout database and access the Checkout Date field in that database it will not work because it is going to a related field.

Is there a way to get all of this to work?

Thanks again for the pointers so far.

[gfisher]

It now works great!!!

The calculation pointed out problems with the data structure. (The databases were created by someone else.) I organized the data logically and it just works.

Thanks again.