Create another intance of the Inventory table (call it Inventory by Points), make the relationship between the Student Info table and Inventory by Points table:
Student Info:: Points ≥ Inventory by Points:: Points
And the value list would be based on Inventory by Points table, based on records related to the Student Info table.
Basically, I tried doing this thing yesterday and then whenever I select something from Pop- up menu then it gives me folwoing error.
Inventory:: name is defined to have unique value only . Allow this duplicate value?
Seems like when I choose anything from my Pick_up lay out then along with writting it to Pick _Up , it also tries to write the same value in Inventory table. I am wondering why would it want to write this thing to inventory.
One more thing when I am NOT creating any instance and NOT doing this filtering thing then selection works fine. I am not sure what is creating this problem.
The field that you apply the value list to needs to be based on the Prize field in the Pick Up table.
Pick up table is basically deriving this field from Inventory and Inventory is the one which keeps all the point info, I am defiing value list from Inventory table( Or you can say Inventory by point table)
Let me ask another way:
If you have a record for a student in Student Info, and the student comes to pick up a prize, where do you store that? In the Inventory, or Pick-Up table? If the pick-up table, you have two fields, Student ID, and Prize ID. The Prize ID field, in turn, has the value list assigned to it, created according to my first post. The value list will only show prizes from the Inventory table, if the current student's points are more than/equal to the points needed for those prizes. So, in list form:
Layout, based on Student Info
Relationship between Student Info and Pick-Up, by Student ID, with the Allow creation of records... turn on
Portal, based on Pick-Up
Relationship between Student Info and Inventory, based on Points
Value list, based on Inventory, with the first field being Prize, the "Include only related values starting from: Student Info"
Prize field, from Pick-Up, in the portal, with the above value list applied to it
I do store that in the pick Up table and it used to be reflected in pick up table only. Every thing was fine the value list was based on Inventory and used to show all the value from inventory. But since I have tried doing filtering and created the clone table it is not doing well.
I am really sorry , it turned out that while makeing the changes and setting up the Prize field in Pick_up layout , I had by mistake replace Pick_Up:: Prize_name to inventory:: prize_name and that was why data was going back to inventory table Now it works fine and I am really thankful to you.