Validation by Calculation: Unique Value

[Josh Ormond]

I need help building a calculation to validate a field.

The field "Status" has 3 possible entries (as a set of radio buttons)..."Closed", "Open", "Pending".

We are working/enter data on 1 issue of a magazine at a time...the status of that issue would be "Open". Any previous issues would be closed, any future issues "Pending".

At the close of an issue, before another issue can be changed to a status of "Open" in the status field, the previous issue would have to be changed to status "Closed".

Example: Only 1 Issue can have "Open" as the selected option in field "Status". To open issue 05, you would first have to change Issue 04 to "Closed" then changed Issue 05 to "Open".

Issue # (Date) - Field "Status"

Issue 01 (June 03-16) - "Closed"

Issue 02 (June 17-30) - "Closed"

Issue 03 (July 01-14) - "Closed"

Issue 04 (July 15-28) - "Open"

Issue 05 (July 29-Aug 11) - "Pending"

Issue 06 (Aug 12-Aug 25) - "Pending"

[comment]

I think I would do this in an entirely different way: close an issue by filling a DateClosed field; open an issue by setting a CurrentIssueID field in a Preferences table to the IssueID.

The status of an issue can then be calculated: if DateClosed has a value, the issue is closed; if IssueID equals Preferences::CurrentIssueID, the issue is open; otherwise the issue is pending.