Hi ​@Devangvala,

Hope you’re doing well. It will help if we can see how you have your database set up. Do you have a list of symptoms and a list of medicine that will treat that symptom? And then my guess is that a user would select from a list of symptoms and then you want to see what are the best combination of medication that would be able to treat all of the symptoms (ideally using medication that would treat multiple of their symptoms as to require less? 

Hi ​@airvues 

Yes 

Table 1 -symptoms (column 1 -symptoms name, column -2 link to medicine name)

Table 2- medicine (column 1 -medicine name, column -2 link to symptom name)

Both are many to many relationship

I select multiple symptoms and common medicine name should be displayed. How to make that kind of automation, interface or any other thing. 

Hey ​@Devangvala,

I guess you would also have a Patient Submissions table, of some sort? Otherwise, you would want to have that, where you would have at least fields: Patient Name (single line text, or linked to a patients table), Symptoms (linked to symptoms), and Suggested Medicine (lookup).

Where you will want to focus on is on the Suggested Medicine field! This is a Lookup Field type, which basically will be looking up from the Symptoms table (specifically from those selected for the corresponding record) any Medicines linked to them on the Symptoms table. You can read more on Lookup fields on this other article.

Furthermore, if you’d like to be specific on additional data for each combination of Symptom and Medicine (e.g. secondary effects, warnings, etc) you would actually want to build a junction table (check out this video on Airtable Junction Tables). In this case, then the architecture described above would change slightly.

If you need any help setting this up, feel free to grab a slot using this link and we can go through it together in a few minutes. I’d be happy to show you around.

Mike, Consultant @ Automatic Nation