I have table A that has list of pharmacies with phone numbers addresses etc , linked to table B that has patient's info, from B I want to populate the chosen pharmacy of a patient from A through the relation, the pharmacy name in B is value list driven from A through the link, it does pull the list and when I chose the proper pharmacy I am having difficulty figuring out how to populate the rest of this pharmacy info in B from linked A.

3 minutes ago, sharabi said:

how to populate the rest of this pharmacy info in B from linked A.

There is no need to do that, and you should not do that. The very reason for having two tables is to store information in one place only. All the fields describing a pharmacy should be in the Pharmacies table. The only field in the Patients table that has to do with the selected pharmacy is the PharmacyID foreign key filed.

To display the selected pharmacy's info on a layout of the Patients table, simply place fields from the Pharmacies table on the layout of Patients. You might want to make them non-enterable in order to prevent accidental modification. 

(There are some cases where you do want to duplicate information: for example, if you're creating an invoice, you want to record the product's price as it was at that time and not have it change in the future. In such cases, you use a lookup)

P.S. Please do not use abstract table names like A and B. 

 

Ok, 

Patient table has a layout of pharmacy with foreign-key-pharmacy placed on it

Pharmacy table has primary key linked to that foreign key and it has 2014 pharmacy records.

on Patient table I made the layout setup displaying data from pharmacy table ( showing the same number of records)

I made the NAME field of pharmacy a value list displaying dropdown menu from the NAME field in the pharmacy table, how do I set the foreign key field to take the corresponding primary key of the pharmacy name (I chose from the drop down list ) so the rest of the fields on patient layout will display ( Address, phone number , fax, zip…etc)?

Thanks 

 

5 hours ago, sharabi said:

I made the NAME field of pharmacy a value list displaying dropdown menu from the NAME field in the pharmacy table

That is not correct. The value list should be defined to use values form Pharmacies::PharmacyID, also display values from Pharmacies::Name (if you like, you can check the "Show values only from second field" option - provided that the names are unique). And the value list list should be attached directly to the PharmacyID foreign key field in the Patients table.

See the attached demo.

pharma.fmp12

Thank you for the demo, it did it, however the problem is I have 2014 pharmacies , and it starts here with numbers I chose the second field to display the name but I cannot search for name in the fk-field, if I remember the number it is great but 2014? any advice.

11 hours ago, sharabi said:

I cannot search for name in the fk-field, if I remember the number it is great but 2014?

You don't need to remember any number, and you don't need to search for a name in the FK field. If you're doing the search manually, simply enter find mode and type the name (or part of the name) of the pharmacy into the Pharmacies::Name field (in my demo, that is the Name field below the PharmacyID field). This will find patients that belong to the pharmacy you have searched for.