Problem with number of cards - would be soooo easy with SQL, but with FM?
I'm trying to figure out if I can use FM for bigger projects. As a test project to teach myself FM, I created a test application for a card game I own.
The game consists of multiple cards. The game has a core edition that you must own and you can buy extensions. Every extension has multiple cards. Sometimes a certain card is included multiple times in an expansion.
When you own the core game and some extensions, you can build one or more possible decks that you want to try out, which contain cards from the basic game and the expansions. You can also own an expansion multiple times.
So now I have:
Table: Cards: records card details
Table: CardsPerExpansion: records which cards are in an expansion and how many times
Table: Expansions: records the possible expansions
Table: ExpansionsPerDeck: records how many expansions one owns to build the deck
Table: Decks: records the name of the deck/game variant to try out
Table: SelectedCardsPerDeck: records the cards the user has picked to be part of the deck
The idea is that the end-user creates a deck, records which expansions he owns and then selects cards from the cards that are available to him and records how many he wants to add to the deck.
I have it all working, including showing the cards that are available to him based on the expansions he choose for the deck and the cards that belong to each expansion.
But now I have to check how many occurrences of a single card he may add to the deck. If he has
1x expansion A, containing card xyz 2x
2x expansion B, containing card xyz 1x
I have card xyz 4 times. So he may not add card xyz more than 4 times.
This is sooooo easy in SQL, but how the heck am I doing this in FM.
I think I can probably create another table - PossibleCardsPerDeck - that for every deck contains an entry per card available through the expansions and then add a field that I fill with scripting.
But I was hoping I could avoid that.
Problem is of course, that the number of available cards I am after is really a multiplication of the number of expansions in ExpansionsPerDeck and the number of cards that expansion has in CardsPerExpansion.
I already have a field with a list with cards on my Decks table.
I also built a CustomFunction that provides the number of available cards for a given card / deck.
But the problem is that I cannot use this function, because I cannot have unbound fields in my layout. And I think there is no table I can add this to.
So I guess the only solution with FM is to create an intersection table linking Cards and Decks and fill it with scripting when creating a Card or a Deck and then use the CustomFunction I built for a calculation field on that table.
Or does somebody very knowledgeable with FM see another solution?