14 Replies Latest reply on Feb 28, 2017 7:14 AM by philmodjunk

    Autofill one field based on another & more

    BERGSTEN

      Today's troubles concern what we call our consumables layout.

       

      We have three text fields available for "Preferred Vendors". That field is currently a drop-down list of all of the businesses on our "Companies" database. What I want is the following:


      When the user either manually types in or selects the desired company (or Preferred vendor) from the drop down list, I want their Customer_ID to propagate next to it in the field I currently call "Preferred_Vendor_ID" (or, vice versa).

      Then, I want a button next to it- to take me to that company's entry on our "Companies" database.

       

      There IS a relationship between "Consumables" and "Companies" ... "Customer_ID" = "Preferred_Vendor_ID" as well as "Preferred_Vendor" = "Company Name".

      So far i have tried look-ups but I can't get one to respond to the other for some reason. Clearly I'm missing something.

       

      I was hoping entry of either field would autopropogate the other.

      Then I just confused as to how to set up the button so it would respond to one field or the other- and take the user to the correct company entry.

       

      Let me know if I need to re-word or clarify any of this.


      Thanks!!!

        • 1. Re: Autofill one field based on another & more
          philmodjunk

          We have three text fields available for "Preferred Vendors".

          Why three fields and not just one such field?

          There IS a relationship between "Consumables" and "Companies" ... "Customer_ID" = "Preferred_Vendor_ID" as well as "Preferred_Vendor" = "Company Name".

          Is that one relationship or do you link to one occurrence of Companines using the IDs and to a different occurrence of Companies using the name?

           

          Why match by name?--names are neither unique nor unchanging so matching by name should not be done except for certain, very specific purposes.

           

          With relationships and auto-enter features correctly defined, selecting a vendor from a list should enable you to both link your current consumables record to that vendor and provide a means for to pulling up that vendor's record on your companies based layout.

           

          So step one is setting up the correct relationship.

           

          With this relationship:

           

          Consumables::VendorID = Companies::Customer_ID

           

          You can suet up VendorID with a drop down list or pop up menu of Customer IDs and names from Companies. Selecting a vendor by name from this list, then links it to a record in Companies. Both auto-enter calculations and Looked up value field options can then copy data from fields in Comapanies. You can also place fields from Companies on your layout and they will automatically show data from Companies once you select a vendor.

           

          With a vendor thus selected, you can use Go To Related records to bring up that vendor's company record on a Companies based layout.

          • 2. Re: Autofill one field based on another & more
            BERGSTEN

            My boss wants the top 3 preferred vendors- I tried to get him to agree to one- but he said "sometimes we get diff deals from diff vendors based on the time of year, market etc"

             

            Currently, the occurrence includes the name and ID. However upon your suggestion I removed the Name from the defined relationship. I thought they both had to be defined to help autofill.

             

            I currently have the Vendor field set to lookup names/ID's but when I select one- it doesn't fill in the Vendor ID (which I have just replaced with Customer_ID from Companies). So I'm clearly missing something there.

            • 3. Re: Autofill one field based on another & more
              philmodjunk

              The problem with 3 different fields is that you need three different relationships. There are ways to deal with that, but one thing at a time. Once you have this working, I would suggest that you need three records rather than three fields.

               

              but when I select one- it doesn't fill in the Vendor ID (which I have just replaced with Customer_ID from Companies). So I'm clearly missing something there.

              The ID should be entered into the field where you use that value list to select a vendor just be selecting a vendor. If it's a pop up menu, you may not be able to see that ID, but that's what this type of value list should be entering.

               

              Why do you need this ID to appear elsewhere on your layout?

              • 4. Re: Autofill one field based on another & more
                BERGSTEN

                I should be more specific-- the Preferred Vendor field is a repeating field, with 3 places to enter the name of a company.Screen Shot 2017-02-27 at 2.52.35 PM.png

                 

                I suppose, I don't necessarily need the vendor ID to appear anywhere else if i can get the button to take me right to it's corresponding entry in "Companies". I started down this path because my boss suggested it.

                • 5. Re: Autofill one field based on another & more
                  BERGSTEN

                  Even with eliminating the need to involve the vendor ID-- I can't seem to get the "Get related record" script to work. It doesn't seem to retain the vendor name despite the fact that the two share a defined relationship (Vendor ID = Company Name)

                  Clearly I am missing some basic logic even though it seems very straight forward.

                  • 6. Re: Autofill one field based on another & more
                    philmodjunk

                    You cannot use a repeating field for selecting the vendors. If you use a repeating field as the match field, you get an OR relationship that matches to all of the values in your repetitions.

                     

                    Try using ing just a single non-repeating field for this. Once it works, we can look at ways that allow you to select more than one vendor.

                    • 7. Re: Autofill one field based on another & more
                      BERGSTEN

                      Okay so I now have Preferred_Vendors as a single, non repeating field. It is set to be a drop down list that pulls it's data from "Company_Names" in Companies.

                       

                      I am still not able to get the button to work, so it will properly take me to its corresponding record in Companies.

                      • 8. Re: Autofill one field based on another & more
                        philmodjunk

                        What are the exact Go To Related Records options that you are selecting?

                        • 9. Re: Autofill one field based on another & more
                          BERGSTEN

                          Screen Shot 2017-02-28 at 8.39.40 AM.png

                          This is what it was, but I found the problem. I forgot that the relationship between the two tables/layouts was on "Companies 2"- something that was created as a result of the new relationship. Once I included Companies 2 as the from table, and using layout Companies- it worked.

                           

                          Now the problem, as you foresaw, is I cannot get two other fields to perform the same function without creating another 2 relationships, right? Which seems kind of messy?

                           

                          EDIT: Apparently making a new relationship does not matter. Or if so, I'm doing something wrong. I tried to make another relationship for the second field (Preferred_Vendors2) with Companies- but when I go to fill in the script the same was as the last one, it wouldn't give me Companies as  a layout to use.

                          • 10. Re: Autofill one field based on another & more
                            philmodjunk

                            If you can't select the companies relationship, you've linked to the wrong table occurrence. You need a new occurrence of companies. And you should also be selecting the "current record" option in this step.

                             

                            But you also do not need multiple relationships if you set up a related table for selecting vendors.

                            Consumables----<selectedVendors>----Companies

                             

                            You can then use a portal to selectedVendors with three rows for selecting vendors.

                            • 11. Re: Autofill one field based on another & more
                              BERGSTEN

                              I'm afraid we're getting into territory I don't exactly understand.

                               

                              When setting up a related table, do I just literally create a table with fields from each layout in it?

                              • 12. Re: Autofill one field based on another & more
                                philmodjunk

                                You already have related tables such as Companies and Consumables. SelectedVendors is just one more such table with the same need for match fields. You would not "add fields from each layout", but you would need to define at least two new fields: one to link it to consumables and one to link it to an occurrence of Companies--which you would also format with your value list in order to select a vendor.

                                • 13. Re: Autofill one field based on another & more
                                  BERGSTEN

                                  Okay, thank you!

                                   

                                  You have helped me so much, I really appreciate it.

                                  • 14. Re: Autofill one field based on another & more
                                    philmodjunk

                                    One thing also:

                                     

                                    Should your boss ever say: "We really need 4 preferred vendors" (Or 5 or 6 or....). You can simply smile and say "sure thing" as you would only need to add more rows to your portal in portal set up or even add a scroll bar to have an unlimited number of selected vendors. No other changes would be needed to make that happen.