Help with syntax for calculation

[Gwen]

Hi. I would like to specify a calculation for a field A. For fields 1.... n, I want to count the number of fields with the value ? or {blank} (i.e., a non-numeric entry).

Can somebody assist me with the syntax?

Thank you,

Gwen

[VFXdbGuy]

A self relationship is your best bet. See http://www.filemaker.com/ti/103815.html

[transpower]

I think I see what you're trying to do; rather than a calculation, define each field to have a required value: Field | Options | Validation | Not empty or Strict numeric

If that's not what you really want, then write a script to loop through each field, adding to a global sum if IsEmpty(field) = 1 (for a text field) or IsValid(field) = 0 (for a numeric field).

[Gwen]

Thank you. I'll try the second thing. -Gwen

[Gwen]

Hi. I need help with the global sum piece. I think you said I want to define the field as:

If ( IsValid ( field )=0 ; add field to global sum) [to count up non-numeric fields in a table]

How do I set up a global sum?

Sorry, I'm sure this would be simple if I had any programming experience.

Gwen

[Gwen]

Hi. I haven't received an answer yet to my query about how to set up a global sum, so I'm reposting my question. I hope that's o.k.

For fields 1.... n, I want to count the number of fields with the value ? or {blank} (i.e., a non-numeric entry). Transpower suggested that I "write a script to loop through each field, adding to a global sum if IsEmpty(field) = 1 (for a text field) or IsValid(field) = 0 (for a numeric field). "

So I understand that I need to do the following:

If ( IsValid ( field )=0 ; add field to global sum) [to count up non-numeric fields in a table]

But I am not sure how to set up a global sum (result of If expression).

Thank you in advance for your help.

Gwen

[comment]

You'd define a field (number, global) to act as a counter.

Are these fields 1.... n all in the same record? In general, summarizing alike data is much easier when each value is in the same field of a different record.

[Gwen]

Hi. Yes, they are all in the same record.

O.k., so I defined a new field called Blank_counter, and tagged it as a container.

How do I use it?

Thanks, Gwen

[comment]

Container won't do you any good. Define the field as Type Number, and under Options check "Use global storage.". Then in the script start by the Set Field step, setting your counter to 0. Then...

If you don't mind me saying so, this is like helping someone drive a car with no tires. The problem is not how to count n blank fields. The question is why do you need count n blank fields in a single record. I don't know what you're trying to accomplish, and I am willing to bet that your data structure is wrong.

Rather that doing it the hard way (writing a rather complex script), why don't you explain in a few words what the purpose is. Perhaps then we can help you do it the easy way.

[Gwen]

O.k., here it goes. The non-numeric entries in each record (the "blank" or "?" entries) are meaningful. In particular, they represent non-responses on a questionnaire. I want to count these entries separately from the numeric (1=correct or 0=incorrect) entries and write out the number to a summary field for each record.

How would you recommend altering the data structure?

[Gwen]

... I should add that there are 114 fields in each record. Is there an alternative to specifying a very long and tortuous script to loop through all fields?

[Gwen]

Hi. Would you have a quick look at this screenshot and let me know if I'm on the right track?

samplescript.pdf

[comment]

I'd say I win my bet. There are of course a number of ways to go about it, and it would be also helpful to know a few more details: how many questions per questionnaire; how many people are answering; how often is this process repeated; are the questions multiple-choice (how many choices, does the number vary per question); is the answer always correct/incorrect/blank, or are there grades; can the grading process be automated, etc.

But I think I can say with some certainty that each response should be a record. There should be (at least) a Table of Questions and a Table of Responses. If 20 people answered 50 questions, then there will be 50 records in Questions, and 1,000 records in Responses. Since each Response record is related to its parent Question (by including the Question ID in the Response record), summarizing the data should be a breeze.

The same applies to the people; if you need summaries by people, you need a Table of People, and then the Response record will also hold the Person's ID.

Structuring the data will enables you (and FMP) to rapidly produce any kind of summary, from any dimension.

Example:

In Table Questions (50 records) a single calc field = Count (Responses::Correct) will return the # of correct responses for each question. IOW, each Question record will summarize its own 20 responses. A similar field in People will return the # of correct responses for each person.

Does this make sense to you?

[comment]

I just saw your pdf. Yes, it seems the right way to go in the wrong direction, so to speak.

[Gwen]

Hello. You might have won that bet -- I'm not sure yet. I believe the table is set up as you suggest: each record contains a subject ID (actually two, but that's not relevant here). There are 114 responses for each record (subject), and each response can be either correct, incorrect, or -- blank.

I could be wrong, but it seems to me it would be very inelegant to have 342 fields per record (3 checkboxes, as it were, for each question), instead of 114. I can see how that would make it easier to compute sums. But surely there is a simple way to count up the number of fields for each record that hold a particular value? Even Microshit Excel (excuse my language) makes it easy to compute this kind of sum.

I'm also wondering if there is a way in my script to refer to fields by their place in within the record (first, second, etc...). I see in the Help that this is possible for some functions, such as GotoRecord/Request/Page. What I would like to do is have a simple script that looks something like:

GotoFieldX (say, the 10th field)

Loop

SetCounter=0

If FieldX=nonumeric (invalid, whatever)

SetCounter=Counter+1

GotoFieldX+1

Exit Loop when Field = Last

EndLoop

I'm sure this syntax is screwy somewhere, but I hope it makes sense generally. Is there a way to do something like this?

Am I understanding you correctly -- that you are suggesting to have 342 instead of 114 fields per record (3 for each question)?

Thanks for your help.

Gwen

[Gwen]

