Help with a

Hi there, all:

By way of background, I have ZERO knowledge of how to code/script, so please forgive me if I miss any details that would be helpful in the description below.

PROJECT GOAL: create a database of medical expert witnesses that will allow attorneys in my firm to easily:

  • Input information concerning the medical experts that we use (initials of reviewing attorney; date of review; name of expert being reviewed; rating of expert on scale of 1 (worst) to 10 (best)); and
  • Look up the reputation of a given expert at a given time by going to a search page, entering an expert's name and the date that the search was run, and receiving a single webpage that features [a] a table detailing the average of all ratings associated with that examiner over the past 36, 18, 12, 6, and 3 months; [b] a bar chart that features the range of possible ratings on the x-axis (i.e. 1, 2, 3, 4, 5, 6, 7, 8, 9, & 10) and the number of times the expert was assigned each possible rating over all time on the y-axis.

WHAT I HAVE: Thus far, I have generated two objects and two pages. The objects are called "NAMES" and "REPUTATIONS"; the pages are called "INPUT" and "OUTPUT." These interact as follows:

  • Object #1: NAMES: the names of the medical experts that we come up against, stored in three columns (name [first last], title [e.g. MD, DO, Ph.D., etc.], and specialty [e.g. Chiropractic, orthopedic surgeon, pain medicine, etc.])
  • Object #2: REPUTATIONS: the reviews of the medical experts that we deal with, stored in four columns (name [first, last], reputation [number on a scale of 1 to 10, where 1 is the least desirable/worst and 10 is the most desirable/best], reviewing attorney's initials [stored as three letters so that questions concerning a rating can be directed to the right party], and the date of the review).
  • Page #1: INPUT: This page allows users to input the four pieces of information that are stored in the REPUTATIONS object. With regard the the "name" portion of each record, I have connected the NAMES object to the REPUTATIONS object so that users inputting information using the INPUT page/form can search for a given expert's name, which will auto-populate based on the information found in the NAMES object (the idea being that all reputation information can be associated with a given expert, allowing us to avoid losing data due to misspellings, miscapitalizations, etc.).
  • Page #2: OUTPUT: This page currently performs a search of the REPUTATIONS object based on the information in the "name" field, displaying a table of all reviews of a given expert that have been submitted to date. It also allows users to run a report, that shows a bar chart featuring the range of possible reviews on the X-axis (i.e. 1,2,3,4,5,6,7,8,9,10) and the number of reviews for a given rating on the Y-axis. At present, this bar chart does not automatically filter based on the name that the user searched; it does, however, allow users to perform an additional search using the evaluator's name.

WHERE I AM HAVING PROBLEMS: At this point, I am having two problems.

  • GENERATING AVERAGE REVIEWS: I would like to be able to dynamically generate the average reviews that have been submitted over given time periods. These averages would be based on [1] the information that users have input over time; [2] the name of the evaluator that a user enters into the search field of the OUTPUT page; and [3] the date on which the user submitted their search using the OUTPUT page.
  • GENERATING A BAR CHART BASED ON A USER SEARCH: I would like to automatically generate a bar chart (run a report?) using the data that is stored in the REPUTATIONS object and automatically including a filter that equals the name the a given user searched for in the OUTPUT page.

Any help would be HUGELY appreciated, and, if you are truly interested, I may be able to finagle some money out of our office manager if I can show the bosses a blueprint for how we could bring this database to a point where it would be useable as intended.

Jason Knox

Hi Jason,

Thanks for writing in. Here are some thoughts on the places you are getting stuck:

GENERATING AVERAGE REVIEWS. Generating averages is something you can do with a Formula field on connected records. (Help article on Formulas). You then could filter that field to only show the average based only on the past 3 months of reviews, for example. In your explanation, you could have 5 average fields, one for each of the different time frames you want to track.

GENERATING A BAR CHART BASED ON A USER SEARCH. This request sounds like a great fit for our Builder Network! You can submit your project with details about your project, budget, and timeline. Knack vetted builders can provide you with an estimate to build your app for you. You can then work with the builder of your choice to complete your project.

You can learn more and submit your project information here: Let us know if you have any questions about the process.