I would make a related table for these diagnosis.
In that table you can create three fields:
ID - A number field with autoenter Serial Number
Diagnosis - A text field
Code - The code that corresponds to this diagnosis.
Then you simply create a dropdown value list based on the first field ID and with the diagnosis in the second field.
Then you simply add the code field from the diagnosis table to your layout and the correct code is displayed and also related to your table.
If you really want to have this code in a seperate field you could create a field that acts as a lookup. And that looks up the Code value when you select a diagnosis.
I think you are going a little to far wanting to create a conditional value list; The way I see it, you don't neeed that, you only need one dropdown to select a diagnosis. Depending on which diagnosis you selected, the correct code needs to appear.
It's really simple. It's exactly like how you would add a product to an invoice and lookup it's price.
Thank you for your help, DaSaint.
I am sorry to say that I need a more step-by-step explanation. I tried to follow your instructions, but some issues came up:
1) Should the "diagnosis" field use a manually inserted value list, like this:
2) in which context should I insert the codes? Another value list, like this:
That makes 3 values list. When creating the value list you mentioned, in the "specify fields for value list" dialog box, should I "include all values"?
I've been fighting with this issue for some time, and any help is most welcome.
Thank you again.
If you make a value list with manually entered values you won't really be able to do what you want.
The idea is that you get the values for your value list from a seperate table.
A table "Diagnosis" that contains every possible diagnosis in a field. And that also contains the corresponding code.
That table should therefore contain two fields: "Diagnosis" and "Code".
Because we are going to relate this table to the other table you already have we will need to use a unique ID to link them. So we also add an ID field. This is a number field with an auto enter serial number.
So you get this:
In this table you can enter every diagnosis and you immediatly put the corresponding code.
This is ofcourse assuming the code is always the same for a certain diagnisis.
If the code can be different from the diagnosis we need to fix this differently. But I don't think that's the case.
The idea is that you can easily manage the diagnoses and the codes, and add different ones. And that the Codes are already set up next to the corresponding diagnosis.
OK, now how do we link this to the other table that you need to enter this data?
Well, we have two options.
Either we select a diagnosis in a dropdown and show the Code field from this "diagnosis table". That way you don't always have to store the same code over and over again; You store it once and just link to it.
However there could be reasons to store the code in the second table. This would be called a "lookup".
If for instance your codes can change over time, but you don't want them to be changed on the older records you have already made, you need to create a lookup.
If, however your codes never really change you can just link to the code and leave it as it is.
Let's go with this one in our example, if you want it differently we can always still change that afterwards.
I made another table called "Admission" because I don't know what other table you are using to enter your diagnosis. Because we are going to link to the ID field of the diagnosis I added a "DiagnosisIdFk field to my "Admission" table. FK stands for foreign key, and this is a number field that will store the ID of the diagnosis.
Now lets create the relationships:
Not to hard, and pretty logical. You can look at he relationship as a tunnel. If you are on an admission record. And your DiagnosisIdFk is "3" then you can look trough the tunnel into the Diagnosis table and you can see the record with ID "3".
You can see that records Diagnosis and that records Code.
Now create a layout the the admission table called LayAdmission, and add all the fields.
Now we need to be able to get the correct Diagnosis ID into the DiagnosisIdFk field.
So our DiagnosisIdFk field will be the field we put a dropdown on.
In the dropdown list settings choose "Use Values from field" and click "Specify Field"
The values come from the Diagnosis table. The first value (the value that will be entered in the DiagnosisIdFk field) is of courde the ID field from the diagnosis table. But because we want to be able to see the diagnosis we choose for the Diagnosis as value from second field.
At the bottom we "include all values" and we choose to "only show values from second field." We don't need to see the ID.
Ill continue in a second post.
Now you should already be able to select diagnoses from your dropdown list. That's number one.
Now we need to add this code field.
And also, when we select a diagnosis we only see the ID. Let's fix that.
There are multiple ways to deal with this, but let's take the safest way.
First we'll add the Code:
On your Admission layout, go in layout mode and add a field. Add the Code field from the Diagnosis table.
Now, if you select a diagnosis, it should display the correct code.
Now to also display the diagnosis you simply add the diagnosis field from the Admission table to your layout and you'll see it to.
Here is a nifty trick:
Copy the DiagnosisIdFk field (In windows you can press Ctrl and drag the field to copy it) Then make these changes:
- Specify the field as being the Diagnosis field from the Diagnosis table.
- set it so that it's control style = Edit Box
- remove it's label if it has any.
- In the inspector under "Data" - "Behavior" - "Field Entry" uncheck "Browse Mode"
- Now for the DiagnosisIdFk field, uncheck "Find Mode"
- Now place the Diagnosis field on top of the Diagnosis IdFk field.
- It's possible you need to set the "Diagnosis" field to have a white background.
That way you don't see the Id field but you can still use the dropdown. And you always see the diagnosis.
And at the end of the exercise you should end up with something like this:
Thank you so much, DaSaint.
It worked perfectlly. I cannot thank you enough for the patience you had, setting up that example.
One more issue: Is it possible to do the same with another value list, but with a check-box set as control style, instead of drop-dow?. I mean, in a given check-box set, the values cheked would bring up epecific value (codes)?
That might be a little bit less ideal. Because a checkbox set can be in one field. And if you check multiple values you get multiple codes.
You could list the codes so you have multiple codes separated by a return character. But I don't know if that's ideal.
Or otherwise you could have a portal where you select multiple diagnosis and get the code in a second field.
Tell me exactly what you are trying to do and what you need to do with the codes afterward.
I just made a little test and added it to the previous example:
But you need to be carefull.
I don't really find it ideal to put multiple values in one field.
It really depends on what you are doing with this data later.
These data (diagnosis and respective codes) will be attached to each patient's record. They must be available when I search (sort) by diagnosis, and they will be printed in reports and procedure requests.
The check-box issue is this: I have already a check-box set of diagnostic test (like: blood sugar, hemogram, sodium, and so on), that are inserted in a layout/report that I prepared to be printed out (exam request). A check-box set is much easier to use than many drop-down, separate fields. The problem is that each diagnostic test must have the corresponding code next to it, in the request. A check-box set that brings up the code of each individual test would be ideal.
Is it feasible?
Did you try out my latest version of the demo file?
Did you see the checkbox set and the correcponding codes there?
Is that solution do-able or not?
Let me know so we can maybe look for a different solution.
If it is just a small number of tests with a code next to it you could maybe do something with conditional formatting.
Is there any way you could show us a screenshot of the document?
So we can see what options we might have.
Only today I was able to check out your demo file. Your solution for the checkbox set is working fine. Regarding your previous question, these codes are usefull only to print out diagnostic test requests. They don't need to be stored, and I won't need to find them later.
The diagnosis and its code (my first question to you) however, need to be available when I access the patient's record. What is the problem with the solution you proposed? Is there a (higher) risk of loosing or mixing data? It is true that, with time, about a hundred different diagnosis could accumulate. But it seems to be working fine, so far.
Once again, thank you for your help.
What is the problem with the solution you proposed? Is there a (higher) risk of loosing or mixing data?
Not really a problem, the only thing is that with this List calculation field you have multiple codes in one field.
I don't usually like that.
But it depends on the situation and if it works for you all the better :)