1 2 Previous Next 15 Replies Latest reply on Oct 29, 2010 9:13 AM by philmodjunk

    Assigning more than one type to a field

    Sunny1

      Title

      Assigning more than one type to a field

      Post

      I am working in Filemaker Pro and have a database of schools and school districts.  There are Public, Private and Charter type schools within each District.  I have assigned a type to each school.  We do have several schools that are Public Charter schools.  I have portals on each school district that have tabs for Public, private and charter schools with detail listed on each tab in a portal.  I want to have this special type of school "Public School-C" for charter to be listed on the portal for public schools but I can't figure out how to get it there.  The portal draws records from "Public School" table and this school is listed in the table but it does not show up in my portal.  I have a field for cPublicKey = Public Schools which ties everything together but I need to be able to have it say cPublicKey = Public schools or Public Schools-C and I have tried to write it several ways but it doesn't work when I go back to the Public Schools portal, it shows no records.  Is there a way to do this or another way to show multilpe types in one portal?

      Help!

        • 1. Re: Assigning more than one type to a field
          ninja

          Howdy Sunny1,

          If the school is "public enough" to want it to show on only the Public Schools portal, and not the Charter portal, then the school is Public...not Public-C.  This will fix your portal viewing issue.

          You can then add another selection field/list/checkbox which can sub-define it as a charter school under the main category of "Public".

          Think through how you want to use the data...not just what labels you want to see.  From your description, you want to use this data like a normal public school...so call it that.  This won't stop you from adding subcategories like "Public-C", but it will get you the function you are looking for.

          • 2. Re: Assigning more than one type to a field
            philmodjunk

            Welcome back Ninja and good advice.

            Here's another option to consider:

            If you have FileMaker 11, you might want to investigate how to set up portal filters. These filtering expressions can add a lot of flexibility to what you see in your portal.

            You might, for example, set the portal to view all schools, but then use the filter expression to control which type of school is displayed. This may enable you to simplify your relationship graph as well as enable you to show related records for schools with more than one specific text entry in the type field.

            • 3. Re: Assigning more than one type to a field
              Sunny1

              But there are times when I need to only run lists/labels for public schools or charter schools so I am trying to keep them separate.  So anyway, I can try your method, because really it is half a dozen of one or half a dozen of another.

              I have another question maybe you can answer.  Within this database are elementary, intermediate, middle, high and other types of schools.  Some times, I have to print labels or reports for schools that only have Kindergarden through 8th grade, which we constitute as Elementary but we do have 4th through 8th that is elementary also and maybe we only want to print labels for schools with Kindergarden.  I have added a field for OMIT where I can go in the database and put an X in the OMIT field of the schools I do not want but this can be time consuming because I have to look up the schools who I do want.  Can you recommend some way to do this better?  I have tried using the Find record option but it screws things up when I try it.  If I have pulled up Public School Records, use find to omit types of schools and click find, it re-sorts everything and if I try to get it back to its original sort, it brings up Private and Charter schools which were not part of the find. 

              Help!

              • 4. Re: Assigning more than one type to a field
                philmodjunk

                You can put copies of the same portal on different tabs for different schools and then specify a different filtering expression on each tab if you want. I'm only thowing out a possible option for you. It may or may not be a good idea. It depends on what other reasons you may have for your current relationships that you've set up for each portal.

                It's hard to answer your question about finding records without knowing how you've entered the data for a school that includes kindergarten and how this differs in a record for a school that teaches 4 - 8.

                That said, sorting does not change the records that make up your found set. It only changes the order in which they are listed so I find this statement a bit puzzling:

                "if I try to get it back to its original sort, it brings up Private and Charter schools which were not part of the find. "

                Perhaps you are performing a script to sort your records that includes step that also performs a find or some other operation affecting the found set?

                • 5. Re: Assigning more than one type to a field
                  Sunny1

                  Well, I will look into Portal filters but can you suggest some way that I can easily pick out the schools I want without having to do what I am currently doing with an OMIT button?  Any ideas?

                  • 6. Re: Assigning more than one type to a field
                    philmodjunk

                    It's hard to answer your question about finding records without knowing how you've entered the data for a school that includes kindergarten and how this differs in a record for a school that teaches 4 - 8.

                    • 7. Re: Assigning more than one type to a field
                      ninja

                      Just a thought from the same "not enough info" position as Phil

                      If you tag each school with a repeating field listing what grades it has, you can then search or filter by that criteria.  For example, Calvary has the elementary tag, and lists grades of 4 5 6 7 8.  Pico Elementary has the elementary tag and lists 0.5 1 2 3 4 5 6 7 8 9.

                      If you find for what schools have grades from 1-6, you'll get them both, but if you look for what schools have 3rd grade, you'd only get Calvary.  You could now filter by "elementary" or by grade span.

                      Note: I used 0.5 for kindergarten so that a future search could be done by number span...the "k" would be an impediment for you.

                      Again, it's most important to take time to think of how you will use the Dbase moving forward...then parse your data and set your labelling accordingly.  you won't guess 100% right, but you'll be better off than if you hadn't guessed at all.

                      • 8. Re: Assigning more than one type to a field
                        philmodjunk

                        A field storing a return separated list of values can also be used with Ninja's suggestion in place of the repeating field. It has the additional advantage that you can format this field as a checkbox set in order to specify grade levels for a school. Two fields that store youngest and oldest grade levels might also be used and this might make data entry a tad simpler.

                        • 9. Re: Assigning more than one type to a field
                          ninja

                          Yup, that's what I meant, but Phil worded it much more clearly.

                          If there is any question of what digging you might do in the future, I would lean toward the return separated list rather than min and max grades.  I would do this simply to make a find for "all schools with 3rd or 4th grades" easier in the future.  That search would be possible either way, but would be more direct if there were a return separated list.

                          Just my two cents.

                          • 10. Re: Assigning more than one type to a field
                            Sunny1

                            Thank you both.

                            I was able to do the Repetive Field option and it seems to work well for this use.  I have found that just because we have a high school that you assume is 9-12, we do have one school that has K-9 and it is not classified as a High School so if I choose schools with 9, I will get them both.  Thanks again for all your help.  I had never used repetitive fields before and I like them.

                            • 11. Re: Assigning more than one type to a field
                              philmodjunk

                              Actually, we suggested a return separated list. That would be a single text field of where the values are separated by returns--something you'd see if you formatted a text field with a check box set of k - 12 values. Ironically, that may not be the best approach here because searching the field for first grade (1) would also find schools that teach grades 11 and 12 unless you used scripting to enter more specific criteria.

                              Searching field repetitions wouldn't have that specific issue. On the other hand, repeating fields have other limitations that often lead us to use a table of related records in order to avoid those limitations, but if it works for you in this situation, great!

                              • 12. Re: Assigning more than one type to a field
                                Sunny1

                                Maybe that is what I did use because I have one box that has 10 check boxes in it with numbers representing the grade (.50 representing K)  On the screen the Control used is a checkbox set.  Is that it?

                                One weird thing that I noticed though is after I make a particular selection of grades and pull up those records, if I look at these records in Table View, the boxes checked list as numbers in that field except for 7.  It will list .501234568  if I have checked grade 5 and a K-8 has a 5th grade.  But for some reason, the 7 does not print in the box.  I can do a Find on Grade 7 and it pulls the right ones, but it doesn't pull it up in a larger group.

                                • 13. Re: Assigning more than one type to a field
                                  philmodjunk

                                  Check your value list definition and see if value 7 is missing from the list. One way to check this is to select Manage | Value Lists.

                                  What you are doing works well for grade 7 as that value is unique. 7 isn't the first character of any of the other values that might be selected as a check box in this field. I you search for grade 1, however, I predict you'll find schools that teach 9 through 12 because of the 1 that starts the values for twelfth and eleventh grades.

                                  There's a scripted approach that uses a relationship and Go To Related record that avoids this issue.

                                  • 14. Re: Assigning more than one type to a field
                                    Sunny1

                                    Well, I guess I must not being doing this right then.  I do have 7 in the value list.  I eliminated the chance for 11 or 12 to appear by only listing grades to 10 because if a school has a 10 its a high school but if it has a 9 it could be that odd middle school.  What is it that I am doing differently from your original suggestion?  I must not have figured that one out.

                                    1 2 Previous Next