I have a two table database. The main table contains the project information and the sub-table contains the invoices for that project. What I want is when I go to enter a Vendor in the sub-table I will see a list of vendors already in the system (so I can standardize how the vendor name is entered). This could be a drop down list or preferably a type-ahead field that would narrow the possible responses based on what it typed.
Is this possible and if so how do I accomplish it?
Thanks,
Hi,
Would you like to try creating another sheet to save vendor information and then set up a link & load relationship on your project information sheet to link to the vendor sheet? With link and load, you can select a vendor on your project information sheet instead of manually enter vendor information every time.
No, that’s not quite what I’m looking for.
The Project information record does not contain any vendor related info. It is linked to a subtable for that project that contains the details of each invoice to complete the project. There could be many vendors for a single project. What I want is someway to ensure that the Vendor Name is always entered the same way (ie spelling, abbreviations, LLC, Inc, etc). So that “N & S Pools” doesn’t get entered as “N and S Pool” which would not be the same in a search.
Not sure how I missed it but the Select option for the field is almost perfect. Question, when I add a new vendor to the list it is at the bottom of the list. Other than opening up the Design window and sorting the list again is there anyway to auto-sort the list of choices when a new choice is added?
Hi,
It’s not supported to automatically sort. You’ll need to access the design mode and trigger it manually.
If you wish to sort automatically, you may need to use the link & load workaround provided by Fabio in the previous reply. Simply add the link & load field in your subtable and you’ll be able to select vendors for each invoice.
I guess I’m a little confused. I understand link & load for pulling multiple fields from one table into another but in this case it’s just the vendor name. So would I create a new table with the vendor names then link that field to the data entry vendor name in the other table? When entering a vendor in the “original” table it would reference the new vendor name table and display vendors based on what I had typed? Would it add new vendors to the vendor list automatically or would I have to first enter them in the vendor tabIe? have that now with the Select from list, except for the sorting of new vendors. Do I have that right?
Hi,
To manage your vendor information correctly, it is highly suggested to create a new sheet to save process data for your vendors. Let’s call that the “Vendors” sheet, and this sheet should at least have the “Vendor Name” Field.
After creating the “Vendors” sheet, on your “Projects” sheet, you can set up a link and load relationship to link to the “Vendors” sheet. The linked field on the “Projects” should be the “Vendor” subtable field which links to the “Vendor Name” field on the “Vendors” sheet.
Once the links and load settings are completed, the “Vendor” subtable field on the “Projects” sheet will select values from the “Vendor Name” field on the “Vendors” sheet. If you add a new Vendor on the “Vendors” sheet, it will also be added as a linked selection in the link and load relationship applied on the “Vendor” subtable field on the “Projects” sheet.
Thanks for all your help. I think I’ve got it now.
I just noticed one issue: I added a couple of new vendors and one of them is shown in Blue like a hyperlink. The others are normal. I’ve tried editing that vendor (Facility Management) but it keeps coming up blue. It doesn’t see to do anything it just looks wrong. Any ideas?
Hi,
Please send a support ticket via “Need Help?” at the upper right corner of your database and share the entry link to the entry you get this issue for us to check possible causes.
