8 Replies Latest reply on Sep 14, 2013 11:34 AM by BarbaraCooney

    Field flagging another in different table

    chefportal

      Hi All,

       

      I know this is probably an easy problem, but have not been able to figure it out.

       

      I have a 2 tables “products & suppliers” in the products table I have a preferred supplier field. And in suppliers table I have a preferred_flag field. My question is this, how do I choose a supplier in “Preferred Supplier” field and put a “1” in that supplier in the prefered_flag field in supplier table and un-mark the other suppliers that may have been marked (there should be only 1 preferred supplier per product).

       

      Mark

        • 1. Re: Field flagging another in different table
          Mike_Mitchell

          Write a script that captures the ID of the one you want to mark. Then loop through all the others and set their flags to "".

           

          HTH

           

          Mike

          • 2. Re: Field flagging another in different table
            chefportal

            I used a case statement in a field calculation, it woks in a fasion but get a "?" instead of a "1"

             

            I created the preffered_flag as a calculation field with the cauculation "

             

            Case (

             

            product::prefered_supplier = supplier::name;

             

            preffered_flag ="Y"

             

            )

             

            As is is unstored it does recalculate and is only in 1 field, just not sure why get a "?"

             

            Mark

            • 3. Re: Field flagging another in different table
              Mike_Mitchell

              When you set a calculation, you don't need to insert the name of the field you're calculating. Your calculation should read:

               

              Case (

               

               

              product::prefered_supplier = supplier::name;

               

               

              1

               

               

              )

               

               

               

               

               

              (Note I changed it from "Y" to "1", since that's what you said you wanted. You can change it back if that's different than what you need.)

               

              Mike

              • 4. Re: Field flagging another in different table
                reelsteve

                So ill assume a few things which u can correct me of I'm wrong.

                U have many products and many suppliers.  When the smoke clears, ur suppliers table will have a single preferred provider for each product (unless that product doesn't yet have a preferred provider).

                Painting a picture , u might have 30 products , 110 suppliers.  30 of those 110 will be the preferred providers and the remaining 80 are the second string which may get the call as a backup but lost out to a colleage.

                 

                Unless u have a specific need to flag the preferred provider field with a 1, switch it up and flag it with the Id of the product.  This way, u can derive from the existence of a value in this field , that the provider is a preferred, and u can also derive exactly which product it is a preferred for.  If its just a 1, u have no way to specifically link it to a product.

                 

                To select from a list, create a value list using as the source -  the id of the provider.  U can also check to have it display a second field which u can set to name.  If u tell it to hide field one , the selection list will just show names of providers but u will use a popup field selector so despite seeing the name from which u select, the value that will be used behind the scenes will be the id.  U can put a on modify script trigger on the popup selector that setfields the product id into the providers tag field (a few ways to write the script).

                The results being , every time u select a provider it will push the product id over into that providers record.

                 

                In terms of making sure that it is the provider to have the id of the product in its tag field can be handled a few ways.  What comes to mind would be to do a validation step in ur script prior to setting the field.  This may be handled better by someone else suggestion but I would prob grab a list of the preferred providers ala List (preferredProviderfield) and use patternCount to see if u already have this id in the list, if u don't - u r free to proceed.  If u do get a hit back on pattern count , u will need to blow it out which again , could be done a few ways.  The other option would be not to worry about more than one preferred being set into the field but instead , add a timestamp to the mix, either in its own field or concat'd to the id so u could check for the most recent timestamp in later uses of the flag.

                 

                Ill stop and leave the last parts details to ur gutcheck on the first half, then wrap back to clean it up.

                Best , Steve

                • 5. Re: Field flagging another in different table
                  chefportal

                  I just typed "Y" on auto pilot .. I used a "1" 

                   

                  Thank you, I did't think I was far off but still getting used to calculation logic.

                   

                  Mark

                  • 6. Re: Field flagging another in different table
                    Mike_Mitchell

                    If you look at the top of the calculation window (the part where you type the calc itself), you'll see this:

                     

                    cap.jpg

                     

                    That tells you that the expression you put in the calculation window is the right-hand side of the equation. The field you want to set is the left-hand side.

                     

                    HTH

                     

                    Mike

                    • 7. Re: Field flagging another in different table
                      chefportal

                      Hi Steve,

                       

                      Mike gave me the answer I needed, but just to give you an idea of what Im doing. When a product is created and is set to "active" it "must" have a prefered supplier attached (has option to create via script if non existant) otherwise it does not need a supplier attached. On the order form a product is chosen and the prefered supplier is marked ( although this is not the end, there is alot more logic befor the order is placed)

                       

                      EG:  Checks dilivery days, Cut of order times, Status Change ( if you need to order a "yellow widget" from your preffered supplier for next day delivery, but have missed order cut of time the order will flag this and give option to change supplier.

                       

                      Mark

                      • 8. Re: Field flagging another in different table
                        BarbaraCooney

                        My impression is that you need a join table btw products and suppliers, since it is a many-to-many. Don't products have many suppliers (and one is preferred) and suppliers can be the source of many products?

                         

                        I would have a portal of suppliers on a product form (portal is the join table). Next to each supplier's name, I would have a checkbox, dsp_flag_primary_c. Clicking the checkbox sets the key supplier ID in the product::supplier_primary_ID field. The dsp_flag_primary_c = 1 if the supplierID = product::supplier_primary_ID.