I am creating a database for a non-profit, who is only allowed to distribute one of a certain item per month per recipient. I am not as up to date on validations as I should be, so I have no idea how to prevent more than one yes to be selected for any given record during a month. I currently have two tables:
The tables are related by a record ID field, which allows the distribution to be portaled to the Record layout. There are two types of items on the distribution table (item 1 and item 2), each are a separate field. Currently, there is a conditional formating on the date field of this table, so that if item 1 is marked yes, it will be highlighted. Is there anyway to create a validation, script, or calculation that would prevent an entry of yes to item 1, if another distribution record for that record ID has a yes already marked during the same month?
I apologize if this is a simple solution, but I can't seem to figure it out. Any suggestions you can provide would be greatly appreciated.
One way of doing this is to create a month table. Then relate one record from the Distirbution table to it. No validation needed.