I have 3 entities, one of them contains many records, the other two are lists of those records. My aim is to create long lists and a short lists of those candidates. I can create long lists of course with n-n relationship. I add records to the long list with "add existing record" button. But, I have to create the short lists which select their records from the items from specified long lists. User will first eliminate most of the records and add them to the long list, then eliminate those ones from the long list and add them to the shortlist.
To fully specify it:
Record entity: x1,x2,x3..............xn
Long List Entity: LL1 (x1,x4,x7), LL2(x2,x10)
{sample}
Short List Entity: SL1 (x1,x7), SL2(x2)
{they have the records from the long lists, and when the records are entered, the only options are the records from the specified long lists.}
So, how can I make such a system work?
Create four entities LongList, ShortList, LongListMember, ShortListrMember
LongList Entity attributes
1) List Name
2) Any other information you want to store about the list.
ShortList Entity attributes
1) List Name
2) LongList - Lookup to related LongList record
3) Any other information you want to store about the list.
LongListMember Entity attributes
1) List Name - Lookup to LongList Entity
2) Member - Lookup to entity that you want to track in the list.
ShortListMember Entity attributes
1) List Name - Lookup to ShortList Entity
2) Member - Lookup to entity that you want to track in the list.
Now to create a Long List
1) Create an record of Entity LongList
2) For each of the records you want to track in the list, add a LongListMember record where
a) List Name field points to the new LongList record that you created
b) Member field points to the record you want to add to the list.
Similarly, to create a ShortList
1) Create an record of Entity ShortList
2) Set the Long List field to the related Long List record
3) For each of the records you want to track in the list, add a List Member record where
a) List Name field points to the new ShortList record that you created
b) Member field points to the record you want to add to the list.
To enforce the constraint that members of the Short list should belong to the related long list you will need to write a filtered lookup on the ShortListMember form such that the lookup view only displays records that belong to the related LongList.
This is a little complicated so let me know if you intend to go this way and I can help you further.