What you are talking about is a dwindling value list.
It removes items that where already selected from your value list.
You could look this up, there are a few examples available on line.
I'll try and see if I can make you an example.
Case is a function. There is a difference between a function and a script. Functions are used in calculations.
You could make a calculation field and put in a case function.
You could also make a calculation field with an If function.
But you could also use an If function in a script.
If you want to learn about script steps and functions I suggest you go to:
And you download the:
They have all the functions listed with explanation and examples.
In short the If function is what you use if there are about 2 choices.
If the salutation is "Male" use "Dear Sir"
Otherwise use "Dear Madam".
A Case function would be used where there are more choices.
Age is between 0 and 5 = Free Entry
Age is between 6 and 12 = 10 dollars
Age is between 13 and 21 = 15 dollars
Age is between 22 and 50 = 20 dollars ....
(Maybe not the best example but it shows the idea)
The theory is simple. In the table your layout is based on (in this case "Student") you create a calculation field that lists all the classes that have already been selected. (Those are the ClassID's from the "Enrollment" table)
Then you make a second table occurrence of your classes table. (ClassesNotSelected)
You make a relationship between the list of used classes (In the Students Table)
And the Id of the new Classes Table Occurence.
And you make that relationship not Equal
This will ensure that when you are on your Students Layout you will only see Classes in (ClassesNotSelected) that are not yet selected.
You change your "Classes" Value list to show the records, not from the classes table but from (ClassesNotSelected).
And you make sure it doesn't show all records, but "Includes only related records from" the table Students.
Almost there. The only thing left to deal with is a refresh problem.
Make a script called "Refresh" and add one script step "Refresh Window" and make sure you select "Clear Cached Join Results"
Now attach this script as a script trigger to the ClassedIDFK of the Enrollment table in your portal. Set it to the OnObjectEnter event.
That way when you enter the field to set a class first your portal will refresh and in your value list you will only see classes not yet selected.
nash lopez and Dasaint, take a look at this demo file: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
It implements a dwindlinding value list, but does not use Refresh window [flush cached Join results]. This script step with this option specified can produce long delays while waiting for your screen to refresh in some circumstances and thus is an option best avoided.
Thanks Dasaint and Phil for the quick replied, I try to work on this suggestion and good demo example..
Anyway dwindling value list is not really the issue because the situation is like this, In my Classes table/layout I uploaded different subjects, this subjects have their own Section, Description, Unit, Time, Day and Room assignment etc.. Example, ENGLISH Subject (Section=Eng 1, Description=Communication Skills Part 1, Unit=3, Time=9:00-10:00AM, Day=MWF, Room=108, and this is the same to the other Subjects.
Each subject offers different time, day, room so that student can select subject schedule from AM to PM base on their free time and wishes. In my Student Table/layout I created Portal that contain fields Enrollment::fkClassessID and this is my Value List, Classes::Section, Classes::Desscription, Classes::Unit, Classes::Time, Classes::Day, Classes::Room.
Now, if I have already ENG 1 subject in my Portal and if I select/click ENG 1 again in my VALUE LIST (fkClassesID), I want that their is a script or function that will warn or inform or not allow me to add that the same subject because it was there already in the portal. (see picture below) Can we make a script for this and set it to the script trigger of fkClassesID or Section field? If so please help.
Thanks again guys..
Isn't that exactly what happens in the dwindling value list? It lists all the values that are already used and leaves these values out of the value list. That way you can't select them.
However it's possible that I'm missing something.
You might be able to create a script trigger OnObjectModify of OnObjectValidate that sets the current ClassId and searches trough the list to see if it already has been entered for that student. But that's sounds kind of silly when you could just have a dwindling value list.
I already test the demo file that you sent me, and it's pretty good, I can use that in other way but for the system that Im trying to do is different.
"You might be able to create a script trigger OnObjectModify of OnObjectValidate that sets the current ClassId and searches trough the list to see if it already has been entered for that student" = It is possible that Filemaker pro 11 advanced using OnObjectModify or ObjectValidate script trigger and that was set to the VALUE LIST (fkClassID) can searches through the list of found set or ClassID or Section already entered in the POrtal and if their is, script dont allow the ClassId or Section enter again in the Portal? how Dasaint? please help.
Ok, I was thinking about this a little and there is a simpler solution.
In the Enrollment table (in my example) you can create a number field with an auto enter calculation that creates the Student Id and the Class Id.
That value can only be in there once, because every student can only be in a class once.
So then you can set that number field to Validate to have a Unique value. And you can have a popup message telling you that combination has already been made. I don't find it a super elegant solution, not as nice as the dwindling value list. But it could work.
The demo file I shared illustrates 3 different methods for catching duplicate entries in a join table. Feel free to look them all over to see if they provide any useful ideas.
Dasaint Im sorry for the late replied, I downloaded your CheckingValueList file and I was able to get an idea of not allowing student to select the same subject and that was ok for now, and exactly what I wanted to the system, creating number field with an auto enter calculation that creates the Student Id and the Class Id form Enrollment Table is good.
Now, I have 3 fields from Class Table in my portal: Time, Day and Room, these 3 fields is a drop down field VALUES from TIME, DAY, ROOM data from Classes Table that I uploaded (see picture ablove). My problem is every time I change the TIME/DAY or ROOM of particular student schedules the replaceable value reflect also or the same value to the other student schedules. Is there any way to change the value or change the time/day or room of every student using those dropdown fields (TIME,DAY,ROOM) without comprimising other student schedules selected?
The point is that each subject has a multiple time/Day/Room schedule so that every student has also the liberty to select their schedule/s based on their convenient time.
I configured these 3 fields (OPTION for This Field "TIME,DAY,ROOM") = Always Validate, Unique, Allow Overide, Message. I want that whatever changes I made from those 3 dropdown fields will be save to that particular students only not to duplicate to the other students.
Thanks for your guidance and hopefully you can help me solve on this problem.
PhilMoJunk thanks also.
It's correct that changing a Time / Day / Room would change the value with the other students as well. Because now, on a student's layout you show the fields from the classes table.
Change a value there and it get's changed in the Classes table.
What you need is to store all of these possibilities. So that when you choose a class, you can only select a Time during which this class is given.
I'll try and give an example from a video I once saw:
The idea is simple: Here you have car brands and models.
Every model is from a certain brand.
So when you select a brand (Mercedes) your second dropdown only shows the models from that brand.
That's what you need.
You select a class, and then your Time shows only the times that that class is given.
I don't know the details of your cless shedules, and how complex they are. But I think the easiest way to implement this is to add a few radio button fields to your Class table and to list all possible classes.
It would look like this in a table:
Now the thing is that you also have Day and Room.
I don't know the best way to go about making these kinds of Dynamic Value lists, but I'll try to see if I can't make a little test.
In the mean time, if you want to learn more about this I suggest you do a search here for "conditional Value Lists"
You'll find tutorials and demo files.
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
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
OK, I made a little test file, building further on my "ChackingValueList file.
I added some selections to the "Classes" table. The idea is that you give in every class. for every hour and every day.
Then if you go to the students layout, you can select a class and the second dropdown (Day) shows you all the days that that class is available.
If you select the day, yhe can then select the hours that are available.
I didn't test this trough and trough, but it looked like it was working pretty well.
Give it a try:
In the Classes table I used a combination of value lists getting manually entered values and getting values from a table, you just try and see what works best.