Sales personnel take a physician (contact) around to community practices (accounts) in order to assist them in getting referrals. My task is to help them enter this information efficiently and then be able to report on it. I created a custom object with a contact lookup field for the physician and 5 account lookup fields for them to enter practices visited. I want the report to show how often practices were visited in this manner. My stuck point is that when I create the reports, the 5 lookup fields are distinct - the same practice may be located in any of the 5 fields (reporting columns) in different rows. I am asking for help with creating the desired report but am open to scrapping the whole approach for a better one. I am not skilled in the ways of code so if your answer involves that, please include and example. Much thanks.
1 Answer
To me this sounds like you should be using the PersonAccount feature, and either create a task for each visit, or create a new table/sobject which records each visit and have it lookup to the Person Account. This should be a master detail relationship. On these visits, you should have 2 fields required at least, Patient (Person Account) and Doctor (Could be a User, an Account, or new Table).
This is known as a Many To Many Relationship. You'll have 1 patient record, 1 doctor record, and multiple visit records which link to both.
