8 Replies Latest reply on Apr 7, 2014 1:15 PM by Donovan_1

# Problem with number of cards - would be soooo easy with SQL, but with FM?

### Title

Problem with number of cards - would be soooo easy with SQL, but with FM?

### Post

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?

• ###### 1. Re: Problem with number of cards - would be soooo easy with SQL, but with FM?

Here is the data model.

• ###### 2. Re: Problem with number of cards - would be soooo easy with SQL, but with FM?

I think the SQL would be:

```SELECT   c.CardID          AS      "CardID",
c.CardName        AS "CardName",
SUM
( kpe.NumberOfCards *
kpe.NumberOfExpansions
)                 AS "NumberOfCardsAvailable"
FROM     Cards             c,
CardsPerExpansion kpe,
Expansions        e,
ExpansionsPerDeck epd,
Decks             d
WHERE    kpe.CardID      = Cards.CardID
AND      e.ExpansionID   = kpe.ExpansionID
AND      epd.ExpansionID = e.ExpansionID
AND      d.DeckID        = epd.DeckID
GROUP BY c.CardID```

I attached a screenshot, because somehow "Formatted" is not working...

• ###### 3. Re: Problem with number of cards - would be soooo easy with SQL, but with FM?

Since you are Familiar with SQL, You could use ExecuteSQL to get a count of the number of times a given card exists.

But your relationship for SelectedCardsPerDeck does not look to be set up correctly.

It looks to me like you should have:

Decks::DeckID = SelectedCardsPerDeck::DeckID
Cards 2::CardID = SelectedCardsPerDeck::CardID

Without using SQL, one method that comes to mind is to add a new table occurrence of CardsPerExpansion linked to Expansions like this:

Expansions::ExpansionID = CardsPerExpansion|SelectedCard::ExpansionID AND
Expansions::gSelectedCardID = CardsPerExpansion|SelectedCard::CardID

gSelectedCardID would be a field with global storage specified. You'd select a card in this field from a value list or via a script and then, from the context of Decks or SelectedCardsPerDeck, you can refer to CardsPerExpansion|SelectedCard to get the total number of instances of that card that is available for your current Deck.

• ###### 4. Re: Problem with number of cards - would be soooo easy with SQL, but with FM?

Ah, I wasn't aware of the role of these global fields on tables. That is nifty.

Still, that would involve scripting and would not allow me to create a dynamic calculation.

For example, suppose I would create an intersection table: CardsPerDeck and I would somehow fill it physically with the correct rows, I still would not be able to add a calculation field to that table to calculate number of expansions and card occurrences would I? I noticed that inside a Sum() function, you can not have a calculation...

When I use the ExecuteSQL function, there is a field called ODBC data source. That put me off, because nothing was available there. Can I leave that empty and will the function then use the internal tables? That would be cool!

Is there a reference guide on how the ExecuteSQL function works in detail - including the SQL syntax that is expected?

• ###### 5. Re: Problem with number of cards - would be soooo easy with SQL, but with FM?

Still, that would involve scripting and would not allow me to create a dynamic calculation.

Given the use of script triggers to update the global field automatically, the end result can be indistinguishable to the user in many situations.

When I use the ExecuteSQL function, there is a field called ODBC data source

Then you are not using (or looking at documentation for) the ExecuteSQL() function, you are looking at the Execute SQL script step. Similar names but very different contexts!

First, look up the ExecuteSQL() function in FileMaker Help. Then check out this PDF on FileMaker SQL syntax: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf (this file can also be opened from FileMaker Help | Product Documentation | More Documentation...) This PDF file documents the possible SQL for both ExecuteSQL() and the SQL used to access and ODBC data source so only the SELECT query examples apply to the ExecuteSQL() function.

You may also find the free SQLExplorer file produced by SeedCode to be helpful.

PS. I've got two teen-aged boys that are pretty fanatical about several such card based game systems. If you ever want a couple of beta testers and your game happens to be one that they have, let me know.

• ###### 6. Re: Problem with number of cards - would be soooo easy with SQL, but with FM?

I found my misunderstanding: I was confusing the ExecuteSQL scripting step with the ExecuteSQL Function.

Obviously I can use the function in scripting as well.

Using SQL on calculation fields opens up sooooo many possibilities! I love it!

Btw, the application is for MageWars from Arcane Wonders. It allows the user to build their spell books.

• ###### 7. Re: Problem with number of cards - would be soooo easy with SQL, but with FM?

the application is for MageWars from Arcane Wonders. It allows the user to build their spell books.

As someone who did D & D in his college days, I get it, But since my boys don't use that system, I'll definitely not mention this to them! Last thing my two fanatics need is to get sucked into yet another system that has them collecting cards and building decks.

• ###### 8. Re: Problem with number of cards - would be soooo easy with SQL, but with FM?

Not to make this entirely off-topic, but if you are a parent and find your children spend way too much money on collecting cards and building decks: Have a look at MageWars from Arcane Wonders.

This game provides you with all the cards you need, so no collecting here!