5 Replies Latest reply on Sep 23, 2013 8:56 AM by philmodjunk

    Really Simple question about making a pop up menu

    MichaelLawrence

      Title

      Really Simple question about making a pop up menu

      Post

           I have a simple question.


           I am creating an Invoice database with the products being previously defined. I have typed out the few hundred items into the Inventory table of the database. The customer is primarily using this item on their Ipad and I'm deciding (but leaning towards) a pop-up menu over a drop down box. My question is that I want to have the product they select to appear only once on the Invoice. The customer has usually 40-50 items per invoice and I don't want them to accidentally list a product twice on there. I know how to do this in MySQL and C+ but am a little lost on how to do this in FMP. Hopefully it is easy.

        • 1. Re: Really Simple question about making a pop up menu
          philmodjunk

               This requires a special conditional value list--called a diminishing value list.

               The trick is to use a relationship to your products table that only  matches to products records that don't have a record in your line items portal and then use this relationship in the conditional value list to limit what items are listed.

               Do you know how to set up the typical conditional value list?

               Have you every used a return separated field of values as a match field in a relationship?

               And this also can be done with ExecuteSQL in FileMaker 12 to simplify your relationship graph a bit.

               Here's the "exclusion list" calculation field I use with the  ≠ operator (instead of = ) in a relationship to make this work:

               List ( -1 ; List ( LineItems::_fkProductID ) ) // clear the do not evaluate if all referenced fields are empty check box.

               The -1 is a value that doesn't match to any product ID so I am sure to always have at least one value in this match field.

               That then is used in a relationship like this:

               Invoices::cExclusionList ≠ Products|notChosen::__PkProductID

               and my value list lists product IDs from Products|notChosen, include only related values starting from Invoices.

               And if memory serves, there may need to be a refresh window step performed by script trigger after selecting a product before the value list updates...

               For the ExecuteSQL version, see: Diminishing Value Lists and ExecuteSQL

          • 2. Re: Really Simple question about making a pop up menu
            MichaelLawrence

                 Thanks Phil. I like both options. I tried the exclusion list, but I'm keep getting 'index missing' when I got to my data entry screen.

            • 3. Re: Really Simple question about making a pop up menu
              philmodjunk

                   Sounds like the exclusion list is on the wrong side of your relationship. It's an unstored and thus unindexed calculation field. It has to be defined in the table on the "one" side of the relationship (Invoices) not the "many" side (Products).

              • 4. Re: Really Simple question about making a pop up menu
                MichaelLawrence

                     Just to make sure I'm doing this right. This is all being done on the Ipad layout 'Invoice Details'.

                     I created a field in the 'Invoices' table (I'm using the starter solution 'Invoices') called 'cExclusionList' as a calculation with the definition set to return a calculation result as 'Text'.

                     The calculation is 'List ( -1 ; List ( Invoice Data::Item). The do not evaluate if all referenced fields are empty check box.

                     I duplicated my products table, named it 'Products|NotChosen'.  I linked the relationship as "Invoices::cExclusionList ≠ Products|Not Chosen::Product Id".

                     I created a new value list, and the value is 'Field:Products|Not Chosen::Item' because I want to show the item names on my form. I changed the properties on the layout to show values from the new value list. I added a 'perform script step' to trigger 'OnObjectExit' to perform a refresh window script and it still allows me to select the item name more than once.

                • 5. Re: Really Simple question about making a pop up menu
                  philmodjunk
                       

                            The do not evaluate if all referenced fields are empty check box.

                       ???? Make sure that this check box is cleared.

                       

                            I duplicated my products table, named it 'Products|NotChosen'.

                       I hope that you actually did NOT duplicate the table, but that you duplicated the Tutorial: What are Table Occurrences? for Products.

                       We have some confusion over "item" and "Product ID". Your value list should specify Product ID as field 1 and Item as Field 2. Your exclusion list should then also refer to product ID and not Item. Note that the relationship matches to Product ID with the not equals operator, not item.

                       'List ( -1 ; List ( Invoice Data::Product ID)