Importing into calculation field

[TanyaI]

Hi all

 

We have an old database (flat file) in which I entered data from a hand scored questionnaire. Each client had one record and they answered questionnaires at yearly follow-up times. Each year has it's own layout. I would score each questionnaire and then put in a raw score (number), then a transformed score (%) that I calculated and entered into a number field. There are about 100 records in this database with a number of yearly questionnaires for each record.

 

We have been using a new database (relational database) in which we enter the responses to the questionnaire for example yes/no, better worse and the database scores the questionnaire. Thus these resulting fields are all calculations and each questionnaire is a record.

 

Now it would be very nice if we could have all the questionnaire results in one solution. I did do an import into the new database of a different questionnaire from a similar old database (flat file). I exported each year to excel fixed all the field labels to be the same as the new solution and then I basically did a straight import. None of those were into calculation fields so was a simple if a little tedious process.

 

Can anyone give me any guidance as to how I can import the old data into the new solution where the target fields are calculations? It may be a case of adding new fields and writing new calculations but that is a lot of new fields and lot of calculations.....

[mfif]

Why don't you export to Excel, add new columns with the right precalculations there, and run the import? I love FileMaker for good interface, but excel's really good for quick adding and testing of calcs, if you're handy with it.

 

You say each year has its own layout. Are you really saying each year has its own set of fields? So that the fields in the old database are something like

 

Client --- 2004Q1 (that would be Question1) --- 2004Answer1 --- 2004Q2 --- 2004Answer2 --- 2004RawScore --- 2004PercentageScore --- 2005Q1 --- 2005Answer1 --- etc etc?

 

Or do you just have

Client --- 2004RawScore --- 2004Percentage --- 2005RawScore --- 2005Percentage --- etc.

 

If the latter, the only solution I can come up with involves four more fields: OldRaw, OldPercentage, DisplayRaw and DisplayPercentage.

 

You import into OldRaw and OldPercentage. The display fields are calculation fields

 

DisplayRaw=If (IsEmpty (OldRaw ) ; YourRealRawScoreCalculationField ; OldRaw )

 

DisplayPercentage= If (IsEmpty (OldPercentage ) ; YourRealPercentageCalculationField ; OldPercentage )

 

But it *is* clunky. And you'll want a validation calc that requires OldRaw and OldPercentage to be empty if *any* of the regular data fields contain data.

[TanyaI]

Unfortunately it is the latter(of course)! Thank you for the quick response. It has taken me a little while(and a long weekend) to get my head around your response. In all honesty I am still mulling over your solution!

 

I think it will work and I think the amount of work needed to get it done is balanced by the benefits of having everything in one database. I will give it a try. Thanks againsmiley-laughing

[Jack Rodgers]

First, you can't import into a calculation field. If you had tried this you would see that you can't.

 

Your problem seems to be of this design:

 

Answer:

1. Question

2. Their Answer

3. Your Preferred Answer

 

CalcField If( 3 = 2, 1, 0 )

 

If this is so you only need to import 1,2,3. And in the test table you can sum the calcfield for a result.

 

If you designed the file as relational:

 

Test Number Answers

 

Your problem is quite simple. Just export to Question one to file 1, question 2 to file 2.... and then import each file into your three fields in the answer file. Or import multiple times targeting the three different fields set 1, set 2, set 3.

[TanyaI]

First, you can't import into a calculation field. If you had tried this you would see that you can't.

 

Your problem seems to be of this design:

 

Answer:

1. Question

2. Their Answer

3. Your Preferred Answer

 

CalcField If( 3 = 2, 1, 0 )

 

If this is so you only need to import 1,2,3. And in the test table you can sum the calcfield for a result.

 

If you designed the file as relational:

 

Test Number Answers

 

Your problem is quite simple. Just export to Question one to file 1, question 2 to file 2.... and then import each file into your three fields in the answer file. Or import multiple times targeting the three different fields set 1, set 2, set 3.

smiley-undecided that was the original ques really importing into a calc field then I got all caught up in a possible solution Opps!

 

The problem is it is a huge questionnaire and because it was hand calculated the only fields entered on the old database is a count of number of yeses(for example) for a group of ques(x8 scales/fields) and in another case a more complex calculation from responses to multiple choice ques then "transformed" into a percentage(again x 8 different scales/fields).

 

Answers were not entered into the old database just scores.....if we had entered the answers yes it would be much simpler, or am I not understanding your answer? Thanks for your response.

[mfif]

Maybe you don't need the Display fields I suggested; you can just adjust the calculations:

 

a) add an Auto-Enter formula that replaces field content to the two Old fields: Case ( notIsEmpty ( Answer1 ) and not IsEmpty ( Answer2 ) and ; "" ; oldfield )

 

b) for good measure, add a strong validation formula to the same fields:

Case ( notIsEmpty ( Answer1 ) and not IsEmpty ( Answer2 ) and ; "" ; Self )

 

This is to make sure there is no way you can have content in those fields entered by mistake in any new records.

 

c) modify your calc field:

 

Case ( IsEmpty ( OldRaw ) ; ; OldRaw )

 

and do the same for the percentage. You might want to add some text formatting (color or bold) to highlight the old data.

 

Before you decide, consider the implications of having separate display fields or a single calc field in terms of aggregate operations (summaries and the like).