Showing available records while excluding used ones
There is probably a very simple solution for this but unfortunately I haven't come across with that yet so I decided post my question here.
I have a database for different journal articles. As obvious most articles have multiple authors and most authors appear in multiple articles. Based on this I have a very basic table structure as follows.
Article -----< Article/Author join table >------ Authors
My layout shows the (article) authors in a portal.
Next to this portal I would like to have a portal that shows the available (already in database) authors but the portal should not show those authors which have in assigned to the article in question.
First I thought that this is an easy task but I have problems to come with a nice solution.
I have already come up with a solution that is based on showing ALL authors but if the user picks an already used author the system asks if this is what the user wants. Works but not elegant.
I have now used couple of evenings thinking of this but can't seem to be able to solve this.
I'd appreciate if someone more FM savvy could help me with this.