Hi,

I am new to FileMaker, and have been playing around with it for a few hours now.

I'm looking to replicate the functionality shown in iTunes smart playlists, in which the user pulls down a behavior from a drop-down list, but can add more.

Basically I would like to have an inventory of car enthusiasts. For each person, I would like to let them add multiple cars in their collection. I want to include them by brand, which will be accessed by a drop-down list. But I want to user to be able to add multiple vehicles. So maybe Joe Average has only one vehicle, but Jay Leno has dozens.

Is there a way that I can make it so that I can have an indefinitely sized list of lists?

Yes, create a new table for PersonCars (or similar). It needs the ID of the person, and the Brand (at least). Create a relationship from the Person table (table occurrence on the graph) to a table occurrence of the PersonCar table (probably the only one on the graph).

PersonID=:PersonID

On the PersonCars side of the relationship (important to get the side correct):

[x] Allow creation of related records

[x] Delete related records (so if you delete a person, their car collection will go also)

Put a portal on the Person layout, using the above relationship. Put the Brand field in the portal. Use a drop-down list for brands; its Value List could be from a table of brands, could be from existing values in that field (so you'd have to type a new value once, but only once).

You could add an infinite number of cars to a person. But in practice you'd need to scroll the portal to add them, which would be awkward with an "infinite" number :-]

Show Vertical scroll bar in the portal (option)

A few hours is not much to learn FileMaker, unless you're very very smart. But this is not too difficult. If you do not know what an ID is, you should try and learn; it's "key" to using relationships successfully (pun).

Thank you, That helped me look into relationships... which has really helped figure this complex program out...

Now, the next question, what are id's and how to utilize them?

Are they incrementally assigned values based on a calculation?

The simple answer is that a FileMaker serial ID is an auto-enter option of a number or text field. If you double-click the field name of one of these field types, or click the Options button when the field is highlighted, you will see a dialog with panels. The 1st panel is the auto-enter options, of which there are several. The 3rd one down is [x] Serial number. Put the number you want to start with. You can also prefix it with text (though I seldom do).

It is possible to "serialize" existing records. Create the field, with the above auto-enter option. Put the field on a layout, enterable. Show All Records. Click into the field. From the Records menu choose Replace Field Contents (Cmd-=). A dialog with options similar to the above will show. Choose Replace with serial numbers, with Update serial number in Entry Options. Click "Replace" (not the default, notice). It will populate/redo your serial number field, and update the "next value."

You can use that serial ID in relationships. This frees you from depending on such things as names, etc., which are prone to changes, misspellings, etc.. But is also means you have to take some care when doing such things as cloning your file and importing records again, etc..

There are other options, such as using calculations instead, something which combines the timestamp with random numbers to create "unique" IDs. That is best used for distributed files and other special uses. But some people use them for more. Controversial, long discussion ensues :-]

[P.S. A FileMaker auto-enter serial ID should be the only "primary" ID of its table. If it is used as a "foreign key" in another table, used as the target of a relationship, it should NOT be auto-enter. Common beginners' mistake. Chaos ensues.]

Edited September 10, 200817 yr by Guest

I must be in the midst of chaos...

I have done this successfully...

But when I add a new Brand under the portal, it does create a new entry for Brand, which means I can make a list of Brands each person owns... but the ID number doesn't iterate... it simply stays the same value. If I change it, the whole list of brands deletes from the portal.

Well, it's a little tricky the first time. It's difficult to describe in words. If you could post a small example file we could likely see what's wrong.

Ok, here it is.

I also discovered that every time I add a new brand to the 'Owner' category in the portal, it creates an entirely new 'Brand', even when I choose an existing brand.

If I create a new Owner, and add the currently existing brand, 'Honda', it creates a second record of 'Honda' in my Brand table.

And the id of the Brands only iterates as I add new Owners, not new car brands, or new records of Brands.

Thank you for taking the time to look at this. :

Cars.fp7.zip

Edited September 11, 200817 yr by Guest

Yikes. A table should only have 1 serial number field (with rare exceptions). Allow creation (and/or deletion) is generally only from the parent to the child; the child in this case being a "join" table.

There's a few ways to do the drop-down list. I put the "name" field (from the other parent, Brands in this case) on top of the Brand_id field of the JOIN table. I used a little graphic to cover it.

The idea is that you are only entering data into a field of the join table; in this case the local id field of the other parent. The id field of the parent you are on (owner in this case) is automatically entered by the [x] Allow creation in the relationship from the parent.

Cars_fej.fp7.zip

Thank you... that helps a lot, and has me understanding how Filemaker works much better.

That being said, I'm sure I'll have plenty of more questions just around the bend...

:

Now I'm completely stumped ...

I seem to have emulated, on my own, the same steps as you have outlined. But, for some reason, I can't seem to enter data in the portal. It is locked from me using the drop down list, even with a number of Brand records created.

Here is the file:

Cars.fp7.zip

Getting there. You only made 1 mistake. Which is that the field you are entering data into in the BrandOwners portal should be a BrandOwners' field. In this case BrandOwners::Brand_id.

You still had the Brand::Brand_Id in the portal, which is a Brand table's field.

Cars_fej2.fp7.zip

Thanks... so far I've been able to expand upon this database and make it slightly more complex thanks to your help.

I'll probably get stuck soon enough, but I feel like I'm finally getting my feet wet.

Much appreciated. :

now I'm guessing I have to dig into scripting...

How would I create a drop down list that displays values only when certain criteria is met?

For instance: If I have Brands, and Car models, and users can choose what kind of add-ons are allowed depending on their Membership Type. If they have a Pro membership, all add-ons are selectable, if they have a Basic account, only some add-ons are available.

Not scripting, more like a relational filter. You can do a search here for "conditional" value lists. Likely several hits.

You're going to need a table for what Membership Types go with what "add-on". It may need a record for each unique combo of the two. We don't really know what the combos are; some add-ons are "all", some are some memberships, but not others, etc.. Basically you need to do some research, then post an example file close to what you need.

Got it! Thanks!

Issue #1: Okay... I thought I had it... but now I realize I need something that is a bit more complex. I played around with conditional value lists, and got the Brands to show a list of Add Ons that were related to that Brand only.

But that was just a test...

What I really wanted was a List of Add Ons that the Owner can select, limited to what is available for each Brand.

The problem is, I can't seem to get this portal to work. I thought I should hook up the relationship between my AddOns, AddOnOwner, and Owner, but it wouldn't allow it, saying I need to duplicate one of my tables.

And I think this might be why it isn't working....

Issue #2: After this, I want to Add a Membership Level. Owners can select Add Ons available to them not only for their Brand, but also for their Level and under.

Now this membership is a little unusual. Memberships are selected per model. Basically an owner can put their 'car' not themselves, under a membership. Its a little strange, but we want people to 'level up' on a car-per-car basis.

So if John Topaz has a BMW, and he levels it up to Level 4, he gets all the Add Ons for Levels 1 to 4 that are available for BMW (or for All). He doesn't get any that are Level 5.

If John Topaz gets a Honda, starting at Level 1, he can now get Add Ons for Level 1 for Honda, and Levels 1-4 for BMW.

Here's my file, and I've included Add-Ons for BMW, plus some common Add-Ons that are for all cars.

Cars_b1.fp7.zip