Conditional Value Lists in a flat file db?

[mr_needlemouse]

Hey everyone. I hope this is the right place for my questions. I've recently upgraded from Bento as there's a lot more I wanted to do with my database. Now, knowing what I want to do and being able to do it are two completely different things! 

 

So, I have a small (c.1,000 entries) database for my video game collection. It's flat file and contains all my hardware and software. I use forms which only display the relevant information for each item (info for hardware is dramatically different for software) so while I know there will be some data redundancy I don't think it's too big a deal on such a small database.

 

To dip my toe in the water of FM I want to make it so that the Platform Holder field (Nintendo, Sony, Sega, etc.) is related to the Format field (Super Nintendo, Playstation, Saturn, etc.) so that if I select, say, 'Nintendo' in Platform Holder, I can't select any non-Nintendo platforms. Can I do this in a flat file database? I've done a bit of reading and it seems that a Conditional Value List is what I need, however, I can only find relational db examples. I've looked a bit at using 'if' statements and perhaps the 'case' function as well and I don't really know which one is most fitting.

 

To further muddy things, in the future I want to be able to enter games which appear on compilations as being attached to the correct compilation (i.e. one game compilation includes many games), and have a list of the games on a compilation appear in the form for the compilation. Is this even possible on a flat file? If it isn't I'll probably shift to a relational database now and do the 'Platform Holder/Format' problem in a fitting manner.

 

Sorry for how meandering this post is. I'm not massively FileMaker literate and it's difficult to know what information is relevant for getting help. I really appreciate that you've taken time to read this.

 

Thanks in advance.

[eos]

Sorry for how meandering this post is. I'm not massively FileMaker literate and it's difficult to know what information is relevant for getting help. I really appreciate that you've taken time to read this.

 

Why not start with the Help system in your FM application and take it from there? There is a web version, too: http://fmhelp.filemaker.com/fmphelp_12/en/index.html

 

Also, there is a number of books on FileMaker 12, all of which do AFAICT a decent enough job of giving an introduction into relational database theory in general, and the ins and outs of using FileMaker in particular.

 

On a general note: if you want to use a single table only, but still benefit from features such as conditional value lists, you'll have to set up a relation of some kind anyway (yes, that is possible even with only one table), and therefore familiarize yourself with relationships; why then not do it properly and enjoy the many advantages a relational database system gives you?

 

See in the attached file how easily and with just a few tables you can set up a structure that lets you manage games, platform holders and platforms, and gives you a CVL to boot. This is a just a barebones solution with no user interface to speak of, without any bells and nary a whistle, but it's already a relational database and can serve as the foundation for a full-fledged solution.

 

So don't let the lingo frighten you – this isn't rocket science, and you can take it step by step. Also, the topic matter seems to be a hobby of yours, which usually is a great motivation for getting things done …  

Games_MakeItRelational.fmp12.zip

[mr_needlemouse]

Hi, thanks for the in-depth reply and the download. That's going above and beyond the call of duty there! Believe it or not, I have a lot of database development experience, however, it was 15 years ago and it was before object orientated development (on old, old Foxpro if you remember that). So, I'm good with my theory on relations but less hot with the whole putting it into practice. I've decided to take your advice and go full relational as I often write freelance articles on video games, so combining that with the software database will be a great way to keep track of what I've written about.

 

Thanks again for all the help. :-)

[mr_needlemouse]

Okay, I've hit a bit of a brick wall here. I've managed to create and automatically populate an index field for my main software table. I've also created a second table (complete with index field) containing all the hardware information. I've managed to link Platform Holder in the software table to the Platform Holder field in the hardware table and it works great.

 

However, when I try to link the Format fields sorted via my selection in Platform Holder I simply cannot find any tutorial which adequately explains how to do this. As a consequence I get an error message in my Format field. I looked at your file for an example on what to do, and I notice you have three tables but in the relations 'map' view there's a fourth table going off to the left that I can't access. I presume this has something to do with it, but I don't know what's going on.

 

Any help will be greatly received as many of the tutorials online are horribly lacking in step-by-step instructions.

 

Edit: EUREKA! Managed to get it to work without having to make a third table. Right... now to see if I can add an 'and' operator to whittle the list down by platform holder and manufacturer... wish me luck.

[eos]

Those tables in the Relationship Graph (RG) are so called table occurrences (TOs), not tables; they are proxies to the “actual” tables you create and see listed in the Tables tab, and you use TOs to create any number of relationships between tables (even between the same tables, using different matches). That's why you see four TO's in the RG; one table is represented two times, because I needed another TO of Platform Holder to create the value list that is conditional on the foreign PlatformHolderID you select in the a record in the Games table.

 

In short, you need to grasp the concept of table occurrences as placeholders for tables if you want to work with relationships; I can't give any specific advice without seeing your file.

 

I recommend reading up on FM relationships on this page of the help system: http://fmhelp.filemaker.com/fmphelp_12/en/html/relational.11.14.html#1028611

[mr_needlemouse]

Hi Eos, I've attached my database for you to have a look at if you have the time. Obviously don't feel obliged to give it the once over, I know you're going above and beyond the call of duty here helping someone who's lapsed in database literacy so horribly. Would it be right to say that if I don't use TOs I can only have things sorted by the one relationship? If that's the case, then I'm not going to be able to do any of the more complicated things I'd like to. Thanks for the link, I'll give it the once over once real life slows down a bit!

Once more, thank you for all the advice, it really is most appreciated. If I'm ever in Bavaria, I will buy you a pint.

Edit: it would help if I provided the link! http://www.mediafire.com/download/rt0w99dhct97vzr/Software_%26_Hardware_Database.fmp12