Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Help with TO relationships

Question asked by ahamel on Oct 7, 2018
Latest reply on Oct 9, 2018 by philmodjunk

Like • Show 0 Likes0
Comment • 9

I've been away from FileMaker for a few months and am coming back to a solution that needs some updating. I need to clean up my relationships to keep all my base table occurrences not connecting to each other, but use TOs to make the connections. So, here is a simple one: I have 3 tables, one with categories, one with roles, and one with classes. The idea is for someone to choose a category, then a role that fits that category, and then get a result which is a list of classes that role needs to take. I would like to do this all in one layout. I have 14 categories, but a couple hundred roles, and over 350 classes. The categories can be a drop down, but once someone chooses the category, they only have to pick from 50 or fewer roles. It might be better if they could see the whole list, or most of it with a slider to get to the bottom of the list. Then the classes (< 10) could appear in a particular order in their own portal (I'm guessing). I believe this problem can be solved by using TOs, but I am just not getting the right combination. I didn't know if setting up the parts of the layout would help or not.

One last question - I have done this once, but can't seem to do it again. I'd like to have a free text box that would search two separate fields. I have an original example, but I don't see how I got the free text box to show up so I could populate the global field for the find. Any ideas?

Thanks,

Ann

No one else has this question

Outcomes

9 Replies

philmodjunk Oct 7, 2018 6:20 PM
Mark Correct
Correct Answer
Starting with the simple part first, there is no such thing as a “free text box” in Filemaker. The closest to that would be a text field with global storage specified. Such a field, unless used as a match field in a relationship can be defined in any table in your solution and you can still use it.

With your categories and roles, it is not fully clear what you need. It is clear that you are describing conditional value lists or a different selection method with similar function, but in what table are you selecting those values and what does one record in that table represent?
Like • Show 0 Likes0
Actions
ahamel @ philmodjunk on Oct 8, 2018 5:21 PM
Mark Correct
Correct Answer
I hope the image below is clear enough to understand. By free text, I mean the box shown in #2. I made this sample file to try to figure out the solution without all the other elements of my actual file. I was able to make the popup box in #2 here, but not in the actual file. Not sure what I am doing right or wrong.
I do wonder if I have this all wrong, but I desperately want to figure this out. I am filtering twice and so I don't know that this is the right way to go about it. Maybe I need to use check boxes? I also don't know if I even have the layout set up properly.
Besides all of the above I have no idea how to properly set up the relationships. I keep making changes to see if I can figure out what is correct, but not much changes. I have tried this:
and this:

Can you point me in the right direction? I appreciate any advice.
Thanks,
Ann
Like • Show 0 Likes0
Actions
philmodjunk Oct 8, 2018 5:49 PM
Mark Correct
Correct Answer
On which table occurrence (box on your relationship graph) is your layout based?

