6 Replies Latest reply on Nov 29, 2013 6:30 AM by philmodjunk

    Virtual lists and setting fields



      Virtual lists and setting fields


           So I set up a series of virtual lists for pop up selection. It works great and things are happy basically. I am having trouble being able to store the virtual list selections into text fields so I can use them for a text calculation. I have tried a few things and the best I get is that the field is not indexed or the table is unrelated.


           I related the tables with an indexed value. The virtual lists have three repetitions and only one of them should have the value set in the field. This is set by an independent virtual list. I tried a script to chug through the bits I do have, the nth record number of the record in the virtual list and the nth repetition I need. Not having much luck with that either.

           I may need to create this with a bunch of tables and relationships X3 for the repetitions. Not looking forward to that idea.


           Any help would be great.

        • 1. Re: Virtual lists and setting fields

               You'll need to describe what you have set up in more detail. Are you describing setting up repeating text fields (with 3 repetitions) with pop up menus?

               Please explain what you mean by: "only one of them should have the value set in the field."  One of what?

               While repeating fields are often not the best choice, a field formatted with a pop up menu will store the value you select in the pop up menu. With some value lists, however, the value entered may not be the value displayed if your value list is set up to only show values from the second field.


          • 2. Re: Virtual lists and setting fields


                 For simplicity I will break it down to just the main virtual list and one other virtual list.

                 For example there is a table named Volumes and a field "volume" that has 3 repetition that contain values  pint, quart, and gallon. This exists as a virtual value list for the pop up called Volumes. This table contains a field selected_volume that is assigned the value  from the selected repetition. This value is not indexed.

                 There is another virtual value list table that is named Product with a field named "product_type" that has three repetitions containing the product name and the product reference number of each size . There is a field named selected_product that is assigned the actual selected value. This value is not indexed either.

                 The available choices in the second virtual value list depend on the selection made in the first pop up for volume selection.

                 The end selection would result in something like "Milk qt No.57" and this needs to be used in a text field that will get used for another purpose.

                 Everything is working up to the point that I want to use the non indexed "selected_product" field outside of the virtual value list displayed on the record.

                 I have the repetition value selected in the first selection available and the record number of the of the selected record product_type from the Product table.

                 In the example of Qt of Milk, Qt is the 2nd repetition and Milk is the 8th record in the Product table. I tried a script that would get the value of the second repetition from the 8th record in the Product table. That value seems like I should be able set a field with it.This returned a "?" so I either did something wrong in the script or I am way off in the general direction.


                 I know there is a better way to get at this simplified problem, but if you can keep in mind I would like this setup this way for a reason.  There are more than just the one value list and they all have three repetitions driven by the first selection.


                 Basically once something is selected in a virtual value list,how can I make that usable as text?

            • 3. Re: Virtual lists and setting fields

                   What threw me was your use of the terms "virtual" and "repetitions". As far as I can tell, your value lists are just standard table based value lists. And I would guess that your "repetitions" are simply different records in that table.

                   But the exact value list options that you are using are not completely clear to me. It seems obvious that you have selected the 'use values from a field' option but such might enter an ID number from the table of values with a second field showing the name or it could be set up to just enter the name. which are you using? I'd guess that you have the value list entering some type of id number and thus do not have direct access to the name value in the second field.

                   If so, you need a relationship between your layout's table and the table of values with your value list formatted field as one of the match fields. Then you can use the relationship to access data in other fields from this table. FileMaker 12 users can use ExecuteSQL with an SQL query that establishes such a relationship. Otherwise, you'd set this up in Manage | Database | Relationships.

              • 4. Re: Virtual lists and setting fields


                     The repetitions are repeating fields in the value list tables. The Virtual part is that there is no direct relationship between the value list tables and the main table. The relationship is handled with ExecuteSQL currently.

                     In the example I gave the Volume table has records that contain a repeating text field. volume[1] = "pint", volume[2] = "quart", volume[3] = "gallon".  The Product table has a repeating field, product_type[1] = "Milk pint No.56", product_type[2] = "Milk quart No.57", product_type[3] = Milk gallon No.58".

                     I am thinking I need to do something different than I did with the result field being a calculation instead of a text feild. I was using getnthrecord in a script but I may need to be using GetRepetition in a calculation instead. I am new to filemaker and I just do not know what some of these functions do exactly or how to use them. The FM function resource is ok but it does not cover all the options and how things can work.

                     If that does not work out I may have to get you guys a sample file of what I have going. Explaining it is a bit too complicated to do especially since I am not knowing what you seasoned people call things by convention.

                     The field I want to get the value out of and use as text looks like this in the database manager:


                     selected_product       Calculation       Unsorted, = GetRepetition ( product_type, Primary::selected_size )


                • 5. Re: Virtual lists and setting fields

                       I spent a little time thinking about the situation. It seems the problem may be a bit simpler than I first thought and what you originally suggested Phil. Since I am displaying a value from a second field in the popup all I am getting in the actual field is the record number and not the repetition value I want.

                       I did fix this with a caluclated field using a case function with ExecuteSQL for the result of each case. ExecuteSQL does so much more than I thought what a cool function.


                       Phill thank you for taking your time to think about this. You motivated me to keep working on the problem and I learned something.

                  • 6. Re: Virtual lists and setting fields

                         I would also suggest that you not use repeating fields. That seems a needless complication here where your tables of values can be used to produce the same lists of values with one value to each record and such is much more flexible than using a repeating field.