6 Replies Latest reply on Nov 10, 2010 1:59 PM by miw

    Find and Replace help

    miw

      Title

      Find and Replace help

      Post

      I have a two tables, Vendor and Product, linked to a join table named VendorJoinProduct.  Both tables use a unique serial number ID rather than a vendor or product name as their unique identifier (that way, if a vendor changes their name or a product name, I can change the name and the link remains valid).  In my VendorJoinProduct table, I am linked to VendorID in the Vendor table and ProductID in the Product table .  Multple Products can be linked to a Vendor and the same Product may come from different Vendors.  Also my population of Vendors and Products may be more than what may end up being linked in the join table; ie. I may have 100 Vendor names but not all of them may be included in my VendorJoinProduct table because they may not have any products linked to them yet. 

      I have another table, Inventory, that requires the use of a specific VendorJoinProduct ID and I want to use a list to select from the eligible VendorJoinProductIDs that represent the various Vendor and Product combinations.  Since I am using serial number IDs at the Vendor and Product level, I cannot get a defined list to show the descriptive form of Vendor name and Product name.  My attempted solution was to create a field in the VendorJoinProduct table that stored a calculation of [Vendor Name & ":  " & ProductName] by which I can create a descriptive list in a drop down menu.  My problem is if a Vendor name changes, I have to go into each instance in the VendorJoinProduct table that lists that Vendor so that the calculated field can update with the new name - a process that is a bit onerous.  So I then thought the proper way to affect the Vendor name change to my calculated field was to do a find and replace script if the Vendor name was edited.  However, I cannot figure out what that script should be since I will have written over an old Vendor Name and replaced it with a new Vendor name in the first instance; how do I capture the lookup of the old name to be replaced with the new name in all instances?  What would that script look like?

      The "better" solution, if possible, would be that in the Inventory layout, I could pull only a list of Vendors names, which have a defined VendorJoinProduct combo and then after selecting such Vendor name, a list of only the Product names linked to that Vendor in the VendorJoinProduct table.  This would be a preferred solution if I could figure it out.  Any ideas on the "better" solution?

      Using FM 11 ProAdvanced

      Many thanks for any ideas or help!

        • 1. Re: Find and Replace help
          philmodjunk

          Your post challenged me to test an assumption I've been operating under and I discovered my assumption was wrong. Thanks for the chance to learn something new! Wink

          In VendorJoinProduct, define a calculation field such as this: Vendor::VendorName & " " & Product::ProductName.  As a calculation field, this field will update automatically any time you edit the referenced related vendor or product name fields.

          Define your value list to be a two column value list. Make VendorJoinProduct ID the source of values for column 1 and the above unstored calculation field the value source for column 2.

          I had always assumed that an unstored calculation field could not be used as a source of values for a value list, but apparently such a field works for the column 2 value source and this should work well for you here.

          • 2. Re: Find and Replace help
            miw

            PhilMod:  I actually did what you mentioned above; it's a great fix for a shorter list that does not need to be sorted.  It works as long as I do not select the second field in the list to be the "Show only" field or as long as I do not try to sort by the second field.  Given the length of my list, I really need to be able to sort by the calculated field, and that is why I tried using a stored calculation field instead of an unstored one.  Any other ideas?

            Also, getting back to the ideal solution I was looking for whereby on the Inventory layout I indpendently select the Vendor and Product names and then somehow linking that combination to a VendorJoinProduct ID.   I am able to define a list to select only those Vendors in the VendorJoinProduct table by using as my first field VendorJoinProduct::VendorIDLink with my descriptor second field being Vendor::VendorName.  What I cannot figure out is to how then create a list of Products from the VendorJoinProduct table only for such selected Vendor.  Any ideas on that second step?  And then how would I get the combination of the two indpendent selections to lead to the correct VendorJoinProduct ID selection?

            • 3. Re: Find and Replace help
              philmodjunk

              Yep, both those value list options require an indexed field which this calculation can't be.

              Your second paragraph suggests a way to get that shorter list. It's called a conditional value list. With additional relationships linking inventory to a second table occurrence of the join table, you can do this.

              In Manage | Database | Relationships, each "box" is called a table occurrence. Click VendorJoinProduct to select it, then click the button with two green plus signs to make a new table occurrence box that refers to the same data source table. You can rename this box, VendorProductSameVendor. Do the same with Products, naming the new occurrence, ProductSameVendor. Define a Vendor ID field in Inventory.

              Now create these new relationships in addition to  your current ones:

              Inventory::VendorID = VendorProductSameVendor::VendorID
              VendorProductSameVendor::ProductID = ProductSameVendor::ProductID

              On your inventory layout, define a value list for VendorID that lists Vendor ID's from VendorProductJoin to list only those vendors that have been linked to at least one product.

              Define a value list for Inventory::VendorProductJoin ID to list IDs from VendorProductSameVendor in column 1 and product names from ProductSameVendor. Select the use only related values, starting from Inventory option so that only products from the selected vendor will be listed.

              Now you can first select a vendor in Inventory::VendorID and then select a product from that Vendor's list of products in the Inventory::VendorProductJoin ID field.

              • 4. Re: Find and Replace help
                miw

                PhilMod:  Works like a charm!  Never worked with a conditional value list of this ilk.  I am able to successfully choose the desired VendorJoinProductID in this manner.  I do have a follow up:  Once I make the correct VendorJoinProductID selection through the inputs of Vendor and Product as you described, I can put on my Inventory layout a non-browsing edit box to show the Vendor::Vendor Name - this works with no issue - the Vendor Name is the same as for my VendorJoinProductID.  However, I cannot figure out how to do the same for ProductName.  I would have thought that since these were defined by relationships, that this would be easy.  I put on my Inventory layout a non-browsing edit box for Product::ProductName but I only get the ProductName for the very first record to show up on my layout.  What's going on?

                • 5. Re: Find and Replace help
                  philmodjunk

                  I created a test file when putting my last post together. On it, I can place the Product::ProductName field on an Inventory based layout and can then see the desired product name without any trouble.

                  http://www.4shared.com/file/BGFWYYxM/TwoTable2ColValuelist.html

                  • 6. Re: Find and Replace help
                    miw

                    Thanks for the test file.  I had a link going the wrong way and your file cleared up my mistake quickly.  An awesome result that is very helpful!  Thanks!