FileMaker Forum


Mandatory Values

I have started to understand auto enter and validation field options.

I want to ensure that a field isn't empty AND contains an approved value.

For instance:

  • The field should not be empty (“”, NULL, FALSE, 0, or Empty).
  • The field should contain either the value "Cat" or "Dog".

What are some of the best ways to accomplish this? Should I use value lists or a calculation to validate the field? What about using a script? What is the best sentinel?

The state field must contain a two letter state abbreviation.

Chris Johnston


Left on all list items?

I see that when you use Left(return separated list; 10) it gives you 10 of a count of every character in the list. So if you had 10 list items all 20 characters long in the list you just get back 10 characters of the 1st one only. How can you use Left with a list and make it shorten every Item in the list?

Chris Johnston


Compare Lists Boolean?

I have the result in a return separated list (it came from ExecuteSQL) something like this:




I also have another list










What is a way FileMaker can say if any of the values in List 1 exit List 2 evaluate as true? I was trying to use FilterValues ( ) Inside of PatternCount () but it went sour and I forgot the whole plan.

Chris Johnston


Wrap lines of list

If you have a return separated list how could you wrap each line like this?






To this






Chris Johnston


Breadcrumbs & Dropdowns

I am new to FileMaker and I am loving every moment of it. It is so powerful. I have took some training (online) and have been reading on best practices and other related topics. My solutions for my business are coming along, but I am stuck on some areas. I have constructed a database that will collect information for my business. This information will have Topic and Subtopics. It was suggested to me that I try a setup like this.

INFORMATION and INFORMATION_Topic are both Tabel Occurrences with the same data source table. TOPICLIST is a join table that allows the Many to Many relationship. This was a stretch for me to understand at first but once I played with it a bit it made very good sense. I fully understand now and have been able to get it all working well. I learned a lot and I am pleased.

The problem I am having now is that I need a way to offer a breadcrumb like system to know where you are at as it relates to information, and having a way to navigate the levels of Topic or Subtopic you are in. I need to be able to go up or down a level, and make dropdown list of all available at current level.

I have learned about global fields and I am finding a lot of reading on how to use them with a Table. This Table that is described seems to be populated from existing content as a utility Table. I am lost in this area and don’t know where to start. How do you populate a table with existing data? I have used a global for filtering via a Table Occurrence, but it seems to be different with this type of setup which I am new to. Should a global work the same way here to create a way to populate the related children of particular parents in to something like a dropdown. I was able to get a lot out of ExecuteSQL, I have a good amount of experience with PHP and SQL related concepts. With ExecuteSQL I could see ways to deduce who is a child of a particular parent and so on. However I want to learn more of the FileMaker Table Occurrence or before ExecuteSQL ways, because that is not my strong point. I think learning it will give me a greater understanding of FileMaker.

So to be clear I need help understanding:

  • How to create a utility Table that can help with a breadcrumb system, dropdown lists, and listing over all.
  • How to populate this table with the correct existing data to make it useful.
  • How to use a global field and Table Occurrences with this type of Many to Many setup to help with the process.

I have included a Mimic file much less complex then my database but it show this setup exactly like mine and can created Topic or Subtopics for as many levels of information as you choose. I hope someone else like me who does not have the knowledge of this type of set up find their way to it. I had learned some of FileMaker’s concepts but this was a great one to learn.


Chris Johnston