Templates

[Mike J]

I have a DB that has the tables patient and patient samples. When creating a sample from a patient, at the moment, the user has to create each on separately.

In most cases, the user knows based upon the study what samples they need to take. I want to help them out by creating a template creation so that when new patients are added, they just need to select the template (dropdown or pop-up window) and hit create samples.

When creating the template, I've thought about two methods

-create all record and just import into the samples table and replace the foreign key with the new patients primary key (extra records)

-create one record with the relevant data and then loop through the relevant fields to create the new samples (sluggish and more scripting)

Thoughts on how you might create a templates so the user can set up the information once, then just use the methods above to eliminate the repetitive task.

Thanks

Mike

[Fenton]

If possible, this is the structure I'd try:

SamplesRef - This would have all possible samples, a record for each, and a unique auto-entered serial ID, which would be used everywhere else. There would be a Name field, and a Units field. The latter is where you put the units of measurement. This allows you to abstract the data in a way that lets you select which samples to use.

TestsRef - This would have all possible tests. Much the same as above, auto-entered Test_ID

TestSamplesRef - A "join" table. This would have single record for each required Sample for a Test. Its two main fields would be foreign keys, a Test ID and a Sample ID. It would also have its unique auto-entered serial ID field.

Those are the reference tables. I put the suffix "Ref" so I don't confuse them with the similarly named data tables. They should be completely populated (as well as you can) before using. There should be a good method to add a reference when needed from the other data entry tables. Now we have the "data" tables.

Patient - Unique auto-entered patient ID.

PatientTests - Patient ID and Test ID.

TestSamples - Patient ID, Test ID, Sample ID

When you are ready to create sample(s) for a PatientTest, a relationship from PatientTests to TestSamplesRef will identify all required samples for that test. Go To Related Record will isolate them. Import (or Loop) will bring them, creating records in TestSamples.

It is much like you proposed, though you maybe didn't realize it required reference tables, with a join table.

[Fenton]

Now I don't really know what these tests are, how many fields, how different they are. It may difficult to put them all in the same table. If they have a lot of common fields, but also quite a few different fields, that is OK. The problems then will be how to show them intelligently on the layout(s) for data entry.

You would need to give different tests different layouts, with the correct fields, or different tabs on a layout (but I think not). Since you would know which samples any particular PatientTest needed, then you could use that info to script navigation to required layouts.

If however different Tests had lots of fields, and very few fields in common, then you have a problem. You would have the same problem with Samples.

There is a fairly advanced relational method, which I talked about in this recent post:

http://www.fmforums.com/forum/showtopic.php?tid/194695/post/288333/fromactivity/posts/#288333

I've seen it done with Companies and People, but it would also work with such things as medical tests (which tend to have lots of fields, different for each). It would allow a separate table for each Test and Sample, with a central table for each, to unify them when they need to be viewed as a single entity.

It would be a lot of tables, yes. But it would not be "flat," it would still be fully relational and functional. The separate layouts would then go to not only the proper layout, but that layout would belong to the proper table for that Test or Sample. It would require a better understanding of relational structure, but would cut down on the confusion within each table. Whether it's worth it depends on how many different fields each test or sample has. If tens, then maybe not worth it, if hundreds, then yeah, I think I'd try it.

[Mike J]

Hey Fenton,

Yeah I got to the point where separate tables/test was the way to go and GTRR when needed. I guess I'm trying to reduce overheard/unnecessary duplicates when possible. I already have you suggestion: Sampleref as you called it so that the user creates all the records needed once at the begginning of a study and then whilst in the room(s) with patients can focus on the tasks and not data entry and making sure they collected all the samples.

This portion of my DB helps me build the template. The experiment is where we ask questions about several groups (control vs experimental). To compare them we need to assure that we collect all relevant sample in a standard fashion. Hence the need for this template. You can see the other tests as well in the TOG.

The second JPG is the layout template form. I'm using a tab system to get the correct UI form fields depending upon the button pushed. In the JPG, you'll notice that the middle list is a visual planning method and the type of event from the start of the experiment (when the patient begins the study).

Although the layout isn't close to being complete, any thoughts about it?

[LaRetta]

As an aside, starting table occurrences with something very long such as the green ... [color:green]EXP_experimentstemplate_(something) can haunt you when you (or a User) needs to select a table occurrence from a popup which won't expand so you can see the full name. If the 'meaning' portion of the name is at the end of the table occurrence name, you'll never be able to see it.

Just a thought ...