change fields

[mleiser]

I have a report that, say, has 10 fields. One field may be the class that a student is in. If we're in term1 the class is whatever it is. In term 2 the student may have been moved to another class. So I have a field in in the table for class for term1 and one for the class in term2 - which may be the same or different. I want to somehow have the ability to tell the report to print either class1 or class2 into the same spot on the report, depending on, say, some parameter. Any easy way to do that?

Mike

[bruceR]

Sounds like a basic design problem - different fields for different classes? Should be a normalized design with a single field for class and other fields for term, student ID, etc.

[mleiser]

I guess I didn't explain it right. For example, we have a report for bus service. In the am they may take bus number abc, In the pm they may take the same bus of bus number xyz. I want ONE report that will print all the students info and when it comes to the bus number, if it's a report on the am buses the bus field on the report should contain the am bus, if the pm report that field on the report should have the pm bus number. It's not really a different type of field. I'm trying to determine how I can have a field on a report that is either this field or that field.

Mike

[efen]

Now I am totally confused - in the first post you describe your problem with classes and then in the next post you talk about buses???

[icanhazdatabase]

You can do several things:

1. If you have two fields, one for AM bus number and one for PM, add a sub-summary to the report for each field and put each field in it's own sub-summary. Then in your report script sort the records by either of the two fields (after sorting by student name or id or whatever else). Only the summary part that's based on a field that was in the sort will show up. If you sort by this field AFTER the student id/name field, then it will show up for each student.

2. Create a global flag field in the students db to track if you are running a report on AM or PM buses. Then create a calculation field that uses the flag field to decide weather to display AM or PM value. Add this field to the report. In your report script change the global flag depending on what you want to display.

I personally recommend solution 1.

Best,

Aleks T.

[bruceR]

And the reply is still the same: you have a design problem.

It should be one field, with the correct value. There should not be an AM bus field and a PM bus field. There should be one bus field, and it might hold an empty value or AM or PM or whatever values you are using.

[mleiser]

I don't quite understand why this is a design floor. By the way, I used two exaples because the issue applies to both. I thought I can explain the bus one better. We have students that come into school on one bus and go home on another. Most come and go on the same one. So if I have a AM report it must show per student what bus they're on. If it's a PM report it must show the bus they are on going home. Those are two separate fields. I don't see the design flaw.

Mike

[mleiser]

Thanks for the response. I actually need both solutions. If it's a straight report where for each student I need to know the bus number I can use the calculation field on the report. If I'm printing a report in bus number order, the sub summary solution will work. I have to think on how to do it, but it should work. Thanks so much.

Mike

[bruceR]

I don't quite understand why this is a design floor. By the way, I used two exaples because the issue applies to both. I thought I can explain the bus one better. We have students that come into school on one bus and go home on another. Most come and go on the same one. So if I have a AM report it must show per student what bus they're on. If it's a PM report it must show the bus they are on going home. Those are two separate fields. I don't see the design flaw.

Mike

So what is the context for the rest of the report? This is by student? Or by bus? Have you looked at the reporting section of the Web Viewer Example.fp7 file that comes with FileMaker?

It shows how to make a scripted HTML report which allows the user to specify fields. There are various ways to do "data collector" reports instead of hard-wired layouts.

[mleiser]

I actually haven't looked. I'll check it out.Thanks so much.

Mike