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.
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.
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?
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.
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.
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.
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.
What are the exact Go To Related Records options that you are selecting?
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.
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.
You can then use a portal to selectedVendors with three rows for selecting vendors.
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?
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.
Okay, thank you!
You have helped me so much, I really appreciate it.
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.