8 Replies Latest reply on Mar 27, 2017 1:17 PM by tleitzke

    Remove items from Dynamic Value List by Field Value

    tleitzke

      I have an inventory, and not all the products are sold by all distributors. I have a dynamic value list that does a simple grab all items that are of the selected category. Not, I want to add a second condition to ignore any that are not sold by the selected distributor. Since not all our distributors use unique ID items for all their products (yeah, honestly not fun), I had made a second field in the products for just a checkbox's of all the distributors (created by a dynamic value list). I tried making a relationship connection of 'distributor(invoice) = availability(product)' to try and ignore any where they do not match- however, it just showed all of them. Then I tried using the 'not equal' symbol, but that obviously made nothing show(I tried all the symbols).

       

       

      My structure for invoices work in 3 tables of Invoice->Invoice Part->Product.
      Invoice is the main holster of the currently working on order.

      Invoice Part is all the items on the order.

      Product is just a general list of all products (which is copied over to invoice part when item is selected).

        • 1. Re: Remove items from Dynamic Value List by Field Value
          philmodjunk

          Is a given invoice limited to a single distributor and thus you specify the distributor in a field in the Invoice record?

           

          Or can a given invoice list products from multiple distributors and thus you select the distributor via a field in the Invoice Part record?

          • 2. Re: Remove items from Dynamic Value List by Field Value
            tleitzke

            There is a single distributor for each invoice.

            • 3. Re: Remove items from Dynamic Value List by Field Value
              philmodjunk

              There are two approaches that you can use to set up a basic relationship based conditional value list (there are many other options possible):

               

              1) Define a category field in Invoices. Set up a relationship to a new occurrence of products:

               

              Invoices::Category = Products|CVL::Category AND

              Invoices::Distributor = Products|CVL::Distributor

               

              Your value list can list values from Products|CVL and if you select "Include only related values starting from Invoices", you'll get a list of products only from the specific category and distributor.

               

              Draw back: You have to click outside the Invoice Parts portal to select a different category before you can select a product from a different catetory.

               

              2) Set up a calculation field in Invoice Part that copies the value of your distributor field that you've defined in Invoices:  cDistributor:   Invoices::Distributor. Define your Category field in Invoice Part. Put this field in your portal row for selecting a category.

               

              Define this relationship:

               

              Invoice Part::Category = Products|CVL::Category AND

              Invoice Part::cDistributor = Products|CVL::Distributor

               

              Now make Invoice Part your "starting from" table occurrence

               

              For more on Conditional Value Lists and alternative methods for picking a value from a list see these teaching files:

               

              Adventures in FileMaking #1--conditional value lists

              Adventures in FileMaking #2--enhanced value selection

               

              Each file has a number of different working examples along with detailed documentation on how they are set up and how they work.

              • 4. Re: Remove items from Dynamic Value List by Field Value
                tleitzke

                Each item uses a checkbox for whether or not the product can be ordered from X location, created by a dynamic value list. Will this worth with that method, even though multiple checks will be used? I feel like what I originally tried was method number 2.

                • 5. Re: Remove items from Dynamic Value List by Field Value
                  philmodjunk

                  It should still work as long as the check box fields in products are indexed.

                   

                  A check box formatted field stores the selected values as a list of values separated by returns. When a field with a list of such values is used as the match field in a relationship, you get an "OR" type relationship where records match in the relationship if any one of the listed values matches to a value in the corresponding match field on the other side of the relationship.

                   

                  Example:

                   

                  Say check box field is in a relationship like this:

                   

                  TO::CheckboxField = TO2::Field

                   

                  Say you've clicked two check boxes to get:

                  Apple

                  Orange

                   

                  Then any records with

                  Apple OR Orange in the TO2::Field will match.

                  • 6. Re: Remove items from Dynamic Value List by Field Value
                    tleitzke

                    So, still pretty much the same thing, but I have been changing my form for adding items to be added NOT through portal, but through the form itself (The users said it was too annoying on their iPads to add items through the portal). So now, I have a button for adding the product to the portal, so I can do a check by script on add- whether or not the product will show. I tried again on adding distributor == distributor, but it just created empty lists. So now, just to try and make things a little easier (I guess) I can check on script. However, when I use 'distributor = distributor' through script, it fails still (I am using a dialog box that pops up only on fail).

                     

                    edit:

                    The field does say 'indexed', and options say it is at 'minimal', 'automatically create indexes'.

                    • 7. Re: Remove items from Dynamic Value List by Field Value
                      philmodjunk

                      This is not the same thing at all from what I can tell. But there's just enough info to tell exactly what you are attempting.

                       

                      By context, you should use option 1.

                       

                      But distributor == distributor looks like find criteria rather than a relationship.

                      • 8. Re: Remove items from Dynamic Value List by Field Value
                        tleitzke

                        I have tried option 1, and it does not work. The value list comes up with mixed results. 1 supplied shows all, then the rest come up with empty lists; even though select items are selected for all (I just randomly picked 5 items, and check all the check boxes for every supplier)

                         

                        Capture1.PNG

                         

                        Please ignore the "genscoID/JohnStoneID; those are going away.

                         

                         

                        Edit:

                        It turns out, that on my form for modifying/adding items, the piece for "availability"/"seller" was not changed to the correct field- so it was setting the values to "Category" instead, providing these problems. Option 1 does work now, along with the scripting portion (which is now tossed, since it is not needed)