I think I see what you are saying, now, although I need to think more about how to move around the layouts. I have the data structured as shown in the pic (attached), but you are suggesting each that each question be associated with its own set of records.

O.k. I'll think about how to make this work. Seems like it would be better over the long run. Sigh. Thanks, gwen

datastructure.pdf

[comment]

No, Gwen. What I am saying is that there should be a separate record for each single response. If there are 114 responses, then it is 114 records. Each record holds a single answer to a single question - either correct or incorrect.

Other than identifying information (which question is being answered by whom), the record holds a single field - the answer.

BTW, if I understand correctly that the answer is a Boolean, then this will be even simpler.

[Gwen]

O.k. Say I have 114 records (one per question). Then each record will contain one of 3 responses -- correct, incorrect, or BLANK (this must be treated differently than correct and incorrect responses). Do I then have 114 records for each subject? That would result in 1,000s of records (114 x >900 subjects)...

It seems to me that the layout I'm using may still make the most sense for our purposes....

[comment]

Here is a little unacademic demo of what I mean.

Regarding the number of records - so? Do you think 114 records with 1 field is more data that 1 record with 114 fields?

Regarding layout - the layout should not dictate the data structure. Fix the engine first, and we'll arrange the upholstery to your liking later.

SurveySays.fp7.zip

[Gwen]

O.k. I'll try this alternative layout. I would still like to know how to make the script work, though, whether it has 114 if-then statements, or just a single generic form (start with fieldx.. goto x+1... end at last x).

A while ago I tried the script with just one if.. then statement (just Mill_spelling; see attached), and it worked fine. But when I added a second (Mill_real_word), it no longer worked. How do I fix this?

Thanks. I promise to try out the alternative layout you suggested when I have some time....

Thanks,

Gwen

ifthenseq.pdf

[Gwen]

Hi Comment. One more question about your sample database....

Maybe I'm just tired, but I don't see where you're computing number of blank/valid responses per person. I see where you've tallied the number of blank (or valid) answers across people, but where do you compute the number of blank (or valid) responses across the 4 questions for a given person -- say, Anne Smith? Your People layout looks like my current layout, so I don't see how it simplifies the problem of summing over fields for a given person.

Gwen

[comment]

The person who should answer this (about the script) is Transpower. I don't quite understand how it's suposed to work myself.

If you gonna interrogate each field by name (instead of looping it), then you don't need a script at all. You can make a calc of

not isValid ( field1 ) +

not isValid ( field2 ) +

...

not isValid ( fieldn )

Probably just as fast to count it yourself, don't you think?

This NOT how it's supposed to work. I have simplified my demo even further, and added two scripts to report summarized data. Please examine the field definitions, and see how easy it is to get results with minimum effort.

SurveySays.fp7.zip

[Lee Smith]

Gwen,

Please do not change the subject of the Threads.

I sent you a Private Message on this.

TIA

Lee

[Gwen]

Hi. I thought I sent a follow-up response last night, but I must've failed to hit the "send" button.

Although instructive, the example you provided is not really relevant to my problem. I'll try again: What I want to do is, for each record (subject), to count the number of fields in that record holding a particular value (0,1, and 2/?, for example). A global field does not work: although I can use it as a counter (my script worked for 1 field as I explained above), it propogates the result to every record.

What I want to do is to write a script that does the following:

1. Start on record (person) X.

2. For that record, specify a (unique) variable that acts as a counter. Set the counter initially to '0'.

3. Go to field A.

4. If field A holds a particular value (say, '2'), add to counter.

5. Go to next field for record X.

6. If next field contains '2', add to counter.

7. Etc... Exit loop when I reach final field for record X.

8. Go to record X+1.

9. Repeat loop (3-7).

10. Terminate after final record.

I know generally how to begin. One problem is that if I try to do it the dumb way -- without looping, but instead going sequentially through each of the 114 fields for record X -- my script doesn't work when I add more than one field. I'm attaching a screenshot of the syntax.

Second, I would like to use a Loop to make things easier, rather than having to specify all 114 fields in the script. I notice there are commands like "Go to next field." But how to I query the value for that field?

Thanks.

Gwen

ifthenseq.pdf

[Ted S]

Will "Get(ActiveFieldContents)" work?

[Gwen]

O.k. Sorry -- I just saw your message. I'll quite the thread, then.

Thanks, Gwen

[Gwen]

Hi. Thanks, it looks like that's what I need. I didn't realize I need to go outside of the scripting "wizard" to access some of these functions. Thanks for your help. Gwen

[comment]

the example you provided is not really relevant to my problem

Well then I don't know what else to offer.

I will most definitely not waste time, yours or mine, on the question of the counting script. This is not something I would use myself, certainly not suggest to a beginner. Moreover, checking if the result is correct would be twice as complex.

Excuse the bluntness, but if you insist on banging your head against the wall, allow me not to participate in the effort.

[Gwen]

Hi. That's o.k. I appreciate your help, but I'm just not sure how your proposed change in the layout addresses my problem. Maybe I'm just being dense...? Thanks anyway, Gwen

[-Queue-]

Not that I am recommending it either, but I will give you a script that should do it.

Go to Record/Request/Page [First]

Freeze Window

Loop

Set Field [numCounter; 0]

Commit Records/Requests [ ]

Loop

Go to Next Field

If [Get(ActiveFieldContents) = gValue]

Set Field [numCounter; numCounter + 1]

End If

Exit Loop If [Get(ActiveFieldName) = Substitute( RightValues( FieldNames( Get(FileName); Get(LayoutName) ); 1 );

[Gwen]

Cool, thanks! I hope this will do it. -Gwen