Relating tables/portal trouble

[apwalton]

Newbie here! Hello All!

Been using Filemaker for 3 days now so be easy on me....

I have 2 tables: photos and categories. I want photos to be a child of categories in that photos has a category field that I want to be a reference to the category table. I know that this can be done by creating a pop-up in the photos layout that gets its values from categories - but that is a poor solution if I ever wanted to edit the category list.

I attempted relating the tables and creating a portal in the photos layout and it almost worked - but one problem still remains. I removed the "category" field from the layout and replaced it with the portal which is a pop-up with values from the category table. The problem is that it won't let me choose a value unless I first go to another view that still has the original "category" numerical field and enter a value there. Then I go back to my view with the portal and the pop-up for that record is working and I can choose whatever value I want and everything works as expected.

Any ideas? Sorry if this is confusing - still a newbie with the terminology etc.

[apwalton]

OK maybe that is complicated. Lets say I have 2 tables:

Categories

-categoryID (primary key)

-categoryName

and

Photos

-photoID

-photoName

-photoCat (foreign key)

-photo

Where photos:photoCat is related to categories:categoryID (I think this is the correct relationship?). Do I have my ideas/terms correct? Maybe somebody could give me a quick list of steps to make the portal in the photos table so that I can pick the category from the list generated from the category table?

I've read about this, and tried it, and I thought I had it but its just not working. Any help would be greatly appreciated.

[QuinTech]

Hi apwalton, and welcome to FM Forums! A more appropriate relationship might be from photos::photoCat to categroies::categoryName, assuming that the contents of photoCat are "Baby Pictures," "Vacation Pictures," etc.

There is a hitch in this approach, however, which is that if you change the contents of categoryName from, say, "Vacatoin Pictures" to "Vacation Pictures" (note the change in spelling), you will break the relationship. You'd be better off adding a field called photoCatID and basing the relationship on that.

The most straightforward way of doing this, in my opinion, is through a looked-up field. You'll have two relationships; one between category names, the other between category IDs. The field photos::photoCatID is an auto-enter looked up value, which auto-enters CategoryNames::categoryID.

Now, when you enter a value into photos::photoCat that matches a value in categories::categoryName, the corresponding categories::categoryID will be automatically entered into photos::photoCatID.

You'll notice that now photos::photoCat contains static data which does not change when you edit categories::categoryName. Thus you should use this field only for entry purposes; display should be handled by placing CategoryIDs::categoryName onto the appropriate layout in photos.fp5.

Let me know if this helps or if i've glossed over anything.

Jerry

[apwalton]

Thanks Jerry. I think that solved the problem but I still have a couple bugs.

In my photos table I have added the CategoryID and related that to the categories:categoryID field. I have added a lookup to photos:category so that it uses the related value from category:categoryName. In the layout, I have added a pop-up for photos:categoryID that uses values from category:categoryID. So now I choose from a list of numbers photos:categoryID and when that choice is made the correct value for photos:categoryName shows up. The only problem is that choosing a number in the pop-up is guess work and I would rather choose from a list of names from the categories table. What is the best way to do this?

[QuinTech]

I think your best, quickest method will be to modify your configuration as follows:

In my photos table I have added the CategoryID and related that to the categories:categoryID field.

Cool.

I have added a lookup to photos:category so that it uses the related value from category:categoryName.

Get rid of this lookup. Instead, create a global text field called CatLookup.

Make two relationships:

CategoryByID

[apwalton]

Ack why is this so complicated? I could do this in MySQL and HTML by simply querying the categoryID and categoryName fields - and building dropdowns on the photos form with this data. I would think this would be easier to do in Filemaker, not much harder.

I'm afraid you've lost me.

Make two relationships:

CategoryByID photos::categoryID = categories::categoryID

CategoryByName photos::CatLookup = categories::categoryName

I did this in the relationships tab of the define database screen however you reference it as if it were a table

CategoryByID::categoryName

Lost me there.

Place CatLookup on your layout and attach the value list to it that displays category names. Place on TOP of this the field CategoryByID::categoryName, and do not allow entry into this field (it is for display only). Also make sure its background is opaque and not clear. It should be completely obstructing CatLookup, so that unless a user has tabbed or clicked into CatLookup, CatLookup cannot be seen.

Stack the fields visually? If one is obstructing another then why have the bottom one on the layout at all? How do I disable entry into a field?

Make photos::categoryID a looked-up field, looking up its value from CategoryByName::categoryName

Don't understand the reference to CategoryByName::categoryName

If I could just overcome this one issue I'd be in the data entry phase of this project. Very frustrating

[apwalton]

Also - since a portal is used to view and/or edit the contents of another table - a portal isn't even needed in this case? Lookups are used instead?

[QuinTech]

Sorry, i am sort of slipping into FM6 language. It's a bit different than 7.

When i write CategoryByID::Field, i mean Field as defined through the join from photos to categories, joined on CategoryByID. So CategoryByID is the name of the linked table in FM6, which is probably why you're confused by my response.

Thus, when i write "CategoryByID::categoryName", think

SELECT categoryName FROM categories LEFT JOIN photos.categoryID = categories.categoryID

(I hope i got my SQL syntax right--it's been a few months!)

The field that is being obstructed is there only for selection purposes. The user uses that to force FM to decide which categoryID to use. Then FM displays the category name using a link from the selected caetgory ID.

Also, by "CategoryByName::categoryName", i mean

SELECT categoryName FROM categories LEFT JOIN photos.CatLookup = categories.categoryName

Clear things up any?

J

[apwalton]

OK, to answer one of my own questions - it seems that this isn't a portal issue at all. No portal needed to accomplish this scheme.

I am attaching a file with 2 tables - photos and categories. If you would be so kind as to demonstrate what you have described, I would be eternally grateful.

photos2.fp7.zip

[QuinTech]

Sorry, can't. I only have FM6 available to me right now. You're right, though, that my solution leaves portals out of it entirely.

Perhaps one of our experts who works on v7 will step in...

J

[apwalton]

Got it solved, thanks.