calculation: do ALL fields in a record contain variables

[Woodnote]

Hi everyone,

I'm sorry if someone has already answered this question, I tried searching through the forum but was unable to find an answer. I've included the backstory below this question in case it helps.

My question is this...I need to create a calculation/function that returns "true" if all the fields in a specific record contain values, and false if ANY of the fields in that record are empty. I know that I can do this by creating a calculation that uses If(IsEmpty(field 1) or IsEmpty(field 2) or etc.) but then I'll need to enter the variable names for hundreds of variables (total) into the calculations that I'll have to create for each tests table. Is there any elegant way to create a calculation that looks something like this:

If (all of the fields in RECORD1 contain valid variables) return "true" otherwise return "false"

Background:

...I'm creating a database for the research study that I run using filemaker pro 11 advanced. I've gotten pretty far (I have all the relationships set up and many of the layouts built) but I have a problem that I've been unable to solve so far and I thought I'd ask here.

Part of the database is designed to facilitate the entry of collected data. I administer a number of tests to participants at the beginning of the study and at the end. I've defined each test as its own entity and all the tests are linked to a single entity called "time points" which is in turn linked to the entity "participants".

I have a data entry layout that is associated with the "time points" table and then have tabs that contain portals to each of the tests so that data can easily be entered for each test.

Edited November 30, 2010 by Guest

[comment]

I know that I can do this by creating a calculation that uses If(IsEmpty(field 1) or IsEmpty(field 2) or etc.) but then I'll need to enter the variable names for hundreds of variables into the calculation.

Well yes, if you want to know if any field is empty, you must check them all. Ostensibly, you could use a custom function for this - but I believe you have a deeper issue here. I didn't quite understand your description, but it seems you are using "hundreds of" FIELDS where you should be using RECORDS in a related table instead.

[Woodnote]

it seems you are using "hundreds of" FIELDS where you should be using RECORDS in a related table instead.

Hmm, I think I used the wrong terms in my description...each research participant has a record in the "participants" table, which is related to a record in the "timepoints" table in one-to-many relationship (each participant has many timepoints, each timepoint has one participant). Each timepoint is related to many tests, i.e testA, testB, testC, etc (each of which has its own table)...each test has many fields. So if I were to create a calculation in each test's table as described above, I'd have to enter the names of every single variable in each test into each test's calculation. Combined...there are hundreds of variables that I'm tracking in this database (spread out across all the different tests)

does that make more sense?

[comment]

I'm afraid that's still not quite clear. What is a "timepoint" in real life? Why do you have a separate table for each test?

Most importantly: why does each test have "many fields"? What exactly are those fields? My guess is they are something like Question1, Question 2, Question3, etc. and/or Response1, Response2, Response3, etc.

If my guess is correct, you have a flawed data model. Every question should be a record in a Questions table (a child table of Tests). Every response should be an individual record in a Responses table - related both to the question and to the participant (or perhaps to a participant's "timepoint"?).

[dansmith65]

Are you trying to determine if the test is complete or not? (if all fields contain data, then test is complete?)

If so, you will probably want to use trim() on the field values before evaluating if it's empty, so that the user cannot only enter spaces. You may even want to take it a step further and determine if it contains alpha-numeric characters, min. amount of characters, etc.

If you want to do this via a script, then it may be possible to use the "Go to Next Field" step in a loop to evaluate if all fields on the layout are empty. That might be tricky if you have each test in a tab though.

If you want to use a calculation, then look into using FieldNames function. (actually, you could use this functions in a script too; might be better than the "Go to Next Field" step)

[Woodnote]

Thank you for your fast reply!

And yes, that's it exactly (if all fields in a record (for a specific test given at a specific timepoint to a specific participant) contain data, then the test is complete)

would it make it easier to create the script if I changed the format from one layout with many tabs (each tab contains a porttal for each test) to many layouts (one for each test) that the person entering data switches between by clicking buttons on the layouts?

thanks

[Woodnote]

thanks for taking the time to try to help me,

a "timepoint" is a specific point in the training regimen.

My study aims to determine if a specific training regimen that takes several months affects (improves) study participants scores on many different tests (all of which yield numerical scores). To determine this, I administer 29 tests at various points (timepoints) during the participants training regimen (at baseline, in the middle of the training, at the end of the training, 6 months after the training, etc)

what would the advantage of having a "questions" table and an "scores/answers" table as a child of each test entity (there are 29)?

More importantly what are the potential pitfalls of having my database set up the way it currently is (where each test question (all of which are unique) is an attribute of a specific test and the subjects scores/answers are part of a record associated with each subject and each timepoint

thanks again

Edited November 30, 2010 by Guest

[comment]

what would the advantage of having a "questions" table and an "scores/answers" table

Well, for one thing, if you wanted to compare the number of questions to the number of answers, you would simply count the related records using the Count() function.

For another, you could add a new test or modify the structure of an existing one without having to modify your solution.

But the main reason is that you cannot easily produce statistics if your data is not "atomic". IOW, if all responses are tied to a specific test, you cannot regroup them by another parameter.

[Woodnote]

Ahhh, I see

Well, for one thing, if you wanted to compare the number of questions to the number of answers, you would simply count the related records using the Count() function.

-this makes sense

For another, you could add a new test or modify the structure of an existing one without having to modify your solution.

-this isn't a big issue (it won't really ever change)

But the main reason is that you cannot easily produce statistics if your data is not "atomic". IOW, if all responses are tied to a specific test, you cannot regroup them by another parameter.

-this is significant to me. Originally, I decided to define each "question" as an attribute of each test because it seemed that by doing this I could enact validation rules much more easily (it's only a slight exaggeration to say that each attribute in every test has its own unique validation rules associated with it). In truth I'll be doing almost all of my statistics in external programs like SPSS and I was planning on exporting all the variables as I needed to do so (not very often). However I would like to be able to run real-time statistics calculations from within the database if possible.

What would you suggest?

Also, can you recommend any good reading on creating calculations/running stats on research databases (or any other type for that matter)

thank you again so much for taking the time to help me!

[comment]

What would you suggest?

What I already suggested: use records instead of fields. I'm afraid I cannot be much more specific than that without more details. For example, I still don't see why each test needs its own table. I'd say a "test" is no more than a group of questions asked at the same time. Seen that way, a "timepoint" would have one participant and one test (i.e. on date X participant Y took test Z).