Okay, so...it's possible that i can't even do what I'm trying to do or that this isn't the appropriate way to do it, but some help would be appreciated.

I play a game called HeroClix. One of the things you need to do in this game is assemble armies of little figures within certain constraints. So my goal is to create a database with two primary functions: cataloging my particular collection of figures and their relevant statistics, and then using that list to compose armies.

So my thinking was to create two databases, one for each primary function, and use relationships between the two of them to accomplish the goal. So far I haven't been able to get it to work.

Ideally I'd like a record in the army creation section of the database to have, say, a dozen pop-up lists. Each list would reflect the figures entered into your collection, and figures selected in one list would be removed from the other lists. (ie if i have Captain Marvel and Spiderman, and i select Spiderman in list A, list B contains only Captain Marvel.) Selecting a figure in any of these lists would then update a running tally of how many points you've spent, and how many you have remaining against a user-entered total.

Is this even possible?

If it is, how do I go about it?

I tried creating a relationship between the entry database and the army building database, but I have thus far been unable to find a way to get the information to show up in the form of a list (or, indeed, at all).

Any help is greatly appreciated. As far as my own experience, I've created a number of stand-alone databases but am essentially a virgin when it comes to relationships.

Thanks!

I don't know that you even need 2 files for this. Unless you need to alter the default characteristics for a character after you pick them for your army, then you could just use a simple checkbox to mark your selections in the main file, and quickly find them to see your army.

If, however, you customize each character after selection, or store a great deal of other info about them, then a separate "Army" file would be needed.

Normally you would want an auto-entered serial number ID for each record to use in relationships, but I imagine the names ("Spiderman," etc.) are unique and fixed, so maybe they're sufficient.

The main difficulty I see is removing your army selections from the value list of available characters. It is not too difficult to remove the current record's name from a list; but it is far more difficult to remove all your selections. To "hard code" it would not only be a pain, but would require extra code for every choice.

A far easier method, if you're the only using this, and only have 1 army, is to mark your choices in the Characters file, with a checkbox (the same as if you only had 1 file).

Reverse the Mark field with a Not Mark calculation (those not marked). Create a Constant1 calculation field, = 1, and a relationship from Constant1 to NotMark.

Filter the drop-down value list with that relationship, and it will remove your choices. Any deletions from the Army file would need to have the mark cleared in the Characters file.

If you only have 1 file, the drop-down is not need, 'cause you can just see and use the Army Mark field.

By the way, I don't see the reason for a dozen drop-down lists. It would be just one (if any). You create a new record for each Army choice, with the same drop down of characters available.

Character_Armies.zip