Filemaker Match/Find

[makingtrails]

Apologies if this is a simple question but i'm not having much luck finding a solution. It's late so it may just be my brain not working.

 

I have a table full of website categories called "Categories). Here are some two example records:

 

Parcent Category

 

Category ID (123)

Category Name (Tablets & Mobile)

Parent Category (BLANK as N/A)

Parent Category ID (BLANK as N/A)

 

Child Category

 

Category ID (456)

Category Name (Ipads)

Parent Category (Tablets & Mobile)

Parent Category ID (123)

 

Currently both the Parent Category & Parent Category ID fields are entered manually (Parent Category uses a drop down list). However I am trying to get the right calculation so when I select the Parent Category, the Parent Category ID is automatically populated. 

 

Something like ( If Parent Category = Category Name, Parent Category ID, "BLANK" )

 

Any ideas?

[comment]

Currently both the Parent Category & Parent Category ID fields are entered manually (Parent Category uses a drop down list). However I am trying to get the right calculation so when I select the Parent Category, the Parent Category ID is automatically populated.

 

This is not the best arrangement to have. There should be no Parent Category field in your table at all, only Parent Category ID. The parent category's name is available through the relationship which you presumably have.

 

To populate the Parent Category ID field, use a value list based on the Category ID field, also displaying the Category Name. You can set the value list to "Show values only from second field", if you so prefer.

[eos]

I had just written about the same answer as comment when he pre-empted me; so I just want to add that …

 

If you want to prevent making a category its own parent, you could define a conditional value list by adding a new table occurrence (TO) of Category, say, Category_others, and creating a self join where

 

Category::categoryID ≠ Category_others::categoryID

 

Then define the value list as suggested by comment, but select the fields from Category_others, and make the value list conditional by selecting the option “Starting from:” and the TO Category. This will give you a value list of all categories save the current one.

[comment]

If you want to prevent making a category its own parent, you could ...

 

This won't prevent making a category its own grand-parent, will it?

[makingtrails]

Thanks for the replies, much appreciated. 

 

I've removed the Parent Category field and replaced it with the Parent Category ID which now gives me the right data that I need to export/relate to the another fields. Superb

 

However in the previously in the Parent Category Field, the categories were listed in order of assignment for example:

 

Tablets & Mobile

Tablets & Mobile > Ipads

Tablets & Mobile > Ipads > 16GB

Tablets & Mobile > Ipads > 32GB

Tablets & Mobile > Ipads > 64GB

 

Now my list just lists

 

Tablets & Mobile

Ipads

16GB

32GB

64GB

 

Which unfortunately isn't good for when creating a new product. To be able to recreate these assignments I would need to relate the Parent Category ID to the Parent Category Name. So now i'm back to square one, attempting to match the Parent Category ID to the Parent Category Name. 

 

My apologies, I should of given this instance in my first post. 

[makingtrails]

ok, I've gone in a slightly different direction and have changed my database so I have four tiers of website category (Grand Parent, Parent, Child & Grand Child)

 

So now I have those fields in the same table (Categories)

 

Categories::Grand Parent Name

Categories::Parent Name

Categories::Child Name

Categories::Grand Child Name

 

I also have four other fields for the RecordID

 

Categories::Grand Parent ID

Categories::Parent ID

Categories::Child ID

Categories::Grand Child ID

 

Now I want to create a calculation which lookups/matches the Categories::Grand Child Name from Categories::Category Name and returns the RecordID of that record.

 

What's the best way of doing this?