10 Replies Latest reply on Nov 26, 2010 9:39 AM by raybaudi

    Conditional value lists

    WinstonChurchill

      Title

      Conditional value lists

      Post

      I wish to use a conditional value list using values from two fields in two separate but related tables. My problem is that I can't create another field with an indexed calculation because at least one of the referenced fields will be related. Can't figure out how to work around this.

        • 1. Re: Conditional value lists
          raybaudi

          " My problem is that I can't create another field with an indexed calculation..."

          but you can use a trigger that fires a script wich SETs a text field.

          • 2. Re: Conditional value lists
            WinstonChurchill

            Sorry, could you please expand on that.

            • 3. Re: Conditional value lists
              WinstonChurchill

              OK, I've spent a little time trying to create a script to use that will do the same thing without success. I appreciate the advice is likely just what I'm looking for but I don't see at the moment how a script could be used to give the same result as a value list and wondering if my goal isn't explained well enough. To recap:

              I want a drop down menu in a field in my (horizontal_envelopes) table, I want that list to combine values from two other fields which are in two other tables (floors::names and roofs::names), all 3 tables are related.

              My initial naive approach was to create another field with a calculation (list (floors::names ; roofs::names)), but of course the value list won't work because if I create that field in floors, roofs is related, if I create the field in roofs, floors is related and if I create the field in (horizontal_envelopes), both floors and roofs are related, if I create the field in an unrelated table then the calculation doesn't work because the fields are unrelated.

              • 4. Re: Conditional value lists
                raybaudi

                Hi

                the value list could come from values of a new field, setted by a script, that is simply:

                Set Field [ YourNewField ; ValueListItems ( Get ( FileName ) ; "FirstListName" )  & ¶ & ValueListItems ( Get ( FileName ) ; "SecondListName" ) ]

                • 5. Re: Conditional value lists
                  WinstonChurchill

                  Thanks I'll try that in a moment, I've just been told that dinner is ready. I don't think my title helped me all that much, it's not a conditional list I'm after at all, is it.

                  • 6. Re: Conditional value lists
                    raybaudi

                    No, it isn't a conditional value list.

                    • 7. Re: Conditional value lists
                      WinstonChurchill

                      Still struggling here I'm afraid. I try to keep things simple at first and build on what I have working once I see it working, so for now I'm just working on getting values from one field into my new field but am not getting very far with this one.

                      I've created my new (text) field and created a script, which for now have added as a button set up to the new field itself after adding it to my layout.

                      My script is: Set Field [envelope_records::horizontal_constructions; ValueListItems (Get ( FileName ) ; floor_records::name)]

                      I have 4 records in the table 'floor_records', but when I hit the new field/button, nothing is added.

                      I wasn't entirely sure about the (FileName) part, the FM11 functions reference document seems to indicate it should be the actual name of my database, I've tried it both ways and with and without quotations and with the field name with and without quotations.

                      • 8. Re: Conditional value lists
                        raybaudi

                        ValueListItems (Get ( FileName ) ; floor_records::name)

                        The red part os wrong !

                        There you must write, between quotes, the name of the value list, not the name of a field.

                        • 9. Re: Conditional value lists
                          WinstonChurchill

                          OK I have the new field working, but the solution as a whole isn't working.

                          To recap to make sure I'm not doing anything wrong:

                          I've created value lists for floor and roof names and used those lists in my script. My script sets the combined lists into my new field (horizontal_constructions) and I've used this new field for the new value list that I want as a drop down menu in another (subject) field.

                          The first issue is that if I apply the script to the subject field, the drop down menu seems to appear before the script runs. In my approach problem solving and simplifying steps, I have created a button to run the script which I can hit before going into the subject field to see if this solves my problem, it partially does but not entirely.

                          The new drop down menu, doesn't seem to update properly. If I hit the button then enter the subject field, that's fine. If I then go and change something in say the floor records and add a new floor or change an existing floors name and then go back and hit the button in my original layout and enter the subject field the drop down menu doesn't reflect the changes;-  unless I then completely deselect the portal which the field is in, go into the field again, deselect the portal again, go into the same field but in another record in the portal, deselect the portal again and then go back to the subject field.

                          • 10. Re: Conditional value lists
                            raybaudi

                            Whichever value list ( not ONLY this special one ) that gets its values from a field, updates itself AFTER the record that contains the field is committed.

                            So it's better to use the trigger on both the fields that originates the combo-values.