What is the big picture? What are you trying to accomplish by selecting category, role and class?
Like • Show 0 Likes0
Actions
David Weiner Oct 8, 2018 6:17 PM
Mark Correct
Correct Answer
Is that layout based on the FinderFile TO? If so, you may just need to make a value list consisting of Related Values from the Roles table for that dropdown filter (at #4). Given the following relationship setup:
You can set up the Roles value list to only display Roles that are related to the category of course you want:

First, when you select the Category, it will set up a relationship to the CourseCatalog (FinderFile>Categories>CourseCatalog), and it looks like there's a single Role in each course, is that correct? That way, once you've selected a Category, your dropdown value list for Role should be populated with only values that are related to the courses in that category.

If you set up another relationship from FinderFile to CourseCatalog (numbered 2 above) using two global fields (CategoryID and RoleID), then once you've selected the IDs using dropdowns, the portal should show you just the Courses for that Category and Role combination.

I've attached my sample file for you so you can see it working.
Attachments
- CourseCatalog.fmp12.zip73.6 KB
Like • Show 0 Likes0
Actions
ahamel @ philmodjunk on Oct 9, 2018 5:26 AM
Mark Correct
Correct Answer
This is just a lookup screen for now. A person should be able to look up the classes they need for a particular role within a certain category.
Like • Show 0 Likes0
Actions
ahamel @ David Weiner on Oct 9, 2018 5:54 AM
Mark Correct
Correct Answer
David,
I am trying out your relationships. The relationship between roles and classes is many to many. All kinds of roles might take a particular ClassA. Also, each role may have many classes, ClassA, ClassB, ClassC, etc. I have separatetable for classes and I use the course catalog table to connect the various roles with their classes. I added the finder file to see if that would help me create a solution, but I might just be able to use the course catalog as the connecting TO. I will try try using two global fields.
Thanks,
Ann
Like • Show 0 Likes0
Actions
philmodjunk Oct 9, 2018 7:10 AM
Mark Correct
Correct Answer
To repeat, on what TO do you want to base that layout?

This is just a lookup screen for now
OK, but what you need to do next after you do the "look up" on this screen could affect how best to set this up.

David (tried to mention you, but your name just isn't coming up), has suggested a basic relationship based conditional value list, a good place to start, but also not your only option.

Going back to your numbered screen shot, for number 2, you have a custom dialog opening. For this you need an input box linked to a field, or, if you are using FileMaker 17 a variable. But you also say, "a drop down won't work". This is not so. This could be a global field set up with a drop down and several different methods could use the data in that one global field to match to data in more than one field.

In a later reply, you stated:

The relationship between roles and classes is many to many.
At this time, I don't see any relationship between roles and classes in any of the data models you have posted. Instead, they appear to be two separate ways to filter your classes into groups.

That doesn't mean that what you set up was wrong, just that if you truly need a many to many relationship between classes and roles, you'll need a different set of relationships.
Like • Show 0 Likes0
Actions
ahamel @ philmodjunk on Oct 9, 2018 8:58 AM
Mark Correct
Correct Answer
To repeat, on what TO do you want to base that layout?
The CourseCatalog in my sample file is where I join the roles and the classes. I thought that I had to base it on what I want for results so I have been basing the TO on categories.

This is just a lookup screen for now
OK, but what you need to do next after you do the "look up" on this screen could affect how best to set this up.
I guess it seems like I could use the results of the search to actually register someone for classes, but that is way down the road. I would be happy if this is simply a look-up. This is also going to have work in a browser (I haven't put it on the server yet) - something I've only had marginal success with in the past. Right now I have the class registration and the course catalog in the same file to help with looking up classes while registering. There are two audiences here - the folks doing the class registration who use the course catalog to check which classes are needed before they assign the dates and time, and the other audience is the folks requesting classes so they have an idea of what the classes should be, how many, and how many hours total. Because I have two different kinds of users, should I separate the course catalog into its own file and then just have a link to it in the file where the class registrations live?
This is a look at what the registrars see:

then this:

David (tried to mention you, but your name just isn't coming up), has suggested a basic relationship based conditional value list, a good place to start, but also not your only option.
I have a look up field (#4) in the image. That works for one role at a time. It works, but not for my end users. They want to see all of the possible permutations of a physician, for example. Might be an anesthesiologist or a cardiologist. So a user would look up ambulatory and then physician and see all the possible physician roles. When one is selected, the user would see in the box at #5 the classes that particular kind of physician would need.

Going back to your numbered screen shot, for number 2, you have a custom dialog opening. For this you need an input box linked to a field, or, if you are using FileMaker 17 a variable. But you also say, "a drop down won't work". This is not so. This could be a global field set up with a drop down and several different methods could use the data in that one global field to match to data in more than one field.
I need the custom dialog box because someone might list a role as, for example, "physician", or they might search on "MD", or "Provider". I created a field of synonyms so that when someone does the search for "MD", they will also get all the "physician" roles as well. I am using FileMaker Pro 15.

In a later reply, you stated:

The relationship between roles and classes is many to many.
At this time, I don't see any relationship between roles and classes in any of the data models you have posted. Instead, they appear to be two separate ways to filter your classes into groups.

That doesn't mean that what you set up was wrong, just that if you truly need a many to many relationship between classes and roles, you'll need a different set of relationships.
I had thought I could create a sample file to ask my questions with, but these two images are from my actual file. I will try to get a copy and scrub out the data so you can see what I am trying to do. I am able to search the Course Catalog now:
but what they want is to see a list of roles, not a drop down like above. So, in this case, a list of all possible physician roles. Does this make more sense, now? It is easy for me to talk about the real file than the hypothetical one.
Thank you,
Ann
Like • Show 0 Likes0
Actions
philmodjunk Oct 9, 2018 10:39 AM
Mark Correct
Correct Answer
Because I have two different kinds of users, should I separate the course catalog into its own file and then just have a link to it in the file where the class registrations live?
That doesn't require separate files. What you appear to need for that are simply two different layouts, each customized to the needs of one of those two groups of users. There are multiple ways to control which layout comes up for a given user.

They want to see all of the possible permutations of a physician, for example. Might be an anesthesiologist or a cardiologist.
It's not your only option, but that does not mean that you can't use a value list to assist in that selection process. Could "Physician" be a "category" and "cardiologist" be a "role" in the Physician category? No reason that the interface can't be designed to support the use of a value list to select multiple values. There are, in fact, multiple options for how that might be done.

I need the custom dialog box because someone might list a role as, for example, "physician", or they might search on "MD", or "Provider".
Sorry, but you do not need a custom dialog for that. Not only could a user type such text into a global text field without using the custom dialog, they could type in multiple search terms for either an "and" or an "or" type search. (And: find all records with criterion 1 and criterion 2, Or: find all records with criterion 1 or criterion 2....) And as I stated previously, value lists need not be limited to selecting only one value list.
but what they want is to see a list of roles, not a drop down like above.
Well the drop down is a list of roles, but it's far from the only option. A found set in list view or a portal could also present exactly the same list. The point to using a conditional value list is that it can be one way to reduce the list of options presented to a user, such as selecting a category in one field and then the conditional value list in another field (not necessarily a drop down), can list all the roles that are members of that category.

Here are two teaching files that you can learn from:
Adventures in FileMaking #1--conditional value lists This has multiple working examples of different ways that a conditional value list might be set up.
Adventures in FileMaking #2-enhanced value selection This file shows other ways to extend the functionality of a value list as well as demonstrate alternative methods for producing a list of values for the user to select from.

Both files have working examples of each technique that you can take apart and learn from as well as detailed documentation explaining the design and function of the same.
Like • Show 0 Likes0
Actions

Help with TO relationships

Outcomes

Attachments

Related Content

Recommended Content