I am assuming that genre is a field in the story table. If so the following worked for me with a quickie test file.
In the story table create a text field, DupCheck. Make it an auto-entered with the following calculation, episodeID &"-" & genre, uncheck the box do not replace value of field (if any) then go to the validation panel and check require: Unique Value. You will probably want to allow the user to override the validation, and in the Display Custom Message if validation fails, enter your error message.
The not so custom dialogs that are shown when the validation fails, ie, there are two identical genres for the same episode are not obvious and worse cannot be replaced with a custom dialog of your own design.
I put all this in a portal on the episode record so if you have only three or four stories per episode the genres would be pretty apparent at a glance, you could also set each genre to a different color which would increase the obviousness.
Thanks, I would never have thought of that. I put it in a test project and it works great - will impliment on monday.
I would still like a calculation that returns a boleen if there are duplicates in a field on related records so conflict could exist but be apparant to the user (we do a lot of shuffling).
also I can't really use the conditional formatting thing as I am using that to identify other issues and I don't want the end up with a rainbow effect.
You can do more than change colors with conditional formats. You can put Layout text on your layout with text such as "DUPLICATE VALUE" and you can use the conditional format to make the text appear/disappear. Not only can you change the color to match your layout background, you can change the font size to 100 or larger to make the text disappear.
For a boolean result in a calculation, you can set up a self join match on the fields whose combined values define the duplicate condition.
IsEmpty ( SelfJoinTO::NeverEmptyField )
Thanks phil, I do use the text disappear thing but need to get a calculation to trigger the conditional formatting
What calculation would I use, I don't understand the self join match on fields thing and need a little more detail to understand your plan
Say you want to know if Field A and Field B of two different records contain the same value.
Open Manage | Database | Relationships
Select your table by clicking it, then click the duplicate button. This creates a new table occurrence--a new reference to the same table.
Drag from Table::Field A to Table 2::Field A and then from Table::Field B to Table 2::Field B.
Then this calculation:
Count ( Table 2::Field A ) > 1
will return True if at least one other record has values in the same two fields. (I realize now that IsEmpty( ) won't work here as a record will match to itself.)
great, thanks works a treat. Still so much to learn about relationships