Excluding N/A from value list for field count

[kirkrr]

Survey question set - 300+ questions.

The structure is all question fields in one table occurrence.

 

Before we get into data structure alternatives: the alternative of one question per record, is not feasible (unless I create 25 source TO's - what a mess!), as there are 25 sets of value list responses, with some value lists for 1 question, and some value lists for as many as 90. All the examples I've been able to find depict portals or layout approaches, with consistent value list representations across all questions. My data set does not support this approach in any way I can think of. e.g. Record 1, 34, 65, 139, and 318, all use the same value list, and need to be grouped in one layout for user comprehension and other factors. (this is the USER interaction context, not the layout context).

 

Let's get one answer at a time: Is there a way to exclude the N/A response from a value list of 1,2,3,4,5,N/A used in multiple fields (1, 34, 65, 139, and 318) for a count of non N/A responses, used as a denominator in the average calculation for this same set of fields??

 

field 1, 34, 65, 139, and 318

-------- divided by -----------------

# of above field with a 1-5 in the response

 

If there is a way to accomplish this, a single radio button value list would work. If not, what I have working (painfully) is a separate N/A response field and a toggle field to manage the radio button behavior - or maybe there is another approach that would be easier that 300 question fields, 300 N/A fields and 300 toggle calc fields, if the above math question cannot be accomplished. The N/A field response does a SET FIELD = "" to the RESPONSE field, through the toggle calc field. This calc field is the only way I could figure to get the RESPONSE field to blank the N/A field AND vice versa. Calcs in just these 2 fields, created a catch 22 - no data entry in either.

 

I still need a way to count the number of valid (1-5, not N/A) responses for the average calc across valid responses. ???????????? Count functions all seem to be record based, not field based.

 

Suggestions??

 

I can't post the file, do to contract restrictions and the proprietary nature of the content. I'd have to obfuscate the content. It would be quicker to produce a small example file, if I can't effectively describe the problem.

[Maarten Witberg]

there are 25 sets of value list responses

are these hard-coded?

also, is it not possible to just leave out the N/A option (so empty responses are not valid by default)? Or alternatively to just assign a numeric value to N/A (say 0) then exclude them in a find?

 

Before we get into data structure alternatives:

I'm sorry, but that is the first thing that comes to mind....

[kirkrr]

I format the text on the radio button value list to the same as background, and place text over the value list text (each question has a unique conditional response text, but the values are range related). I CAN use a 0 for the last, but that still does not exclude the field from a count - the denominator of the average calculation needs to be all 1-5 responses, excluding the N/A response. If, over a set of 5 questions for a calculation, 2 are N/A, then it is the sum of all 5 fields, divided by 3 - now, how do I calculate the 3???

[Maarten Witberg]

Ok. try

 

 

 sum ( field1 ; field2 ; field3 ; field4 ; field5) 
/ 
count ( getasnumber ( field1 ) ; getasnumber ( field2 ); getasnumber ( field3 ) ;  getasnumber ( field4 ) ;  getasnumber ( field5 ) )

seems to me you're in for a lot of coding. and if the survey ever changes you'd need to open up the database and redo the whole thing. Unless I am misunderstanding you.

I'd still like to know if these value lists with answers are hard coded. You could solve this I think with an answer sets table (two tables actually, one that defines the sets and one with all individual answers). then you could create conditional value lists and link them to each question by answer set ID. then the survey could be one record, one question and life would become a lot simpler I think.

[kirkrr]

Right now, there are 59 fields that use a value list of 1-8, ranging from "Strongly Disagree" (1) to "Strongly Agree" (7), to Don't Know (8). 33 fields are 1-6, from "No Confidence" (1) to "Complete Confidence" (5), to "N/A" (6). The value list is numeric (used for calculations), with the layout depicting text for human consumption.

 

The remaining fields are all over the map, with hard coded value lists for as few as 1 field, to a dozen or so fields with common value list responses. None of these responses would benefit from a non hard coded value list. The responses are cast in concrete, and not subject to change.

 

Originally, I had one question/response per record, but struggled with how to get a subset of records into a given layout (most of my Filemaker experience comes from version 1-5, over a 20 year period, with far less work in FM done in recent years). As I have spent a number of weeks relearning FM (and the idiosyncrasies relative to my knowledge of many other RDB products) , I now have a good grasp of table occurrences, and can understand how I could have accomplished this using a match field in a relationship to a TO that would depict the subset of records for a given portal/layout.

 

At this point, however, all the questions for a single survey are in one table. There are additional TOs for agency, counselors, clients, evaluators, and various reporting tables. Those parts all work great.

 

I'm gonna take a stab at restructuring the tables back to where I started, with one question per record.

 

Is there a way to dump the list of field names into Excel? I can then transpose the table and reimport, to get from a single TO of many fields, to a multi-record table with a few fields.

[Maarten Witberg]

yes, you can use FieldNames ( Filename ; LayoutName ) for this.

[kirkrr]

Restructured it to a table with one question per record. The complexity seems to be up a hundredfold over the one survey/all question/one record structure. (and that was still relational, for agency, client, counselors, and evaluators).

 

In order to set up input layouts, (based on every sample I looked at) I leveraged used self join table, with questions self joined to questions to answers. In my case, as each set of questions was to be specific to a layout and the explanatory text on a layout, it took 18 question TOs (one per layout tab) to map the matchfields to a particular layout (I guess I could have just used a single TO for questions, and started the portal row on the question sequence for that layout, but deleting one question would ripple through every portal). The answer TOs are children to each of the question TOs per layout.

 

This is workable (even if I have 10X more time into what was a virtually finished product, minus one calculation), but now I need to produce a report detailing out the same information input. However, with not all questions are answered, so it looks like another set of 40+ TOs to produce the report, complex layouts, multiple portals per page, and I don't know how to match a question ID with layout position!, at least with non-sequential records (gaps in sequence).

 

Placing fields on a layout is easy. Placing a field on a layout, and expecting one specific record of 5000 to show up in the right place, is, at least from my struggles, not so easy. To add to the problem, attempting to ascertain if the right answer value is displayed in the right place, is a good bit of work as well.

 

In addition, when the answer to one question is N/A, I need to zero out 5 other records - no buttons or triggers allowed, just field entry.

 

It seemed to be the consensus opinion to make one question per record, instead of one survey per record, but I am not sure. What am I missing??

[Ender]

Hey kirk,

 

I've done surveys/checklists in a variety of ways, and I think you're getting there. The best way I've found, is to create all the Answer records for a survey at once via a script. Then all the records are there, even if they aren't answered. You can then simplify the structure to one or two TOs for Answers, utilizing portals based on the same TO (with different record ranges if necessary). With this method, one TO can be used for different questions, even if they use different formats or different value lists.

 

The reporting can then be done through the Answer table. The structure and scripting for the reports can be tricky depending on the Answer format and desired aggregation.

[kirkrr]

How do I get the numeric equivalent of a text item in a value list.

 

I have a horizontal value list (radio buttons) with manually entered value list text for each button.

I need to do a math calculation based on the average of the numeric equivalent of the position within the value list.

I also need to ignore the N/A response - the last radio button.

 

Initially, I had used a value list of numbers, formatted to the background color, overlaid with text on the layout. The problem with this was clicking on the button brought the value list to the front, obscuring the overlaid text - not good for user input. SO, I changed the list to the textual value, and now need to calculate based on the numeric equivalent.

 

Suggestions? I am not sure where to start looking.... maybe the LET function? to substitute values for the text?

[Ender]

Try a Case() function.

[LingoJango]

Here's a custom function you can use. It assumes unique values in the value list.

 

To filter out the "N/A" value you could use another custom function in the items calc within this custom function: http://www.briandunning.com/cf/98

 

I haven't tested these but the general logic oughta work, I think.

[kirkrr]

Try a Case() function.

 

A case function works, however, I'd like to simplify the repetitive entry.

 

I am making the [rash?] assumptions here, that the case calculation must be in a different field; attempting to replace the contents of the entry field with a numeric text value, when the value list are text string entries, will negate the current display of the radio button value list, as the replaced values are NOT in the list.

 

SO, I would guess that I need another field to perform the calculation in. As I have 59 questions that a single value list, ranging from no confidence ... complete confidence to N/A, I would prefer to come up with a calc that can just be copied.

 

I have been attempting to get the fieldname, but am unsure as to the command. I was then going to do a leftwords function against SELF, so that the base fieldname would reference the contents of the parent field, not the copied one. (leaving the default copied field as FIELDNAME COPY, the leftwords would be FIELDNAME, where the textual value list answer resides.) But the SELF function does not return the fieldname.

 

Then, I could do a case function on the value list contents for the source field, using the CASE function to return a numeric equivalent of the position within the value list (No Confidence = 1, some confidence = 2, etc. and N/A = "" ) so that I can do an average on a set of fields.

 

Am I headed down the wrong path here? The goal is just to get the numeric equivalent of a textual value list entry AND be able to do that 59 times without entering a different calculation 59 times. (I can do it manually, selecting the field name, but trying to minimize devtimes).

 

--Kirk

[LingoJango]

If you now have only one question per record, you could manage with a single calc field that uses Case to reference the right answer field, couldn't you?

 

If you don't, I think you might be stuck with a calc field that you get right for the first answer field, copy 59 times over and then amend for the field name. Or a custom function that would do the same thing.

 

I *think* I'd go for the first option (one question per record).

 

If you go for the second option, you can use a Let statement together with an Evaluate or GetField function so that the editing can be absolutely minimal.

[kirkrr]

I reverted from the "one question per record" back to "one survey per record" - the key report has lots of psycho babble boilerplate between responses, which was either individual or aggregated presentation. With 300 questions, it was taking 300 of the same portal with a different starting record (so I could pick the right individual question out of the sequence to portray in the right place) which I had to cross reference with the field name that belongs in that portal to portray the question, and then find the appropriate answer to match it. It was a logistics nightmare, taking serious amounts of time to match all this up (check against the table view that contained the xref info), and major leaque prone to to human error (which could have serious medical consequences). It was taking 22 seconds to pre-populate the questions (so that I could be assured that they all would be there, as the portal is picking based on row sequence number, not on something that you can easily validate as being the right one) on my 64 bit dual core 4gb RAM, 2.2ghz machine - far more horsepower than the clients will probably have.

 

Yeah, fully relational might be the DB purist approach, but relational everywhere has the potential to be problematic.

 

What I need now is a function that will return the FIELDNAME of the current field.

[LingoJango]

I asked about that a while ago but the consensus seemed to be it's not possible.

 

Still, Let plus Evaluate should make it *relatively* painless to edit the calcs in FM9Advanced. Bite the bullet! I had to create 62 almost identical multikey relationships a coupla weeks ago, based on 31 date calc fields. A nightmare, I kept using the wrong operators... but the outcome is BEAUTIFUL and just what I needed.

[kirkrr]

I asked about that a while ago but the consensus seemed to be it's not possible.

 

Still, Let plus Evaluate should make it *relatively* painless to edit the calcs in FM9Advanced. Bite the bullet! I had to create 62 almost identical multikey relationships a coupla weeks ago, based on 31 date calc fields. A nightmare, I kept using the wrong operators... but the outcome is BEAUTIFUL and just what I needed.

 

In the "one answer per record" approach, I had used 16 question TOs to segregate questions into particular layouts (and got slammed a bit for confusing DB structure with presentation). But is is precisely these relationships that allow segregated content easily.... after all the goal is communication, right?

 

(the data pyramid: data --> Info --> Knowledge --> Communication)

 

I could create 300 TOs for questions and 300 TOs for answers, THEN I would be pretty well assured that the right question and answer would be presented in the right place, as the matchfield relationship would solidify it (along with 300 matchfield global fields in the parent TO)

 

.... almost like just placing a field out of the survey record on a layout :-/

[LingoJango]

I'm having trouble visualizing the problems you must have faced, but did you try overlaying fields in a single presentation and using the sliding feature extensively? I've managed a neat trick or two with this kind of approach - though these layouts are admittedly a nightmare to handle.

[Ender]

Hey Kirk,

 

I'm sorry you had trouble with the recommended structure. Although one record per answer is more work to set up for the interface, it sure makes it easier to do aggregating and reporting.

 

Having to duplicate your calc for each answer in your flat file is probably the first of a number of hurdles in getting things aggregated. If you try to make a report that shows counts or breakdowns of all questions at once, you'll face even greater challenges.

 

One technique you could consider is to use your flat file for data entry, then use another table for Survey Answers. The script for setting the Survey Answers shouldn't be too hard. Just loop through all the fields, and create a Survey Answer record for each, with the field name and answer.

 

This way you'd get the advantages of both ease of interface design with the flat file and reporting with the relational structure. The disadvantages are that you're doing a little more work and there's a chance for something to get out of sync if something changes in the flat file after the Survey Answer records are generated (and it takes extra work to guard against this).

 

Now if you'll keep this conversation in one forum, it will be easier for us to follow along.