0

So I have two tables. I'm going to be very specific and use very general terms to make sure there isn't any confusion, so thanks ahead of time for bearing with me since this description might seem a bit monotonous.

Table Name: Item List
Name_fk
Item Number

|

Table Name: Team Association
Name_pk
Team

I put a relationship between Name_fk and Name_pk.

Name_pk is a list of names and what team they are. Name_pk is filled with entirely unique values, so you know John Smith is on Team Blue there is only one record with "John Smith" in the Name_pk field and that record has "Team Blue" in the Team field.

Now Table A will have a lot of repeat items under Name_fk. There will be ten rows with John Doe in them, ten Jane Doe rows, ten Joe Doe rows, and ten Jess Doe rows. Joe and Jane are on Team Green according to Table B, and Jess is on Team Blue with John.

In the relationships section of the Database Manager, I have linked Name_pk and Name_fk.

I need to be able to search for Team Blue, and only have John and Jess's item numbers show up, or search Team Green and have only Jane and Joe's Item Numbers show up.

So far I have been unable to get this to work in FileMaker Pro 14 Advanced

Marcel Marino
  • 962
  • 3
  • 17
  • 34

1 Answers1

0

unable to get this to work

"unable to get this to work" is not a good description of a problem.

If you perform a find in a layout of Table A, searching for "Blue" in the Table B::Team field, you should find all items of people that belong to the Blue team.

use very general terms to make sure there isn't any confusion

Actually, this is a bad idea. It is very difficult to follow an abstract example like that. Please always use meaningful names for your tables and fields.

Note also that your structure is fundamentally flawed. First, you should not use names as your matchfields. Next, you should also have a table of Teams, where each team has a unique record. So your structure would be:

Teams -< People -< Items

and you would use TeamID and PersonID, respectively, as the matchfields. With this in place, you could locate Team Blue's items instantly by doing Go to Related Record[], or display them in a portal right in the record of Team Blue.

michael.hor257k
  • 113,275
  • 6
  • 33
  • 51
  • "If you perform a find in a layout of Table A, searching for "Blue" in the Table B::Team field, you should find all items of people that belong to the Blue team." And that's the 'unable to get this to work'. It isn't returning anything. – Marcel Marino Feb 02 '16 at 21:46
  • "Actually, this is a bad idea. It is very difficult to follow an abstract example like that. Please always use meaningful names for your tables and fields." I was referring to the over-explanation of the relationships between the two tables and their contents. I did use meaningful names for my fields; I will try to carry that over to the table names. – Marcel Marino Feb 02 '16 at 21:52
  • There, I have edited to the table names. Can you think of any reason there is nothing showing up in portals or why I can't seem to filter by the team? – Marcel Marino Feb 02 '16 at 21:55
  • 1
    If your portal (I mean a portal to Items, placed on layout of Names) is not showing anything, then you have no related records. Check your data, and your field types. – michael.hor257k Feb 02 '16 at 21:57
  • That's why this is so strange. I just tested this out with the bare minimum of data, and it works fine. For some reason it doesn't work with my real solution. I can't even edit the related field. On the test solution, I can edit the team field, but when I add the team field to the main solution I can't enter any data, even though it says it is enterable. I double and triple checked: the name field for that record matches the name field in the related table, and yet it won't display the data. This is perplexing. – Marcel Marino Feb 02 '16 at 22:18
  • Wrong TO, perhaps? I am afraid that without seeing your file, I can only guess - and your question is now off-topic for SO. – michael.hor257k Feb 02 '16 at 22:24
  • What do you mean "Wrong TO"? – Marcel Marino Feb 02 '16 at 22:24
  • Wrong occurrence of the right table. http://www.filemaker.com/help/14/fmp/en/html/relational.12.14.html#1028611 – michael.hor257k Feb 02 '16 at 22:26
  • I figured it out. My "Team" table had the "Name_pk" field labeled as a number field. I would have thought you couldn't put non-numeric data in there, but there it was. – Marcel Marino Feb 02 '16 at 22:36
  • Told you so: http://stackoverflow.com/questions/35164021/filemaker-search-with-related-records-not-working/35164291?noredirect=1#comment58047605_35164291 – michael.hor257k Feb 02 '16 at 22:42
  • Thank you Michael. You did in fact tell me so, and I voted that comment up. I greatly appreciate your input. – Marcel Marino Feb 03 '16 at 14:23