This is called a conditional value list. Here are my links on the subject: This looks like a lot, but it sounds like "option 1" of the first link, the forum tutorial is a good "fit" for what you want to do here.
There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.
The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
So based on Option 1, I have created a calculation field called "calc". I have specified the calculation as:
If ( ObjectID > 6000; calc;"")
I then set the value list to the calc field.
This isn't working. I get still ObjectIDs that are lower than 6000. I've also tried If (calc > 6000; ObjectID;"")
What am I missing? Is the formula just wrong?
Option 1: Simple but limited
Define a calculation field that only returns a value if other conditions are met and base your value list on this field instead of the original data field.
Example: If ( include = "yes"; valuefield ; "" )
This probably won't work for you as this field must be a stored indexed field and your "today's date" based requirement can't easily be included in a stored calculation.
Why would you use the name of of your calculation field inside its calculation?
You need something like:
If ( ObjectID > 6000; NameOrDescriptionFieldHere )
Then you set up a value list where you show the values from ObjectID as field 1 and the values from your calculation field as field 2, and either sort or "show only" the field 2 value.
Haha not sure what I was thinking there. This totally worked. I was even able to create a nested If statement that allowed me to create a list based on several criteria. Thanks for all your help!
Instead of a nested if, you might use the Case function. That can result in an expression that is much easier to work with in many cases.
I've run into a problem with this solution. The name field I am trying to use contains 8 repetitions (storage options) to allow for Alias names of the same object. When I use the calculation field, only the first Alias name comes up in the drop down menu. Is there anyway for the calculation field to include all of the repetitions? I tried changing the storage option for the calculation field but it didn't work.
Repeating fields are very rarely the best design choice. They are a holdover from when FileMaker was a flat file system instead of a relational database system. There are a few places where they are useful, but not many and the places where I'd use them over other approaches gets smaller with each new version released.
But a calculation defined as
If ( ObjectID > 6000; NameOrDescriptionFieldHere[ Get ( CalculationRepetitionNumber ) ] )
with 8 repetitions specified in storage options for this field would seem to be what you need here.
I don't think that is working for me. In the calculation field I have:
If ( ObjectStatus = Active; ObjectName[Get (CalculationRepetionNumber)])
It's still just giving me the 1st repetition from the ObjectName field. I have the calculation field set on 8 repetitions.
I'm using a legacy database that was created in Filemaker 5. If I wanted to correct use of repetitions should I just create a new "Alias" table that would include all of the Object names and link each to the corresponding ID number?"
The typical way to remove a repeating field is to first define a related table, then use import records to import both the data and the value of a primary key (mapped to the new table's foreign key field). When you kick of the import, you'll be asked if you want to split the data from the repeating field into individual records and you'd select that option in this case.
Then you replace your repeating field with a portal to the new related table on your layout. But there can also be other changes needed as part of this change depending on how you use this data.
I so seldom work with repeating fields and so have been testing things in a demo file. I just ran a new test and discovered that the calculation would need to be:
If ( extend ( ObjectID ) > 6000; NameOrDescriptionFieldHere[ Get ( CalculationRepetitionNumber ) ] )
(my original test didn't refer to a field in the Boolean expression part of this If function.)
Yes, the extend addition worked. And thanks for the suggestion on how to remove the repeating field I think I can handle that. Like you say it may take more work to make sure everything in the database works correctly but I think that is the eventual solution. As always thanks for all your help you are a life saver.