A Scoring System

[DavidTrap]

I am using a checkbox list to select multiple items from a value list in one field.

I then want to have an automatic calculation that applies different numerical values to the selected items and adds those numbers up to give a final score.

It is used to calculate a person risk of complications from an operation. The more risk factors they have the higher the final score, but some risk factors have a greater weighting than others, hence the different values for each item.

Should I use a checkbox list or individual fields for each item in the scoring system??

Thanks in advance

David T

[LaRetta]

Should I use a checkbox list or individual fields for each item in the scoring system??

Hi David,

Neither. I would suggest related records. To reproduce functionality similar to checkbox, you might consider this approach. But it would help to know more about your setup (what tables you have; whether you are going to use a join table etc).

LaRetta :wink2:

Edited June 25, 2008 by Guest

[DavidTrap]

Not really sure what you mean by related records - I'm very new at this.

I have started creating something that works, but it is very clunky and a long winded series of If functions. I've attached the file, with the irrelevant stuff removed. I have other checkbox lists that I would like to work with, so am trying to work out how to achieve this without creating individual fields for each item.

if you click on the boxes on the left, you will see the additive euroscore increase - but different items have different values (1 for Chronic Pulmonary Disease, 1 for Female, 2 for extracardiac arteriopathy).

Any suggestions???

Thanks

David T

Euroscore.fp7.zip

[LaRetta]

Well, I thought the file referenced in the link would make it clear. But maybe it will help if we translate the meanings into your file. I've reworked your file (please see attached).

UPDATE: I noticed that I left a calculation in Criteria which is ValueListItems() of the Euroscore. This was only used to create records from the custom value list. It can be ignored as can the script called 'Turn VL into records." :blush2:

Euroscore.zip

Edited June 25, 2008 by Guest
Added blushie update ...

[comment]

I am going to disagree. This is a relatively simple risk-assessment calculator, and a join table is not required here.

I have attached a basic implementation using a single checkbox. It can be smartened up by using several instances of the same field, each one with a different section of the value list. This would be not only for convenience (divide the factors into groups) but also required, since some of the factors are mutually exclusive.

EuroScoreAdd.fp7.zip

[LaRetta]

I like the concept but I have a few comments (smile)

1. Why did you pro-rate the cScore depending upon age? I see no indication that there is an additional increase; only 'over 60' should be [color:red]2. I've searched and see no reference for it.

2. What if there is more than one operation? You have the patient details in with the operations. [color:green]Patients should be separate table, I would think.

3. I would question the accuracy of the age based upon 365.2425. Is 'close' okay in this reference? Probably but it bothers me.

4. Most importantly, how would one create a report based upon each factor with the Patient name in the body if there were more than one factor selected per patient?

I'm sure you've thought of all these things. I ask because I want to understand and I was surprised by what I saw.

UPDATE: Added green.

LaRetta

Edited June 25, 2008 by Guest
Fixed number in red

[comment]

1. Why did you pro-rate the cScore depending upon age?

See: http://www.euroscore.org/calc.html

There's an Excel calculator that you can download from the top of the page. I used that as the model for my file.

2. What if there is more than one operation?

Yes, at least in theory the patients table should be really Operations, with patients info coming from a related Patients table. In practice, I suspect this is a stand-alone tool for the surgeon's use only, so the Operations table IS the Patients table (if the same patient has another surgery later, there will be a duplicate record with no link between them).

3. I would question the accuracy of the age based upon 365.2425

Well, it's more accurate than the 365 that they used. But yes, I believe in this context it's the correct approach. We're assessing an age-related risk, not sending a birthday card.

4. Most importantly, how would one create a report based upon each factor..

I don't think such a report is required - as I said, it's a calculator. However, if you wanted, you could just print out the checkbox field, formatted as edit box and set to shrink. Or, if you also want to see the associated values, you could do this in a calculation field using the List() function or a custom function.

[DavidTrap]

You are both fabulous - thanks very much for your assistance (far more than I expected). I've learnt from both examples and it will allow me to use similar principles in another area of my database.

As for the patients age - I had planned to pull that variable out of another field in the database. I had thought of a way to calculate the required amount from that, so I left it out of the example I posted. I'm quite impressed that you found the actual calculator online.

Never thought programming could be so interesting!!!

Thanks again,

David T

Edited June 25, 2008 by Guest