13 Replies Latest reply on Jan 14, 2014 7:32 PM by flybynight

    Dynamic Value List trouble

    carguybikeguy

      Hello Folks,

      I just came across a potentially critical mistake in how I use a dynamic value list to auto populate a price for something.

       

      The skinny: I have an invoice layout that lets a user define a category for a basic product. When the category is set, the product field is limited to just the products in that category. No issues there. That works beautifully. The issue is the price for the item is set when the category is chosen, not when the actual item is chosen and changing the item does not result in a subsequent change in price for that line item.

       

      Workflow inside of invoice is:

      Define the category for a new base item.

      Depending on that category (ex: Juices), choose from dynamically created list of items. (There might be four items in the Juices category; OJ 4oz, OJ 8oz, AJ 4oz, AJ 8oz)

      OJ 4oz is $1, OJ 8oz is $2

      AJ 4oz is $1.25 and AJ 8oz is $2.50 for four different prices.

      The price for the line item gets set (or should be anyway) at appropriate price for each item when the item is chosen.

       

      Rather than the desired outcome however, the category choice drives the price and then that's it. No change in price happens when a different item is selected from the dynamic items list.

        • 1. Re: Dynamic Value List trouble
          Oliver_Reid

          So there is a category gobal(?) and an Item ID population using a drop down that actally displays (condtionally) a field that is a combination of item name and price.

           

          Try

           

          Make sure the fields used to populate the drop down(s) are stored and indexed

           

          When the category is set trigger an on-modify script that sets both item and price for that invoice line to empty, then commits, refreshes and fluhes joins,

           

          and 

           

          When the item is slected via the dropdown, trigger an on-modify scrit thapt explicilety sets te both item and price for that invoice line to the appriate values then commits, refreshes and flushes joins.

          • 2. Re: Dynamic Value List trouble
            mark_b

            It looks like your price is coming from your Category table and not from your Product table.  Since an Invoice can probably contain multiple products from multiple categories, you need to set up a Join table so you don't have a many-to-many realtionship.  Here's a rough diagram of the concept. There are probably other methods.

            Cheers, Mark

            1-11-2014 2-25-36 PM.png

            • 3. Re: Dynamic Value List trouble
              erolst

              mark_b wrote:

              you need to set up a Join table so you don't have a many-to-many realtionship.

              A join table IS a many-to-many relationship.

              • 4. Re: Dynamic Value List trouble
                PSI

                The price of the product should have nothing to do with the category chosen. It should simply be a look from invoice line items to products by product sku. This would pertain to product description or any other attribute of a product you want to load into the line item record.

                 

                Also, there is no reason for a separate Category Table if in fact you have one. The catergory value list should be generated from your product table from the product category field. The second value list, to choose the product sku, should use a relationship from invoice to products -  Invoice::GlobalCategory - Product::productcategory

                 

                This value list would not show only product sku's that matching the selected category.

                 

                John

                • 5. Re: Dynamic Value List trouble
                  carguybikeguy

                  I have a join table set for my line items. Something I don't believe I mentioned is that for a given line item, there is 1 basic product chosen from different categories. Once defined, this basic product can be custom mixed with other mix-ins.

                   

                  Specifically: I offer a range of products. They fall under categories Hair Products, Lotions, Shower Gels to name three. To my choice, I can add up to three botanical boosts on offer. Those are also broken down into categories; Cleansing, Hydration, Calmative... and carry a price.

                  I can also add up to three essential oils on offer. They are not categorized but carry a price.

                  I can also add up to three fragrances on offer. Those are broken down into categories like Citrus, Floral, Musks... and do not cost extra.

                   

                  The number of unique custom recipes for any given line item is astronomical. A customer may even choose the same basic product twice on the same invoice but customize it very differently resulting in a different line item.

                   

                  I currently use a TO for each choice that can be made for a line item resuting in 10 different TOs. One for the base product and three each for the botanical boosts, essential oils and fragrances respectively.

                   

                  Hope this helps calrify.

                  • 6. Re: Dynamic Value List trouble
                    carguybikeguy

                    Yes. That it does already. I choose a product category based upon a value list defined from a field on all the product records for the category. This limits the choices available to me when I choose the product to only those that match the category chosen. That part is working but the price is populating based on the category and not the ultimately chosen product. By the way, the price it's placing in the field for price is the first value it finds in the dynamically generated value list.

                     

                    i.e. If I have chosen Hair Products as my category for my first line item, out of 40 or so products I get a value list of products limited to four items: Shampoo 4oz, Shampoo 8oz, Conditioner 4oz and Conditioner 8oz. It places the price for the first item in the line item price field.

                    • 7. Re: Dynamic Value List trouble
                      mark_b

                      By the way, the price it's placing in the field for price is the first value it finds in the dynamically generated value list.

                      The relationship that is supposed to pull the price needs to also include the unique product id (sku) and not just the category id, or if the product id is truely unique, that's all you need.

                      • 8. Re: Dynamic Value List trouble
                        PSI

                        I would go a step further than mark_b and say the relationship to pull the price should ONLY have the unique product sku. the category is irrelevant.

                         

                        John

                        • 9. Re: Dynamic Value List trouble
                          carguybikeguy

                          You're right. Except for the fact that when ensuring I use the unique ID for the product, the price no longer populates. Hmmm? I'll do some more digging.

                          • 10. Re: Dynamic Value List trouble
                            keywords

                            If you have the data set up right the price should be part of the product record, I assume, and therefore should pull through correctly when the product ID is selected—if you have your relationships and lookups right.

                            • 11. Re: Dynamic Value List trouble
                              flybynight

                              erolst wrote:

                               

                              mark_b wrote:

                              you need to set up a Join table so you don't have a many-to-many realtionship.

                              A join table IS a many-to-many relationship.

                               

                              This may just be symantics, but…

                              I would say "A join table is the correct way to do a many-to-many relationship."

                               

                              FileMaker will allow you to relate 2 table occurances as many-to-many in your graph… but it probably won't work right. I have seen some weird work-arounds to get things to seem like it works… but it really doesn't.

                               

                              A join table takes 2 TOs that you want to join with a many-to-many relationship, and adds a third table in between them, creating 2 one-to-many relationships, with the join table being the "one" to each of the "many" links on the other side.

                              …with the join table being the "many" to each of the "one" links on either side.  (Thanks for catching my goof, erolst!)

                               

                              That's how it was explained to me, when I was getting started, and it's the way that made things click in my head when I was first trying to "get" the mechanics of a many-to-many relationship.

                               

                              To a seasoned developer, this is obvious. But to a noob (and I mean that term with no offense) reading these threads looking for guidance, sometimes the seasoned pros take a little too much for granted. Sort of like in math classes… my teachers always docked me for not "showing my work" - by brain saw step 1 and could jump right to step 4, but not everyone works that way. (I'm sure my skills have gotten rusty since then!)

                               

                              Laters,

                              -Shawn

                               

                              Message was edited by: flybynight

                              • 12. Re: Dynamic Value List trouble
                                erolst

                                You're right – it IS semantics …

                                 

                                and you raised an interesting point as to the relationship of forum “seasoned developers” vs “noobs” etc. that would merit a lengthy, extensive and probably very interesting discussion – preferably over a pint of bitter – but presently I find this medium not real-time enough to do that (or I'm just not in the mood right now …)

                                flybynight wrote:

                                A join table takes 2 TOs that you want to join with a many-to-many relationship, and adds a third table in between them, creating 2 one-to-many relationships, with the join table being the "one" to each of the "many" links on the other side.

                                I consider it to be the reverse:

                                 

                                oneA --< manyAs|manyBs >-- oneB

                                 

                                but maybe that's just hair-splitting, aka degenerated semantics …

                                 

                                Have a good day,

                                – Oliver

                                • 13. Re: Dynamic Value List trouble
                                  flybynight

                                  Oliver,

                                  Thanks for catching my goof. I edited my previous comment.

                                  Yes, agreed about preferring the discussion over a pint!

                                   

                                  Laters,

                                  -Shawn