7 Replies Latest reply on Aug 6, 2009 5:45 PM by kunaaldesai

    Dynamically filterign value list with calculation

    kunaaldesai

      Title

      Dynamically filterign value list with calculation

      Post

      Well guys I have been trying to figure this out for a while. Hop eto get some help from here. basiclly I am using 3 tables

       

      1. Student Info which has fields like Id and Points each student has.

       

      2.inventory which has different prizes and related points.

       

      3. Pick_ up which is basically used to give away prizes to students according to points they have earned. I mean if a student has 10 points then he/she can take away a prize which has a worth less than that.

       

      I have  relationships like this:

       

      1.Student-Info.ID= Pick_up.ID

      2.inventory.Prize_name = Pick_Up.prize.

       

      This way I am making sure that those students who come to pick up arein the database and I am using a drop down to show the list of the prizes available( Which is basically derived from inventory table).

       

      So everything works fine. But now I want to add more depth to the options by restricting the drop down to only those prizes which the student deserves. I mean that I want to make sure that  if a student has 10 points then the drop down should only show those prize which are less than worth 10 points.

       

      Now, I have been trying different  relationship combiations but no luck so far

       

      Anybdy any comments?

        • 1. Re: Dynamically filterign value list with calculation
          etripoli
            

          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.

          • 2. Re: Dynamically filterign value list with calculation
            kunaaldesai
              

            Entripoli,

                          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.

             

            Kunal

             

            Kunal

            • 3. Re: Dynamically filterign value list with calculation
              etripoli
                 The field that you apply the value list to needs to be based on the Prize field in the Pick Up table.
              • 4. Re: Dynamically filterign value list with calculation
                kunaaldesai
                  

                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)

                 

                Kunal

                • 5. Re: Dynamically filterign value list with calculation
                  etripoli
                    

                  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

                   

                   

                  • 6. Re: Dynamically filterign value list with calculation
                    kunaaldesai
                       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.
                    • 7. Re: Dynamically filterign value list with calculation
                      kunaaldesai
                        

                      Entri Poli,

                                         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.